The order a query really runs in

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  F["1 FROM<br/>and JOIN"] --> W["2 WHERE"] --> G["3 GROUP BY"] --> H["4 HAVING"] --> S["5 SELECT<br/>aliases made here"] --> D["6 DISTINCT"] --> O["7 ORDER BY<br/>can use aliases"] --> L["8 LIMIT"]

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 40000
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

SELECT name, bonus
FROM (
  SELECT name, salary / 10 AS bonus
  FROM employees
) AS with_bonus
WHERE bonus > 7000
ORDER BY bonus DESC;

Must-know points

• Run order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
• WHERE can't use SELECT aliases or aggregates; HAVING can use aggregates; ORDER BY can use aliases.
• Window functions are worked out at the SELECT step, so you can't filter on them in WHERE — wrap the query instead.
• To filter on a calculated column, repeat the expression in WHERE or wrap the query in a subquery or CTE.

Common mistakes

• Using a SELECT alias in WHERE and getting "no such column".
• Expecting LIMIT to run before ORDER BY ("take 3 rows, then sort them").
• Filtering on ROW_NUMBER() in WHERE — it doesn't exist yet at that step.

In other databases

• MySQL and SQLite are lenient and let HAVING (and sometimes WHERE) use SELECT aliases. PostgreSQL and SQL Server follow the strict order. Write it the strict way and it runs everywhere.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 0: Querying

  Harvard, free

• Read

  PostgreSQL docs — SELECT (how a query is processed)

  official documentation, free

• Practice

  SQLBolt — Lesson 12: Order of execution of a Query

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Recite the run order from memory, and use it to explain why WHERE COUNT(*) > 1 fails.

   Show answer

   FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

   WHERE runs second, straight after FROM, while the rows are still single rows. Groups are only made at step 3, so at the time WHERE runs there is no group for COUNT(*) to count. The database refuses: "misuse of aggregate".

   A test on a count belongs in HAVING, which runs after GROUP BY.

   sqlCopy

   SELECT customer_id
   FROM orders
   WHERE COUNT(*) > 1
   GROUP BY customer_id;

   Try it

   Error: misuse of aggregate

2. Writefrom a blank page, then run it

   Write a query for categories whose average product price is above £10, sorted by that average from high to low, using an alias in ORDER BY.

   Show answer

   HAVING repeats AVG(price), because HAVING runs before SELECT, so the alias doesn't exist yet. (SQLite and MySQL let you use the alias there; PostgreSQL and SQL Server don't.) ORDER BY runs after SELECT, so the alias avg_price works there everywhere.

   sqlCopy

   SELECT category, AVG(price) AS avg_price
   FROM products
   GROUP BY category
   HAVING AVG(price) > 10
   ORDER BY avg_price DESC;

   Try it

   3 rows

   category	avg_price
   Bags	30
   Electronics	27.5
   Home	25

3. Predictsay it before you run it

   In the "every clause" example, what would change if WHERE were salary > 50000? Work it out step by step, then run it.

   Show answer

   Department 2 disappears, and only department 1 is left, with the same average of 78,750.

   1. FROM: all 8 employees.
   2. WHERE salary > 50000: Eli (45,000) and Gus (38,000) are removed. 6 left.
   3. GROUP BY dept_id: department 1 has Asha, Ben, Chen and Hana; department 2 has only Dara; department 3 has only Fatima.
   4. HAVING COUNT(*) >= 2: only department 1 has 2 or more. With the old filter (> 40000), Eli stayed, so department 2 had 2 people and was kept.
   5. SELECT: department 1, average (95,000 + 70,000 + 70,000 + 80,000) ÷ 4 = 78,750.

   sqlCopy

   SELECT dept_id, AVG(salary) AS avg_salary
   FROM employees
   WHERE salary > 50000
   GROUP BY dept_id
   HAVING COUNT(*) >= 2
   ORDER BY avg_salary DESC;

   Try it

   1 row

   dept_id	avg_salary
   1	78750

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   You want employees whose salary is above the company average. Why does WHERE salary > AVG(salary) fail, and what would you do instead?

   Show answer

   WHERE runs before any grouping, one row at a time, so it can't use an aggregate. AVG needs all the rows at once, and WHERE only sees one. The database refuses with "misuse of aggregate function AVG()".

   Work the average out first, in its own query, and compare with that: a subquery. The company average is 510,000 ÷ 8 = 63,750. Four people are above it.

   sqlCopy

   SELECT name, salary
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees)
   ORDER BY salary DESC, name;

   Try it

   4 rows

   name	salary
   Asha	95000
   Hana	80000
   Ben	70000
   Chen	70000

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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.

Follow-up questions

Tick each check when you can do it without notes