Order of SQL Query Execution

SQL is declarative: you describe the result you want, and the engine is free to compute it any way that produces that result — so there are really two orderings, and conflating them is the bug to avoid. The logical clause order is a semantic contract that defines what a query means (which names are in scope, what a row even is at each stage); the physical execution plan is what the optimizer actually runs, and it reorders, fuses, and skips steps freely as long as the answer matches the logical contract.

The logical clause order (the semantic contract)

This is the order a SQL engine pretends to evaluate clauses in. It is not about efficiency — it is about meaning. Each phase takes the relation produced by the previous phase and emits a new one, which is why a name created late cannot be used early.

1. FROM / JOIN ... ON — take the source tables, form the row combinations, and apply each ON predicate. For an OUTER JOIN this is also where unmatched rows are padded with NULLs.
2. WHERE — filter individual rows. No aggregates and no SELECT aliases are visible yet, because neither exists at this point.
3. GROUP BY — collapse the surviving rows into one row per group. After this step a "row" is a group, and only grouping keys and aggregates are legal references.
4. HAVING — filter groups using aggregate results (e.g. COUNT(*) > 3).
5. SELECT — evaluate the output expressions and assign column aliases. This is the first moment an alias like AS total comes into existence.
6. DISTINCT — deduplicate the projected rows.
7. ORDER BY — sort. Because it runs after SELECT, it can reference output aliases.
8. LIMIT / OFFSET (FETCH) — keep only the requested slice of the sorted output. Last, so a limit always applies to the fully filtered, grouped, and sorted result.

The two practical consequences fall straight out of this order: a SELECT alias is invisible to WHERE (alias is born in step 5, WHERE ran in step 2), and per-group conditions belong in HAVING, not WHERE.

Worked trace with real values

Two tables. We want, for each department with more than two engineers earning over $80k, the department and headcount — highest headcount first, top 2 only.

-- emp
id | name    | dept_id | salary
 1 | Asha    |   10    | 120000
 2 | Ben     |   10    |  95000
 3 | Cara    |   10    |  70000
 4 | Dev     |   20    |  88000
 5 | Esi     |   20    |  82000
 6 | Finn    |   30    |  99000

-- dept
dept_id | name
   10   | Platform
   20   | Payments
   30   | Growth

SELECT  d.name AS dept, COUNT(*) AS senior_count
FROM    emp e
JOIN    dept d ON e.dept_id = d.dept_id
WHERE   e.salary > 80000
GROUP BY d.dept_id, d.name
HAVING  COUNT(*) > 2
ORDER BY senior_count DESC
LIMIT 2;

Walking the logical contract phase by phase:

1. FROM/JOIN ... ON → pair each emp with its dept by dept_id. All 6 emps match a dept, giving 6 joined rows.
2. WHERE salary > 80000 → drop Cara (70k). 5 rows survive: Asha, Ben (dept 10); Dev, Esi (dept 20); Finn (dept 30).
3. GROUP BY d.dept_id, d.name → three groups: 10 has 2 rows, 20 has 2 rows, 30 has 1 row.
4. HAVING COUNT(*) > 2 → every group has 2 or fewer rows, so all three are dropped. Result is empty.
5. SELECT, ORDER BY, LIMIT → nothing to project, sort, or slice. Final result: 0 rows.

The empty result is the point: it surprises people who mentally run HAVING COUNT(*) > 2 against the unfiltered headcount (dept 10 has 3 people total). But WHERE runs first and removed Cara, so the count HAVING sees is 2. Order changes the answer.

Now the physical side. Run the same query under EXPLAIN ANALYZE in Postgres and you do not see eight discrete steps. You see something like an index scan on emp(salary) that applies the WHERE while reading, a hash join, a HashAggregate that computes COUNT(*) and the HAVING filter together, and a bounded Top-N heapsort for ORDER BY ... LIMIT 2 that never materializes a full sort. Same answer, completely different sequence of operators.

