SQL Reference ยท Dialects and Concepts
SQL logical order of execution
The logical order in which SQL evaluates a query: FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT, and why aliases fail in WHERE.
You write a query starting with SELECT, but the database does not start there. SQL defines a logical order of evaluation that is different from the order you type the clauses in. Knowing it explains most "why can't I use my alias here?" and "why is my filter wrong?" questions.
The order is logical, not physical. The optimizer may reorder, merge and skip steps, as long as the result matches what the logical order would give. Think of it as the meaning of the query, not the plan.
SELECT ... -- 5 FROM ... -- 1 WHERE ... -- 2 GROUP BY ... -- 3 HAVING ... -- 4 ORDER BY ... -- 6 LIMIT ... -- 7
The steps
FROMandJOIN. Build the input rows: tables, joins, derived tables and CTEs. Join conditions inONare applied here.WHERE. Keep only rows for which the condition is true. Aggregates and window functions are not allowed, because no groups exist yet.GROUP BY. Collapse the remaining rows into groups.- Aggregate functions are computed per group, then
HAVINGkeeps only groups for which its condition is true. - Window functions are computed over the rows that survived. They see the result of grouping, but not
DISTINCTorLIMIT. SELECT. Evaluate the select list and assign aliases.DISTINCTremoves duplicate result rows.- Set operators (
UNION,INTERSECT,EXCEPT) combine the results of whole query blocks. ORDER BYsorts the rows. It can use select-list aliases because the select list has been evaluated.LIMIT/OFFSET/FETCHcut the sorted result.
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
What follows from the order
- Aliases are not visible in
WHERE,GROUP BYorHAVINGin standard SQL, becauseSELECTruns later. Repeat the expression or wrap the query in a subquery or CTE. Some databases are more lenient (SQLite accepts aliases inWHERE; MySQL inGROUP BYandHAVING; PostgreSQL inGROUP BYandORDER BY), while SQL Server and Oracle are strict. Do not rely on leniency. - Aggregates cannot appear in
WHERE. UseHAVING, or a subquery. - Window functions cannot appear in
WHERE,GROUP BYorHAVING. To filter on a rank, compute it in a subquery or CTE and filter in the outer query. - A
LEFT JOINfilter inWHEREturns it into an inner join.WHERE right_table.col = 'x'removes the null-extended rows. Put the condition inONto keep unmatched left rows. DISTINCTcomes afterSELECT, so it applies to the whole select list, not just the first column.ORDER BYcan use things not in the select list (unlessDISTINCTor a set operator is involved), andLIMITalways acts last.ORDER BYafterUNIONapplies to the combined result and may only use output column names or positions.
Press Run (or Ctrl + Enter) to see the result here.
Dialect differences
- Alias reuse in the same
SELECT. Standard SQL does not let one select item refer to another item's alias. Some products allow it as a convenience (SQLite, MySQL for some uses, and cloud warehouses such as Snowflake and BigQuery), but it is not portable. QUALIFY(Snowflake, BigQuery, Teradata) filters on window functions directly, after window evaluation. Standard databases need a subquery.- Oracle's
ROWNUMis assigned beforeORDER BY, which is whyWHERE ROWNUM <= 5 ORDER BY xdoes not return the top five byx.
Usage notes
- Reading a query: start at
FROM, follow the list above, and finish withSELECT. It is the quickest way to understand a query you did not write. - Debugging: comment out clauses from the bottom up; each step still produces valid intermediate results.
- Performance: filter early. A condition in
WHEREthat removes rows before grouping is cheaper than one inHAVINGthat removes groups afterwards. The optimizer often does this for you, but not for conditions on aggregates. - Subqueries and CTEs are the way to add a step: compute something first, then filter, sort or aggregate it again.
Related
- SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
- OVER clause, WITH (CTE)
- Lessons: SQL queries, Where, Having