Where vs Having

When working with relational databases, filtering data is a fundamental task. Two clauses that frequently appear in SELECT statements—WHERE and HAVING—serve this purpose but operate in different contexts. Knowing when to use each one is essential for writing correct, efficient queries.

Core Concepts

• WHERE

  • Acts as a row filter.
  • Evaluated before any aggregation.
  • Cannot reference aggregate functions (SUM, COUNT, AVG, etc.).

• HAVING

  • Functions as a group filter.
  • Applied after GROUP BY and aggregation.
  • Commonly contains aggregate expressions.

In short, WHERE filters rows, HAVING filters groups.

SQL Execution Order

To grasp why the two clauses behave differently, it helps to remember the logical order in which a SELECT statement is processed:

1. FROM / JOIN – Identify and combine source tables.
2. WHERE – Remove rows that don’t satisfy the condition.
3. GROUP BY – Partition the remaining rows into groups.
4. HAVING – Discard groups that fail the condition.
5. SELECT – Choose columns to return.
6. DISTINCT – Eliminate duplicate rows.
7. ORDER BY – Sort the result set.
8. LIMIT / OFFSET – Restrict the number of rows returned.

Because WHERE runs before aggregation, it can leverage indexes to trim the dataset early. HAVING, on the other hand, operates on the already‑aggregated groups, so it cannot benefit from row‑level indexes.

Practical Examples

Assume an e‑commerce table orders with columns:

1. Using WHERE to Filter Raw Rows

To retrieve orders whose amount exceeds 100:

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > 100;

Here, each row is examined independently; no aggregation is involved, so WHERE is the correct choice.

2. Using HAVING to Filter Aggregated Results

To find customers whose total spend surpasses 1,000:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

The SUM(amount) is only available after grouping, so the filter must be placed in HAVING.

3. Combining WHERE and HAVING

Suppose we want customers who placed orders after 2023‑01‑01 and whose cumulative spend exceeds 1,000:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2023-01-01'   -- Step 1: row filtering
GROUP BY customer_id               -- Step 2: grouping
HAVING SUM(amount) > 1000;         -- Step 3: group filtering

• Step 1 reduces the dataset early, improving performance.
• Step 2 aggregates the filtered rows.
• Step 3 removes groups that don’t meet the spend threshold.

Performance Tips

1. Filter Early with WHERE

   • If a condition can be expressed without aggregates, always place it in WHERE.
   • Indexes on the filtered columns enable rapid row elimination.

2. Avoid Misusing HAVING

   • Writing a condition like HAVING customer_id = 5 forces the database to perform grouping and aggregation on the entire table before discarding the single row, which is wasteful.

3. Use Aliases Wisely

   • Some engines allow referencing column aliases in HAVING, but this is not portable. Prefer explicit expressions.

4. Keep HAVING Simple

   • Complex calculations in HAVING can be moved to a subquery or a Common Table Expression (CTE) to improve readability and sometimes performance.

Summary

• WHERE: Filters rows before aggregation; cannot use aggregate functions.
• HAVING: Filters groups after aggregation; typically contains aggregate functions.
• The logical order of execution ensures that WHERE is the first opportunity to prune data, making it the most efficient place for row‑level conditions.
• Use HAVING only when you need to impose constraints on aggregated results.
• Combining both clauses—WHERE for early filtering, GROUP BY for aggregation, HAVING for group filtering—yields clear, performant queries.

By adhering to these principles, you’ll write SQL that is both correct and efficient, avoiding common pitfalls that can lead to incorrect results or slow performance.