Why the naive "processes data efficiently" framing is wrong

The original page said the database follows the clause order to "process data efficiently." That is backwards. The logical order is a correctness definition, and following it literally would often be the slow path. A real optimizer deliberately departs from it:

• Predicate pushdown: a WHERE condition on a single table can be evaluated before the join — or even inside the storage scan via an index — even though logically WHERE sits "after" FROM/JOIN. Filtering early shrinks the join input.
• Top-N short-circuit: ORDER BY ... LIMIT 10 does not sort the whole result and throw most away; the engine keeps a bounded heap of 10 and discards the rest as it scans — O(n·log k) instead of O(n·log n).
• Join reordering & operator fusion: a cost-based optimizer reorders joins by estimated cardinality and fuses filter+aggregate into one pass. The "FROM picks the first table" story is fiction.

So the correct mental model is: logical order is the contract the engine must honor; the physical plan is whatever it judges cheapest while honoring that contract. When you need to know what actually ran, read EXPLAIN / EXPLAIN ANALYZE — do not infer it from clause order.

Pitfalls

• Aliasing a column then filtering on it in WHERE. SELECT salary*12 AS annual ... WHERE annual > 1000000 fails: WHERE (logical step 2) runs before the SELECT alias (step 5) exists. Repeat the expression in WHERE, or wrap the query and filter in an outer one. ORDER BY annual, by contrast, works — sorting runs after SELECT. (MySQL is laxer about aliases in some clauses; the standard and Postgres are strict — do not rely on the lax behavior.)
• Row filter vs. group filter. Putting an aggregate in WHERE (WHERE COUNT(*) > 2) is an error — aggregates do not exist until GROUP BY has run. Per-row conditions go in WHERE; per-group conditions go in HAVING. Choosing WHERE when you can also lets the optimizer cut rows before grouping.
• OUTER JOIN predicate in WHERE silently becomes an INNER JOIN. A LEFT JOIN pads unmatched right-side rows with NULL during the join phase; a later WHERE right.col = 'x' then rejects those NULL rows, undoing the outer-ness. Put the condition in the ON clause if it must be evaluated during the join.
• Assuming physical order from clause order — the big one. "WHERE runs first so it's already optimal" or "LIMIT is cheap because it's last" are both wrong reasoning. The plan may scan an unindexed column or do a full sort before the limit. Always confirm with EXPLAIN ANALYZE against real data volumes.
• OFFSET on deep pages. LIMIT 20 OFFSET 100000 still computes and discards the first 100,000 rows because LIMIT/OFFSET is logically last. Use keyset (seek) pagination — WHERE id > :last_id ORDER BY id LIMIT 20 — to push the bound into a step that can use an index.

Takeaways

• There are two orderings: a fixed logical order that defines meaning and name scope (FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT), and a physical plan the optimizer chooses for speed. Never conflate them.
• The logical order is a correctness contract, not an efficiency recipe — predicate pushdown, top-N limits, and join reordering all violate the literal sequence while preserving the answer.
• Name scope follows the logical order: WHERE can't see SELECT aliases or aggregates; HAVING filters groups; ORDER BY can use aliases because it runs last-but-one.
• To know what actually executes, read EXPLAIN / EXPLAIN ANALYZE — clause order tells you what the query means, never how it runs.

Re-authored and deepened for this guide. The logical clause-evaluation order follows the SQL standard's conceptual processing model as described in the PostgreSQL documentation ("SELECT" reference and "Queries" chapter) and Itzik Ben-Gan's T-SQL Querying, which popularized the "logical query processing" phase model. The logical-vs-physical distinction, predicate pushdown, top-N sort, and join reordering draw on Hellerstein, Stonebraker & Hamilton's Architecture of a Database System and the PostgreSQL EXPLAIN / planner documentation. The original lesson's "processes data efficiently" framing was corrected because it conflated logical semantics with the physical execution plan; missing JOIN/ON, DISTINCT, and LIMIT/OFFSET phases were added.