Does SQL's logical clause order describe how the engine actually executes the query?
answer
- one order defines meaning, another does the work
- only the result has to match
- the engine may filter early or stop early
- think as-if, not step-by-step
- clause position is about semantics, not cost
basics
~20 sNo. The logical order defines what a query means; it is an as-if rule. An engine may do the work in any order, with any algorithm, or skip it entirely, as long as the rows returned match what the logical order specifies.
solid answer
~50 sThe FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → limit sequence is a **semantic** definition, not a runtime recipe. It tells you what result the query must produce; it says nothing about the steps taken to produce it. In practice an engine will apply a filter while it reads a table rather than after the join completes, evaluate a join in whichever direction is cheaper, satisfy an `ORDER BY` by walking an already-ordered access path instead of sorting, stop early once a `FETCH FIRST n ROWS ONLY` is satisfied, or drop a computation whose output is never used. All of that is legal precisely because the result is unchanged. Two practical consequences: never reason about performance from clause order — moving a predicate between `ON` and `WHERE` changes *meaning* for outer joins, not cost — and remember the as-if guarantee covers the returned rows, not side effects or which expressions get evaluated.
code
sql · 8 lines-- Logically: FROM -> WHERE -> SELECT -> ORDER BY -> row limit
SELECT id, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
FETCH FIRST 10 ROWS ONLY;
-- An engine may apply the filter while reading, walk an ordered
-- access path, and stop after ten rows: no full sort, same result.go deeper
Learn the logical order first and use it to explain errors; just remember it describes what the query means, not the steps the database takes.
Be able to give concrete examples of an engine legally departing from the written pipeline — early filtering, an unsorted top-n, streaming rows between stages — and state the as-if rule that permits them.
Show the judgment: use clause order to reason about correctness and never about cost, know that the as-if guarantee covers returned rows rather than which expressions get evaluated, and refuse rewrites justified by 'this clause runs earlier'.
Own the distinction as a standard: keep 'reordering clauses for speed' out of team guidance, insist that any predicate move across ON and WHERE be justified by meaning, and make deterministic ordering a requirement wherever results are paged or compared.
## Two different questions "What does this query mean?" and "what will the machine do?" are separate questions with separate answers. The logical clause order answers the first. It is a definition in the language standard: the result of a `SELECT` is defined as though `FROM` were evaluated, then `WHERE`, then `GROUP BY`, then `HAVING`, then the select list, then `DISTINCT`, then `ORDER BY`, then the row limit. Nothing in that definition obliges an engine to perform those steps, in that order, or at all. ## The as-if rule What an engine actually promises is an as-if guarantee: the rows it returns are the rows the logical pipeline defines. Inside that promise it is free to do whatever is cheapest. Familiar examples: - A `WHERE` predicate on a single table can be applied *while that table is being read*, long before any join completes — even though the definition places `WHERE` after `FROM`. - A join written `a JOIN b` may be executed by reading `b` first. - An `ORDER BY` may cost nothing if rows already arrive in that order via an ordered access path; conversely a query with no `ORDER BY` may still come back sorted as an accident of the plan, which is why you must never rely on it. - With `ORDER BY x FETCH FIRST 10 ROWS ONLY`, the engine may keep only the best ten rows seen so far rather than sorting the whole input, because the ten rows it returns are the same ten. - Results are usually **pipelined**: rows stream through stages rather than each clause materialising a complete intermediate table. The mental model of "one full table per stage" is a teaching device, not a description of memory. ## What this does and does not license you to conclude **Do not infer cost from clause position.** "`WHERE` runs before `SELECT`, so putting the filter there is faster than filtering outside" is a category error; both forms mean the same thing and typically produce identical work. Likewise, moving a predicate from `WHERE` into a join's `ON` clause is not a performance technique — for an **inner** join the two are equivalent in meaning, and for an **outer** join they mean different things, which is the only reason to choose one over the other. **Do infer meaning from clause position.** This is what the logical order is *for*. Whether the alias is visible, whether an aggregate exists yet, whether NULL-extended outer-join rows survive, whether the limit applies before or after sorting — all of these are semantic questions answered by the pipeline, and the engine's freedom does not touch them. **Do not over-trust the guarantee.** The as-if rule covers the returned rows. It does not promise that only the expressions you expect are evaluated, or that they are evaluated only for rows that pass the filter. SQL specifies no left-to-right short-circuit order for `AND` and `OR` operands, and an expression in the select list may be computed for rows a filter elsewhere in the query would have removed — which is how a query that appears to exclude zero denominators can still raise a division-by-zero error. Guard such expressions explicitly with `NULLIF` or `CASE` instead of relying on evaluation order. ## How to use this in a review or a debugging session When someone reports a *wrong result*, reach for the logical order: which stage produced the value the query is complaining about, and which stage tried to read it. When someone reports a *slow query*, the logical order is nearly useless — the shape of the plan the engine chose is the subject, and rewriting clause order to "help the optimiser" is cargo cult unless the rewrite changes what the query means or what the engine can prove about it. The genuine exceptions worth knowing are rewrites that change what is *provable*, not what is preferred: wrapping a column in a function inside a predicate changes the predicate from a simple comparison on a column to a comparison on a computed value, and that can change the access paths available. That is a property of the expression, not of which clause it sits in. ## The interview answer in one breath "The clause order defines the query's meaning; the engine only has to match the result. So I use the order to reason about semantics and errors, and I never use it to reason about speed."
- Does moving a predicate from WHERE into a join's ON clause make the query faster?No. For an inner join the two are equivalent in meaning and normally produce the same work. For an outer join they mean different things — an `ON` predicate is applied while the join is formed, so non-matching rows are still NULL-extended and preserved, whereas the same predicate in `WHERE` discards them. Choose on semantics, never on hoped-for speed.
- If the engine can reorder freely, why does the logical order matter at all?Because it is the definition of the answer. Every question about which names are in scope, whether an aggregate exists yet, whether outer-join rows survive, and whether the row limit applies before or after sorting is settled by the logical order — and the engine's freedom is bounded by having to reproduce exactly that result.
- A query without ORDER BY returned rows in a convenient order for months, then stopped. What happened?Nothing was ever guaranteed. Without `ORDER BY` the result is an unordered bag, and the apparent ordering was a by-product of the plan the engine happened to choose. A data-volume change, new statistics, or a different access path can change it at any time. If the order matters, state it in `ORDER BY`.
A recipe says to sift the flour before adding the eggs; a practised cook may do the steps in a different order, or skip one entirely, as long as the cake that comes out is the same cake. The recipe defines the cake, not the cook's hands.
saying these in an interview costs you the question
- Says the engine literally executes FROM, then WHERE, then GROUP BY
- Claims moving a predicate into ON speeds up an inner join
- Assumes AND short-circuits left to right like a programming language
- Relies on row order from a query with no ORDER BY
- Thinks ORDER BY always forces a full sort of the result