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 as: `FROM`/`JOIN` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` (including window functions) → `DISTINCT` → `ORDER BY` → `LIMIT`/`OFFSET`. This is 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. Understanding that order explains several rules that 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
Note this is the logical order — real optimizers reorder physical execution (e.g., pushing a WHERE predicate down before a join, or picking an index that satisfies ORDER BY for free) as long as the observable result is identical.
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, which is 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).