SQL Runs in a Different Order Than You Write It

This query looks fine and does not run:

SELECT BlockCode, Acres, Acres * 4.0 AS EstimatedTons
FROM VineyardBlocks
WHERE EstimatedTons > 30;

Msg 207, Level 16, State 1
Invalid column name 'EstimatedTons'.

The alias is defined three lines above the place that can't see it. The reason is one fact: the engine evaluates a query in a different order than you write it. You write SELECT first because the language was designed to read like English. The engine evaluates it sixth.

The logical order:

FROM
ON / JOIN
WHERE
GROUP BY
HAVING
SELECT (including DISTINCT and window functions)
ORDER BY
TOP / OFFSET-FETCH

Hold that list and most of the language's apparent arbitrariness resolves into consequences. This post walks the list and collects what each position explains. Queries run against CellarDB.

FROM and ON build the working set

Everything starts with FROM: the tables, joined into one logical rowset, with ON deciding which combinations survive. Nothing else in the query exists yet. No filters have run, no groups exist, no aliases have been born.

This is why a column reference in ON can only see the joined tables, and why everything downstream operates on the joined result rather than the original tables. When a join multiplies rows, every later clause works with the multiplied rows. The query doesn't remember what the tables looked like before the join.

WHERE runs before groups exist

WHERE filters individual rows of the working set. It runs before GROUP BY, which has two consequences.

First, WHERE cannot see aggregates. "Show me lots with more than 25 work orders" feels like a WHERE problem, but at the time WHERE runs there are no counts to compare; there are only individual work orders. The engine rejects WHERE COUNT(*) > 25 not out of pedantry but because the question doesn't exist yet at that stage.

Second, this is exactly why HAVING exists. It is WHERE for groups, positioned after GROUP BY in the evaluation, where counts and sums are finally real:

SELECT WineLotID, COUNT(*) AS OrderCount
FROM WorkOrders
GROUP BY WineLotID
HAVING COUNT(*) > 25;

HAVING isn't a stylistic variant of WHERE. They run at different times against different things: rows before grouping, groups after. The practical default follows directly: any condition that applies to a single row belongs in WHERE, where it filters earlier and reads as what it is. HAVING is reserved for conditions that can only be asked of a group.

ON and WHERE are different filters

With inner joins, a condition in ON and the same condition in WHERE produce the same result, and people learn to treat them as interchangeable. With outer joins they are not, and the evaluation order says why.

Say you want each customer and their open contracts, including customers with none:

SELECT c.CustomerName, ct.ContractNumber
FROM Customers c
LEFT JOIN BulkContracts ct ON ct.CustomerID = c.CustomerID
WHERE ct.Status = 'Open';

The left join does its job at step 2: customers without contracts survive, carrying NULLs in every contract column. Then WHERE runs at step 3, evaluates NULL = 'Open', and the comparison is not true, so those customers vanish. The LEFT JOIN has been converted to an inner join by a filter that ran one step later. Same condition, placed where the join can apply it instead:

SELECT c.CustomerName, ct.ContractNumber
FROM Customers c
LEFT JOIN BulkContracts ct
  ON ct.CustomerID = c.CustomerID
 AND ct.Status = 'Open';

Now "open" participates in the matching itself, and customers with no open contracts come back with NULLs, the way the left join promised. If you take one habit from this post: on an outer join, conditions about the outer side's matching belong in ON, and a WHERE clause on the nullable side of a left join deserves a hard look every time you see one in review.

SELECT is where aliases are born

Step 6 finally computes your expressions and names them. The implications run in both directions.

Downstream, ORDER BY runs seventh, so it is the one clause where the alias works:

SELECT BlockCode, Acres * 4.0 AS EstimatedTons
FROM VineyardBlocks
ORDER BY EstimatedTons DESC;

Upstream, nothing can use the alias, because it doesn't exist yet: not WHERE, not GROUP BY, not HAVING. The fix for the failing query at the top is to repeat the expression in WHERE, or, when the expression is heavy enough that repeating it offends you, compute it in a CTE and filter the CTE. The CTE's SELECT has already run by the time the outer query's WHERE looks at it, so the order is satisfied.

TOP runs dead last, after ORDER BY, which is the only reason "top ten by volume" works at all. It also confirms the previous post's warning from the other direction: TOP without ORDER BY is "any ten rows," now with an explanation.

Window functions evaluate at SELECT

Window functions are computed at step 6, alongside the other SELECT expressions. They see the rows that survived WHERE and GROUP BY, which is what makes them useful. It also means no earlier clause can filter on them:

SELECT LotCode, Vintage, VolumeGal,
       ROW_NUMBER() OVER (PARTITION BY Vintage ORDER BY VolumeGal DESC) AS SizeRank
FROM WineLots
WHERE SizeRank <= 3;   -- fails: the alias doesn't exist yet

Inlining the window function into WHERE fails differently but for the same underlying reason; SQL Server tells you windowed functions can only appear in SELECT or ORDER BY. The idiom, and this is worth memorizing because you will use it weekly, is to compute the window in a CTE and filter one level out:

WITH Ranked AS (
    SELECT LotCode, Vintage, VolumeGal,
           ROW_NUMBER() OVER (PARTITION BY Vintage ORDER BY VolumeGal DESC) AS SizeRank
    FROM WineLots
)
SELECT LotCode, Vintage, VolumeGal
FROM Ranked
WHERE SizeRank <= 3;

Three largest lots of every vintage. Some dialects (Snowflake, Teradata, Databricks) have a QUALIFY clause that filters on window functions directly. T-SQL does not, and as of SQL Server 2022 still doesn't, so the CTE is the idiom. If you came from one of those platforms, this is the habit to retrain; if T-SQL is your first dialect, you'll appreciate QUALIFY immediately whenever you meet it.

The order is about meaning, not execution

One caveat. This is the logical order: the contract that defines what a query means. It is not a promise about what the engine physically does. The optimizer reorders aggressively, pushes filters into scans, collapses steps, and runs joins in whatever sequence is cheapest, provided the result matches what the logical order requires. Use this model to reason about what a query returns and why a clause can or can't see something. Don't use it to reason about speed; performance gets its own post at the end of the series.

Next month: set-based thinking, and what to do with the row-by-row instincts everyone imports from Excel and procedural code.

— Lo