The order a query really runs in

You write a query as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database works it out in a different order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. That order explains most "why doesn't this work?" errors.

Why it matters

Why can't WHERE use the alias you made in SELECT? Why can ORDER BY use it? Why can't WHERE use COUNT? Without the run order these look like random rules. With it, they are obvious — and interviewers ask about it directly.

The idea

Each step works on the output of the step before. FROM (and JOIN) builds the rows. WHERE removes rows. GROUP BY makes groups. HAVING removes groups. SELECT works out the output columns and their aliases (window functions run here too). DISTINCT removes duplicate rows. ORDER BY sorts. LIMIT cuts.

So: WHERE runs before SELECT, and can't see its aliases. ORDER BY runs after SELECT, so it can. WHERE runs before GROUP BY, so it can't use aggregates — HAVING runs after, so it can.

Making a sandwich. You write the order as "sandwich: cheese and tomato, on brown bread, cut in half". But you actually get the bread first (FROM), pick the fillings (WHERE), assemble it (GROUP BY and SELECT), and cut it last (ORDER BY, LIMIT). You can't cut what you haven't made yet.

A cook really does work in that order. A database only has to behave as if it does. The optimiser may reorder the real work (for example, use an index to sort early), as long as the answer is the same.

Syntax

-- written order        -- run order
SELECT    ...           -- 5
FROM      ...           -- 1
WHERE     ...           -- 2
GROUP BY  ...           -- 3
HAVING    ...           -- 4
ORDER BY  ...           -- 6
LIMIT     ...;          -- 7

How it works

  1. FROM employees: all 8 rows.
  2. WHERE salary > 40000: Gus (38000) is removed, so 7 rows are left.
  3. GROUP BY dept_id: department 1 has 4 rows, department 2 has 2 (Dara, Eli), department 3 has 1 (Fatima).
  4. HAVING COUNT(*) >= 2: department 3 is removed.
  5. SELECT works out dept_id and AVG(salary) AS avg_salary for the 2 groups left.
  6. ORDER BY avg_salary DESC can use the alias, because SELECT has already run.

Key points

Common mistakes

Not true: LIMIT runs before ORDER BY: the database takes a few rows and then sorts them.
In fact: ORDER BY runs first and LIMIT last: the database sorts all the rows, then keeps the first few. That is what makes "top 3" queries work.

In the interview

SQL is written SELECT first, but logically processed as FROM and JOINs, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That explains the common rules: WHERE can't reference SELECT aliases or aggregates because they don't exist yet, HAVING can use aggregates, and ORDER BY can use aliases. To filter on a computed column or a window function, I wrap the query in a CTE and filter outside.

Part of Unit 2: Summarising data: aggregates, GROUP BY and HAVING in the free SQL course.