WHERE vs HAVING — The Mistake Everyone Makes Exactly Once

At some point you write this:

SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE SUM(total) > 1000
GROUP BY customer_id;

And your database throws back something like aggregate function calls cannot appear in WHERE clause. It feels like an arbitrary restriction — you can clearly see the aggregate right there in the WHERE clause, why can't it just... use it?

The answer isn't a syntax quirk. It's that the aggregate doesn't exist yet at the point WHERE runs. SQL is not executed top-to-bottom the way it's written. Understanding the real order fixes this permanently, along with a handful of other bugs you haven't hit yet.

The order SQL actually runs in

Written order and logical execution order are different things. Roughly, a query executes like this:

FROM      -- gather the rows, apply joins
WHERE     -- filter individual rows
GROUP BY  -- collapse rows into groups
HAVING    -- filter groups
SELECT    -- compute the output columns (including aggregates, aliases)
ORDER BY  -- sort the final result

WHERE runs in step two. SUM() is computed in step five, three stages later. When the database evaluates your WHERE clause, grouping hasn't happened yet — there's no "group" for SUM(total) to sum over. It's not that the database refuses to let you use an aggregate in WHERE; it's that the aggregate is a meaningless expression at that point in execution. There's nothing to compute.

HAVING exists specifically because someone needs a stage after grouping where filtering is still possible:

SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;

Same intent, correct clause. HAVING runs in step four, after groups exist, so SUM(total) has something real to evaluate.

The mistake in the other direction, which is more expensive

Once people learn "aggregates go in HAVING," a lot of them overcorrect and start putting everything related to the aggregated column in HAVING — including filters that don't need an aggregate at all:

-- works, but wasteful
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING customer_id IN (SELECT id FROM customers WHERE region = 'EU');

This runs correctly. It's also slower than it needs to be, because HAVING filters groups after the database has already scanned, joined, and aggregated every single row — including all the non-EU ones you were going to throw away anyway. Compare:

-- correct and faster
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'EU')
GROUP BY customer_id;

Here, WHERE throws out the non-EU rows in step two, before the expensive grouping and summing work happens in steps three and five. The database aggregates only the rows that were going to survive anyway. On a small table you won't feel the difference. On a table with tens of millions of rows, this is the difference between a query that returns in 200ms and one that scans the whole table for no reason.

The rule, stated plainly

WHERE filters rows, before grouping. HAVING filters groups, after grouping.

If your condition only needs columns that exist on the raw row — a date, a status, a foreign key — it belongs in WHERE, even if the query also happens to have a GROUP BY somewhere else in it. HAVING is reserved for conditions that literally cannot be evaluated until aggregation has happened: totals, counts, averages, anything produced by an aggregate function.

A useful gut check: if you could answer the filter condition by looking at a single row in isolation, it's a WHERE. If you need to have already looked at every row in the group to know the answer, it's a HAVING.

Why this matters beyond passing the query

This isn't really a SQL trivia rule — it's a forcing function that makes you think about when information becomes available. That instinct transfers directly to reasoning about query performance, to understanding why some filters can use an index and others can't (an index can help WHERE skip rows before they're read; it can't help HAVING skip groups that don't exist yet), and to debugging query plans where the biggest cost is almost always "we aggregated rows we didn't need to."

Learn the execution order once, and a dozen SQL "quirks" stop being quirks.

If you want to build this instinct by doing rather than reading, SQL Quest has a full lesson on query execution order with a live database in your browser — free, no signup.