In what order does SQL logically evaluate the clauses of a SELECT statement?
answer
- written order is not evaluation order
- the row set must exist before it can be filtered
- output names are created near the end
- starts at FROM, ends at the row limit
- SELECT is stage five, just before ORDER BY
basics
~10 sSQL is written SELECT-first but evaluated FROM-first: FROM and its joins, then WHERE, GROUP BY, HAVING, the SELECT list, DISTINCT, ORDER BY, and finally the row limit. Each stage consumes the previous stage's output.
solid answer
~40 sYou write a query starting with `SELECT`, but the language defines its meaning starting with `FROM`. The logical pipeline is: **FROM** (build the working row set from the tables and joins), **WHERE** (discard rows failing the predicate), **GROUP BY** (collapse surviving rows into groups), **HAVING** (discard whole groups), **SELECT** (compute the output expressions and mint aliases; window functions are evaluated here), **DISTINCT** (remove duplicate output rows), **ORDER BY** (sort the result), and last **LIMIT / OFFSET / FETCH FIRST** (cut the sorted result down). Each stage consumes the previous stage's output, which is exactly why `WHERE` cannot see an alias created in `SELECT`, why a group-level predicate has to wait for `HAVING`, and why a row limit without `ORDER BY` gives you an arbitrary slice.
code
sql · 8 linesSELECT d.name, COUNT(*) AS headcount -- 5. select list computed, alias minted
FROM employees e -- 1. FROM: tables and joins
JOIN departments d ON d.id = e.dept_id
WHERE e.active = TRUE -- 2. WHERE: row filter
GROUP BY d.name -- 3. GROUP BY: rows become groups
HAVING COUNT(*) > 5 -- 4. HAVING: group filter
ORDER BY headcount DESC -- 6. ORDER BY: may use the alias
FETCH FIRST 10 ROWS ONLY; -- 7. row limit, applied lastgo deeper
Be able to recite the pipeline in order without hesitating: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, row limit. That list is the answer to a surprising number of screening questions.
Explain what each stage consumes and produces, and use the order to diagnose errors on the spot — which stage made the reference, and which stage would have created the thing it asked for.
Draw the line between the logical contract and what the engine actually does at run time, and use the pipeline to reason about outer-join filtering, non-deterministic row limits, and where to introduce a derived table so a name exists early enough.
Treat the pipeline as the shared vocabulary a team debugs with: reviews and coding standards land better when 'this predicate is in the wrong stage' replaces 'this query looks wrong', and it keeps portable semantics rather than one engine's tolerance as the baseline.
## The written order is not the evaluation order SQL's clause syntax was designed to read like English, not to describe processing. You type `SELECT ... FROM ... WHERE ...`, but the standard defines the *meaning* of a query as a pipeline that begins at `FROM`. Every clause consumes a table of rows and produces a table of rows, and each one may only reference what earlier stages have already produced. Nearly every confusing error message in everyday SQL falls out of that single fact. ## The stages, one by one **1. FROM.** Table expressions are resolved first: base tables, derived tables, and every join in the `FROM` clause. Joins themselves have an internal order — each `JOIN ... ON` produces an intermediate result that the next join consumes — and for outer joins the `ON` predicate is applied *during* this stage, before `WHERE` exists. The output is one wide row set whose column names are the qualified columns of the sources. **2. WHERE.** A row-level filter. It sees only the columns produced by `FROM`, and it keeps a row only when the predicate evaluates to `TRUE`; rows evaluating to `FALSE` or `UNKNOWN` are discarded. Because it runs before grouping, no aggregate value exists yet, and because it runs before the select list, no output alias exists yet. **3. GROUP BY.** Surviving rows are partitioned into groups by the grouping expressions. After this stage the working set is no longer individual rows — it is one row per group — which is why later clauses may only reference grouping expressions or aggregates over each group. **4. HAVING.** A group-level filter, evaluated after grouping, so aggregates are available to it. **5. SELECT.** Now, and only now, the output expressions are computed and column aliases are assigned. Window functions are evaluated at this point too, over the rows that survived `WHERE`/`GROUP BY`/`HAVING`. **6. DISTINCT.** If present, duplicate output rows are removed after the select list has been computed — so duplicate elimination applies to the projected values, not the underlying rows. **7. ORDER BY.** Sorting happens on the result of the select list, which is why `ORDER BY` is the one clause that can reference an output alias by name. **8. LIMIT / OFFSET / FETCH FIRST.** The row limit is applied dead last, to the already-sorted result. ## Why the order explains the errors you hit ```sql SELECT price * 1.2 AS gross FROM products WHERE gross > 100; -- error: no column named gross ``` `WHERE` runs at stage 2; `gross` is not created until stage 5. The same reasoning explains why an aggregate cannot appear in `WHERE` (there are no groups yet at stage 2), why `ORDER BY gross DESC` in the same query is perfectly legal (stage 7 sees stage 5's output), and why `FETCH FIRST 3 ROWS ONLY` without `ORDER BY` returns three arbitrary rows rather than the "first three" — the limit is applied to an unordered result. It also explains a subtler pairing: with `SELECT DISTINCT`, the standard restricts `ORDER BY` to expressions that appear in the select list, because by the time sorting happens the pre-projection columns have already been thrown away by duplicate elimination. ## Logical, not physical This pipeline is a *semantic contract*, not an execution plan. An engine is free to run the work in any order it likes, use any algorithm, and skip work entirely, as long as the rows it returns are the rows the logical order specifies. A query with an `ORDER BY` and a small `FETCH FIRST` need not sort every row; a filter written in `WHERE` may in practice be applied while the table is being read, before any join completes. Candidates who say "the engine executes FROM, then WHERE, then..." have swapped a semantic rule for a runtime claim — the rule tells you what the query *means*, and the engine guarantees only that its output matches. ## What interviewers listen for The recitation is the easy half. The half that separates candidates is using the order as a diagnostic tool: given an "unknown column" or "aggregate not allowed here" error, say which stage the reference was made in and which stage produced the thing it wanted, then reach for the standard fixes — repeat the expression, wrap the query in a derived table or a `WITH` clause so the alias exists in an earlier stage, or move a group-level predicate to `HAVING`.
- Where does DISTINCT sit in that pipeline, and what does its position imply for ORDER BY?Duplicate elimination happens after the select list is computed and before sorting. Because the pre-projection columns are gone by then, the standard restricts `ORDER BY` under `SELECT DISTINCT` to expressions that appear in the select list — you cannot sort a de-duplicated result by a column you did not project.
- If the row limit is applied last, what does that mean for a query that has LIMIT but no ORDER BY?The result is non-deterministic. The limit slices an unordered result, so the engine may hand back any n rows and may return a different n rows on the next run, after a data change, or under a different plan. Any query that limits rows should specify a deterministic ordering.
- Given that FROM is evaluated first, why can a WHERE predicate on the right-hand table of a LEFT JOIN throw the outer join away?`FROM` produces the join result including the NULL-extended non-matching rows; `WHERE` then filters that result. A predicate like `r.status = 'X'` is UNKNOWN for the NULL-extended rows, so they are discarded and the query behaves like an inner join. Filtering the right side belongs in the join's `ON` clause.
Think of an assembly line: the parts bin (FROM) has to be loaded before anything can be rejected (WHERE), items are boxed into cartons (GROUP BY) before whole cartons can be rejected (HAVING), and the labels (SELECT aliases) are printed near the end of the line — a rejection station earlier on cannot read a label that has not been printed yet.
saying these in an interview costs you the question
- Says SQL executes top to bottom starting with SELECT
- Thinks LIMIT is applied before ORDER BY
- Believes WHERE can filter on aggregate values
- Claims the logical order is the engine's actual execution plan
- Places DISTINCT before the select list is computed