Chapter 21 · advanced · 8 min
Query execution order
You've now used SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY, but never asked why some combinations work and others don't. Why can't WHERE filter on an aggregate? Why can ORDER BY use a SELECT alias but WHERE can't? The answer is that SQL executes clauses in a different order than you write them.
We'll use a simple orders table to see the run order in action.
The dataset
An orders table: one row per order, with an amount and a region.
Schema
| id | INT |
| customer | TEXT |
| amount | NUMERIC |
| region | TEXT |
Example data
Written order vs. run order
You type a query as SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT. SQL actually runs it in this order instead:
| Step | Clause | Job |
|---|---|---|
| 1 | FROM / JOIN | gather the rows |
| 2 | WHERE | filter individual rows |
| 3 | GROUP BY | form groups |
| 4 | HAVING | filter groups |
| 5 | SELECT | compute output columns, including aggregates and aliases |
| 6 | DISTINCT | de-duplicate |
| 7 | ORDER BY | sort |
| 8 | LIMIT | trim |
SELECT is written first but runs almost last. Everything below follows from that one fact.
Why WHERE can't use an aggregate
WHERE runs before GROUP BY; at that point, no aggregate has been computed yet, so writing WHERE SUM(amount) > 150 fails with an error like "aggregate functions are not allowed in WHERE". HAVING runs after grouping, once SUM(amount) actually exists per group, which is exactly why aggregates belong in HAVING, not WHERE.
Before you run it — North totals 500.00 and South totals 245.00. Do both regions clear the 150 bar?
Why ORDER BY can use a SELECT alias, but WHERE can't
SELECT computes its aliases before ORDER BY runs, so ORDER BY doubled works, referencing an alias defined in SELECT. But WHERE runs before SELECT even exists yet, so WHERE doubled > 100 would fail: as far as WHERE is concerned, doubled hasn't been computed.
Before you run it — Farid's amount is 300.00, the highest. Where does his row land after sorting by doubled DESC?
Where window functions fit in
Window functions run as part of SELECT, after WHERE/GROUP BY/HAVING, but before ORDER BY/LIMIT. That means you can't filter on a window function's result directly in WHERE (it doesn't exist yet at that stage). The fix is to compute it in a CTE, then filter the outer query, where the window function's result is just an ordinary column.
Before you run it — this keeps only rank 1 and 2 by amount. Which two customers do you expect?
If you've used Excel or Google Sheets
Spreadsheets don't really have this problem, because a formula in one cell can freely reference another cell's already-computed value regardless of "order"; there's no equivalent gotcha to teach. That's actually a good way to explain why this trips people up coming from Excel: spreadsheets are computed cell-by-cell with automatic dependency resolution, while SQL executes clause-by-clause in a fixed pipeline. Once you know the pipeline, the "why won't this work" questions stop feeling random.
Ready to practice? Grab's above-average drivers question below leans on this exact ordering; it needs a subquery precisely because WHERE can't see an aggregate directly.
Keep this order in your head: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, and most of SQL's "why won't this work" moments stop being mysterious. It's also exactly why CTEs and subqueries exist: whenever you need something SELECT computed to be filterable, you wrap it and filter one level up.