What is the logical order of execution of a SQL SELECT statement's clauses?
Quick Answer
SQL is declarative, but the engine conceptually processes clauses in this order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT (including window functions) → DISTINCT → ORDER BY → LIMIT/OFFSET. That's why you can't reference a SELECT alias in WHERE, but you can in ORDER BY. The alias doesn't exist yet when WHERE runs.
Detailed Answer
You write a SELECT statement top to bottom, but the database evaluates it in a different logical order. Knowing that order explains several rules that would otherwise look arbitrary.
The logical order
1. FROM / JOIN -- build the base row set
2. WHERE -- filter individual rows
3. GROUP BY -- bucket remaining rows into groups
4. HAVING -- filter groups
5. SELECT -- compute output expressions (incl. window functions)
6. DISTINCT -- remove duplicate output rows
7. ORDER BY -- sort the result
8. LIMIT / OFFSET -- take a slice
This is the logical order, not necessarily the physical one. Real optimizers reorder execution — for example, pushing a WHERE predicate down before a join, or picking an index that satisfies ORDER BY for free — as long as the final result matches.
Why this explains common gotchas
You can't use a SELECT alias in WHERE:
-- Fails: "total" doesn't exist yet when WHERE is evaluated
SELECT price * quantity AS total FROM orders WHERE total > 100;
-- Works: recompute the expression, or move the filter to HAVING with a subquery/CTE
SELECT * FROM (
SELECT price * quantity AS total FROM orders
) t WHERE total > 100;
But you can use it in ORDER BY. By the time ORDER BY runs, SELECT has already executed and the alias exists:
SELECT price * quantity AS total FROM orders ORDER BY total DESC;
WHERE can't filter on aggregates, HAVING can:
-- Fails: WHERE runs before GROUP BY, so COUNT(*) doesn't exist yet
SELECT customer_id, COUNT(*) FROM orders WHERE COUNT(*) > 5 GROUP BY customer_id;
-- Correct: HAVING runs after grouping
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;
Window functions see SELECT-time data, not raw rows. They run after WHERE/GROUP BY/HAVING but before DISTINCT/ORDER BY/LIMIT. That's why you generally can't reference a window function's result directly in the same SELECT's WHERE clause — you need to wrap it in a subquery or CTE and filter in the outer query instead.