Query Execution Order
Concepts covered: Execution Order
SQL is written in one order and evaluated in another. The text reads SELECT, FROM, WHERE, ORDER BY, LIMIT from top to bottom; the engine evaluates FROM first and SELECT close to last. The CTE from the previous section already showed the gap: active_set was complete before the outer query read its first row, even though the outer SELECT sat below it on the page. Alias errors in a long query trace back to that gap, and so does a surprising HAVING or a filter sitting in the wrong clause.
| Step | Clause | What it works on |
|---|---|---|
| 1 | WITH | Defines each CTE so later clauses can read it by name |
| 2 | FROM and JOIN | Builds the working row set from tables, CTEs and derived tables |
| 3 | WHERE | Drops individual rows before any grouping exists |
| 4 | GROUP BY | Folds the surviving rows into groups |
| 5 | HAVING | Drops whole groups, using aggregates |
| 6 | SELECT and DISTINCT | Computes expressions, creates aliases, removes duplicate output rows |
| 7 | ORDER BY | Sorts the finished rows; sees every alias |
| 8 | LIMIT and OFFSET | Keeps a slice of the sorted rows |
The order is a contract about results, not a description of the machine. An optimizer is free to push a WHERE predicate into an index scan, stop a sort early to satisfy a LIMIT, or flatten a CTE into the query that reads it, as long as the rows that come out match what the logical order would have produced. Reasoning about correctness uses the table above; reasoning about speed uses the query plan.
What an alias can see
A column alias is created when SELECT evaluates its expression list, which is step 6. Any clause that runs earlier cannot reference it, because at that moment the alias does not exist. WHERE runs at step 3, so SELECT total * 1.2 AS gross ... WHERE gross > 100 fails with an unknown-column error in PostgreSQL and MySQL, and in SQL Server and BigQuery as well. An alias that shares its name with a stored column fails more quietly: WHERE reads the stored column instead. ORDER BY runs at step 7 and can sort by gross without restating the arithmetic.
Each query defines an alias tax. Which one runs without error in PostgreSQL and MySQL?
- SELECT total * 0.2 AS tax FROM orders WHERE tax > 10
- SELECT total * 0.2 AS tax FROM orders ORDER BY tax DESC
- SELECT total * 0.2 AS tax, tax * 2 AS double_tax FROM orders
The same rule decides where an aggregate may appear. SUM(total) is computed when groups are folded at step 4, so a filter on the sum belongs in HAVING at step 5, and WHERE SUM(total) > 100 is rejected because there are no groups yet when WHERE runs. SQLite and DuckDB are among the engines that accept an alias in WHERE; they silently substitute its expression. The query still evaluates in the standard order; the engine has only saved the typing.
SELECT id, user_id, total, total * 0.2 AS tax
FROM orders
WHERE total >= 50
ORDER BY tax DESC| id | user_id | total |
|---|---|---|
| 1001 | 98 | 40 |
| 1002 | 99 | 75 |
| 1003 | 101 | 20 |
| 1004 | 102 | 15 |
| 1005 | 103 | 60 |
| 1006 | 98 | 90 |
| id | user_id | total | tax |
|---|---|---|---|
| 1006 | 98 | 90 | 18 |
| 1002 | 99 | 75 | 15 |
| 1005 | 103 | 60 | 12 |
WHERE total >= 50 ran before tax existed, so it filtered on the stored column and kept 3 of the 6 orders. SELECT then computed tax for those 3 rows only, and ORDER BY tax DESC sorted the finished rows: 18.0, 15.0, 12.0. Change ORDER BY tax to ORDER BY total and the order is identical, because tax is a fixed multiple of total; only one of those 2 spellings is available to WHERE.
Reading a query in execution order
When a long query returns the wrong number of rows, read it in the order the engine does rather than the order on the page. Start at FROM and name the row set. Apply WHERE and count the rows that survive; once the groups are folded, look at what SELECT has to work with. The wrong count is found at the step where the row set changed size in a way the author did not expect. That step is rarely SELECT, which changes columns rather than rows. The exception is DISTINCT, which collapses identical output rows at step 6 and belongs on the checklist whenever a count comes back too small.
About this lesson
This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.