Your SQL Doesn't Run in the Order You Wrote It

When you write SQL, you almost always start with SELECT. Write it long enough and it feels natural to assume: it starts running from SELECT. It doesn’t — SQL is declarative; you write “what you want”, the engine decides “how to run it, in what order”, and the order it runs in is very different from the order you wrote. This post carves that order into your head, and a whole pile of “why does this fail” puzzles that follow resolve in one go.

You write it this way, it doesn’t run this way

The writing order you’re used to is SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. But the engine’s real logical query processing order is this:

Remember it in one sentence: first decide “where it comes from and which rows stay”, then “group and filter groups”, only then “compute the columns you asked for”, and finally sort and take. SELECT is merely what you wrote first; it actually comes fifth.

Three “whys” solved at once

This order isn’t trivia; it directly explains three traps almost everyone has fallen into.

Why can’t WHERE use an alias defined in SELECT? Because WHERE (②) runs before SELECT (⑤), and the alias doesn’t exist yet at that point:

SELECT price * qty AS revenue
FROM orders
WHERE revenue > 1000;   -- ❌ revenue hasn't been computed yet

SELECT price * qty AS revenue
FROM orders
WHERE price * qty > 1000;  -- ✅ write the expression (or wrap in a subquery/CTE)

Why can ORDER BY use the very same alias? Because ORDER BY (⑥) runs after SELECT (⑤), by which time revenue has been born:

SELECT price * qty AS revenue
FROM orders
ORDER BY revenue DESC;   -- ✅ the alias exists by the time we sort

Why can’t a window function go in WHERE? Same reason — window functions are computed in the SELECT (⑤) stage, and at WHERE (②) they simply haven’t happened yet:

-- ❌ wants "only the top-ranked per category", but rn isn't computed at WHERE time
SELECT *, ROW_NUMBER() OVER (PARTITION BY cat ORDER BY sales DESC) AS rn
FROM products
WHERE rn = 1;

-- ✅ the standard fix: wrap in a subquery and filter in the outer layer
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY cat ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn = 1;

That age-old question, “why does a window function always need a subquery around it”, is answered by this diagram: it’s computed later than WHERE. (Window functions are the subject of post 5 in the series; for now just know where they sit.)

WHERE filters rows, HAVING filters groups

The order also settles the difference between WHERE and HAVING once and for all: one runs before grouping, the other after, so they filter completely different things:

Drop it into an example and it’s obvious:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE amount > 0             -- ② first remove invalid rows (refunds, zero amounts)
GROUP BY customer_id
HAVING SUM(amount) > 1000;   -- ④ after grouping, keep only the "groups" over a thousand

There’s a performance principle hiding here too: rows you can cut with WHERE shouldn’t be left to HAVING. WHERE shrinks the data before grouping, so there’s less to group and less to compute afterwards; handing it to HAVING means a pile of rows destined to be discarded go through grouping first — wasted work. This “filter as early as possible” instinct is the same thing as “filter to shrink the data before you shuffle” in the Spark post.

Reflections

Remember the running order, and half of SQL’s puzzles solve themselves

When I started writing SQL, I treated “aliases can’t be used in WHERE”, “window functions need a subquery” and “what’s the difference between WHERE and HAVING anyway” as three separate rules to memorise. Only later did I realise they’re three faces of the same thing — the order you write isn’t the order it runs. Once that processing-order diagram is carved into your head, these stop being rules to memorise and become “of course it works that way” deductions. Now when someone asks me “why does this query error”, my first reflex is to run FROM→WHERE→GROUP BY→… in my head, and eight times out of ten I can see on the spot which stage referenced something that hadn’t been born yet. Reducing rules to mechanism is the first step in how I learn anything, and SQL is no exception.

This is really the other face of “declarative”

SQL’s “written order ≠ execution order” is, at heart, the same thing as the declarative idea I keep coming back to in K8s and Spark DataFrames: you describe “what”, the engine decides “how, and in what order”. The upside is you don’t manage execution details and the engine can optimise for you; the price is — you can’t assume it runs in the literal order you wrote. That’s why “understanding the execution model” matters so much for SQL: without it, you write queries that “look right but are wrong” or “run, but are inexplicably slow”; with it, you can predict, and therefore tune. All declarative tools are like this: the flip side of convenience is the effort of understanding the engine that makes decisions on your behalf.

”Shrink the data first” is a shared instinct across tools

WHERE before HAVING, cut rows as early as you can — this small SQL habit holds when scaled up to any data system. Spark wants you to filter before shuffling and push filters down to the scan; here it’s WHERE before GROUP BY. Behind both is the same sentence: the later data is processed, the more each row costs (it has to survive every earlier stage first). So whenever I look at a piece of data processing — SQL, Spark, a whole pipeline — I ask the same first question: can the step that filters out the most data be moved earlier? It’s the most reliably rewarding kind of optimisation — no cleverness required, just the right order.