Part 1 — The relational foundation › Chapter 06

How a SELECT is really evaluated

FROM before WHERE before GROUP BY before SELECT. Nearly every beginner error is this one order.

From the advert

“Ottima conoscenza del linguaggio SQL” — “ottima” starts with knowing that the clauses do not run in the order they are written. Almost every beginner error is this one fact.

Written order is not evaluation order

🧒 Simple

You write a shopping list starting with “I want apples”, but you do it in a different order: first you go to the shop, then you walk to the fruit aisle, then you look at what is there, then you pick apples, then you queue. SQL is the same. You say SELECT first, but the database does it almost last — it has to know which rows it is looking at before it can decide what to show you about them.

The two clauses you write first are evaluated fifth and first.

Four things this explains

🎓 1 · You cannot use a SELECT alias in WHERE

WHERE runs at step 2; the alias is created at step 5. It does not exist yet, and the error message — “invalid column name” — sounds like a typo rather than an ordering problem. Repeat the expression, or wrap the query in a derived table or CTE.

The same question three times: the version that cannot work, the version that does, and the version that scales to three conditions.

-- FAILS: `spend` does not exist yet when WHERE runs. WHERE is step 2, SELECT is step 5.
SELECT customer_id, SUM(total_cents) AS spend   -- the alias is created HERE…
FROM orders GROUP BY customer_id                -- …after the rows are read and grouped…
WHERE spend > 100000;                           -- …but WHERE ran before either of them

-- WORKS: HAVING runs AFTER grouping, so the aggregate is available to it.
SELECT customer_id, SUM(total_cents) AS spend   -- same columns, same aggregate
FROM orders GROUP BY customer_id                -- same grouping
HAVING SUM(total_cents) > 100000;               -- filters GROUPS, not rows: repeat the expression

-- ALSO WORKS, and reads better when there are three of these:
SELECT * FROM (                                 -- the outer query sees a finished result
  SELECT customer_id, SUM(total_cents) AS spend -- the inner query does the work…
  FROM orders GROUP BY customer_id              -- …and its aliases DO exist outside it
) t WHERE t.spend > 100000;                     -- so a plain WHERE on the alias is legal here

The rule underneath all three

Names become visible in the step that creates them. WHERE (step 2) cannot see a SELECT alias (step 5); ORDER BY (step 6) can. The error message — “invalid column name” — reads like a typo, which is why this costs beginners an afternoon rather than a minute.
A derived table ( … ) t is a completed query, so its output columns are real columns to the query outside it. That is the general escape hatch, and a CTE (chapter 12) is the same trick with a name at the top.
t is an alias, and it is mandatory in most engines: a derived table with no name cannot be referred to, and PostgreSQL and MySQL reject it outright.

🎓 2 · ORDER BY can use an alias

Because it runs at step 6, after SELECT. So ORDER BY spend DESC is legal in exactly the query where WHERE spend > … is not. That asymmetry looks arbitrary until you know the order, and then it is obvious — which is precisely why interviewers like the question.

🎓 3 · WHERE and HAVING are not interchangeable

WHERE filters rows before grouping; HAVING filters groups after. Where a condition could go in either, put it in WHERE: it can use an index, and it removes rows the aggregate would otherwise have had to read. Filtering in HAVING what you could have filtered in WHERE is correct and slower — and saying why it is slower is the answer that lands.

🎓 4 · A LEFT JOIN condition in WHERE is an INNER JOIN

The join happens at step 1, producing NULLs for unmatched rows. WHERE then runs at step 2 — and a predicate on the right-hand table is UNKNOWN for those NULLs, so they are dropped. The outer join has been silently undone. If the condition is part of the matching rule it belongs in ON; if it is a filter on the result it belongs in WHERE. See chapter 08.

Where a date filter goes decides which question you asked — and neither version is an error.

-- Every customer, with their 2026 orders. Zero-order customers INCLUDED.
SELECT c.name, COUNT(o.id) AS orders_2026    -- COUNT(o.id) counts non-NULL ids: 0 for no match
FROM customers c                             -- the preserved side: every customer survives
LEFT JOIN orders o                           -- unmatched customers get NULL-padded order columns
       ON o.customer_id = c.id               -- the pairing rule
      AND o.placed_at >= DATE '2026-01-01'   -- part of the MATCH: stays in ON
GROUP BY c.name;                             -- one row per customer name

-- Move that line to WHERE and the customers with no 2026 orders vanish:
-- the NULL-padded rows fail `NULL >= DATE '2026-01-01'` (UNKNOWN), so they are
-- discarded and the outer join has silently become an inner one. No error.

Why the two versions differ

ON is evaluated while the join runs, before the padding is added. WHERE is evaluated on the finished join, when the padded rows already exist and are full of NULLs.
COUNT(o.id) versus COUNT(*) is the second half of the trick. COUNT(*) counts the padded row too and would report 1 for a customer with no orders; COUNT(o.id) skips the NULL and reports 0.
The rule to carry away: if it is part of what counts as a match, it belongs in ON; if it is a filter on the result, it belongs in WHERE.

"Logical" order, not physical order

The clarification that separates good candidates from great ones

This ordering is a semantic definition, not a description of what the engine does. The optimiser will happily push a WHERE predicate down inside a join, use an index to satisfy ORDER BY without sorting, or stop early because of a LIMIT. It may do anything at all provided the answer is the same as if it had followed this order. So the order tells you what your query means; the execution plan tells you what actually happened. Say both halves of that and you have answered the follow-up before it was asked.

⚠ Trap

SELECT DISTINCT is evaluated after SELECT, so it deduplicates the projected rows — including columns you added only to make the output readable. Adding o.id to a SELECT DISTINCT makes every row unique and silently disables the deduplication. When DISTINCT appears as a fix for duplicates, the real bug is almost always a fanning join; see chapter 08.

Evaluation order is FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
Aliases are born in SELECT, so ORDER BY can use them and WHERE cannot.
WHERE filters rows, HAVING filters groups. If it can go in WHERE, put it there.
A predicate on the outer table of a LEFT JOIN placed in WHERE turns it into an inner join, with no error.
This order is what the query means. The optimiser may do anything that produces the same answer.
DISTINCT applies to the projected row. Adding a unique column to the select list disables it.
Reaching for DISTINCT to remove duplicates usually means the join is wrong.

Interview questions this chapter answers

Why can't I use a column alias in the WHERE clause?

Because WHERE is evaluated before SELECT, and the alias is created by SELECT. ORDER BY runs after, which is why the alias works there. Repeat the expression, or wrap the query in a derived table or CTE.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after. Beyond correctness there is a performance consequence: a WHERE predicate can use an index and reduces the rows the aggregate has to process, so a condition that could live in either should live in WHERE.

My LEFT JOIN is returning fewer rows than the left table. Why?

There is a condition on the right-hand table in WHERE rather than ON. Unmatched rows have nulls there, the predicate evaluates to UNKNOWN, and they are filtered out — so the join behaves as an inner join. Move the matching condition into ON.