Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT   ...      -- 5
FROM     ...      -- 1
WHERE    ...      -- 2
GROUP BY ...      -- 3
HAVING   ...      -- 4
ORDER BY ...      -- 6
LIMIT    ...      -- 7

The steps

  1. FROM and JOIN. Build the input rows: tables, joins, derived tables and CTEs. Join conditions in ON are applied here.
  2. WHERE. Keep only rows for which the condition is true. Aggregates and window functions are not allowed, because no groups exist yet.
  3. GROUP BY. Collapse the remaining rows into groups.
  4. Aggregate functions are computed per group, then HAVING keeps only groups for which its condition is true.
  5. Window functions are computed over the rows that survived. They see the result of grouping, but not DISTINCT or LIMIT.
  6. SELECT. Evaluate the select list and assign aliases.
  7. DISTINCT removes duplicate result rows.
  8. Set operators (UNION, INTERSECT, EXCEPT) combine the results of whole query blocks.
  9. ORDER BY sorts the rows. It can use select-list aliases because the select list has been evaluated.
  10. LIMIT / OFFSET / FETCH cut the sorted result.

Try it

SQL WHERE runs before SELECT, ORDER BY after ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL WHERE filters rows, HAVING filters groups ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

What follows from the order

  • Aliases are not visible in WHERE, GROUP BY or HAVING in standard SQL, because SELECT runs later. Repeat the expression or wrap the query in a subquery or CTE. Some databases are more lenient (SQLite accepts aliases in WHERE; MySQL in GROUP BY and HAVING; PostgreSQL in GROUP BY and ORDER BY), while SQL Server and Oracle are strict. Do not rely on leniency.
  • Aggregates cannot appear in WHERE. Use HAVING, or a subquery.
  • Window functions cannot appear in WHERE, GROUP BY or HAVING. To filter on a rank, compute it in a subquery or CTE and filter in the outer query.
  • A LEFT JOIN filter in WHERE turns it into an inner join. WHERE right_table.col = 'x' removes the null-extended rows. Put the condition in ON to keep unmatched left rows.
  • DISTINCT comes after SELECT, so it applies to the whole select list, not just the first column.
  • ORDER BY can use things not in the select list (unless DISTINCT or a set operator is involved), and LIMIT always acts last.
  • ORDER BY after UNION applies to the combined result and may only use output column names or positions.
SQL Filtering a window function with a subquery ยท database: company

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 ROWNUM is assigned before ORDER BY, which is why WHERE ROWNUM <= 5 ORDER BY x does not return the top five by x.

Usage notes

  • Reading a query: start at FROM, follow the list above, and finish with SELECT. 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 WHERE that removes rows before grouping is cheaper than one in HAVING that 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.