HAVING vs WHERE

## Two filters, two stages Every SELECT that aggregates offers two places to reject data, and they act on different objects. `WHERE` tests **rows**: each candidate row coming out of `FROM` is examined on its own, and any row whose predicate is not TRUE never reaches the grouping step. `HAVING` tests **groups**: after `GROUP BY` has collapsed the surviving rows into one group per distinct set of grouping-key values, each group is examined once, and groups whose predicate is not TRUE are dropped. The standard's logical evaluation order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Every practical difference between the clauses follows from those two positions: WHERE runs while only rows exist, HAVING runs once groups and their aggregate values exist. ## What each clause may reference `WHERE` sees the columns of the tables in `FROM`, one row at a time. It cannot contain an aggregate such as `COUNT(*)`, `SUM(amount)` or `AVG(salary)`; at that point nothing has been grouped, so there is nothing to aggregate over, and a conforming engine rejects the statement. `HAVING` may reference aggregate expressions over the group, the grouping columns listed in `GROUP BY`, literals, and correlated references from an enclosing query. It may **not** reference a plain column that is neither a grouping key nor wrapped in an aggregate, because such a column holds many different values inside one group and there is no single value to test. A useful consequence: `HAVING` can filter on an aggregate that never appears in the result. ```sql SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(amount) > 10000; -- SUM is tested but not returned ``` ## A worked example ```sql SELECT customer_id, SUM(amount) AS total_2026 FROM orders WHERE order_date >= DATE '2026-01-01' -- row test: drop older orders GROUP BY customer_id HAVING SUM(amount) > 1000; -- group test: drop small customers ``` Read it in stages. `WHERE` discards every order placed before 2026. `GROUP BY` makes one group per customer out of what remains. `SUM(amount)` is computed **only over the 2026 orders**. `HAVING` keeps the customers whose 2026 total exceeds 1000. Moving the date test into `HAVING` is either illegal (`order_date` is not a grouping key) or means something entirely different. ## The clauses change different things This is the part candidates most often miss: `WHERE` changes the aggregate **values**, because it changes which rows feed them. `HAVING` never changes a value — it only decides whether an already-computed group survives. So a filter moved from `WHERE` into an aggregate-based `HAVING` predicate does not merely reorder work; it can produce different numbers as well as a different set of rows. ## When the two are interchangeable If the predicate references only grouping columns, either placement returns the same rows: ```sql -- same result set both ways SELECT region, COUNT(*) FROM sales WHERE region <> 'EU' GROUP BY region; SELECT region, COUNT(*) FROM sales GROUP BY region HAVING region <> 'EU'; ``` Prefer `WHERE`. It states the intent — this is a row filter — and it gives the grouping step less input. Whether an engine internally rewrites the second form into the first is an optimizer matter, not a guarantee of the language. ## HAVING without GROUP BY `HAVING` does not require `GROUP BY`. Without one, everything that survived `WHERE` forms a single implicit group and `HAVING` is one test over that group's aggregates: the query returns either the single aggregate row or no rows at all. ## Three-valued logic applies to both Like `WHERE`, `HAVING` keeps only what evaluates to TRUE. If a group's aggregate is NULL — `SUM(x)` over a group whose `x` values are all NULL, for instance — then `SUM(x) > 0` evaluates to UNKNOWN and the group is discarded, exactly as an UNKNOWN row predicate discards a row. ## What interviewers listen for "HAVING is just WHERE for GROUP BY queries" misses that they filter different objects and see different data. `WHERE COUNT(*) > 1` is the classic error. Putting an ordinary row predicate in `HAVING` because "the query has a GROUP BY" is legal only for grouping columns and misleading even then. And expecting `HAVING` to surface empty groups never works: a group exists only because at least one row produced it, so `COUNT(*)` for any returned group is at least 1.