Query Execution Order

You write a query in one order. The database runs it in another. Almost every confusing SQL error comes from that gap.

The real order

Learn this table and three separate mysteries stop being mysteries.

Mystery 1 — an alias does not work in WHERE

SQL▶ Run
SELECT first_name, salary / 12 AS monthly
FROM employees
WHERE salary / 12 > 6000
ORDER BY monthly DESC;

Writing WHERE monthly > 6000 fails: at step 2 the alias from step 5 does not exist yet. Repeat the expression, or wrap the query in a subquery.

But ORDER BY monthly is fine — step 7 runs after step 5. Same alias, two different answers, entirely because of ordering.

Mystery 2 — WHERE cannot use an aggregate

WHERE COUNT(*) > 1 is an error. WHERE runs at step 2, before any grouping has happened, so there is nothing to count yet. Filtering groups is HAVING, step 4:

SQL▶ Run
SELECT dept_id, COUNT(*) AS staff
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING COUNT(*) > 2
ORDER BY staff DESC;

Read it in execution order: take employees (1), drop the ones with no department (2), group by department (3), keep groups larger than two (4), then compute the columns (5) and sort (7).

Mystery 3 — WHERE and HAVING are not interchangeable

They filter different things at different times. WHERE filters rows before grouping; HAVING filters groups after. When both would work, prefer WHERE: discarding rows early means fewer to group.

SQL▶ Run
SELECT dept_id, COUNT(*) AS staff, ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE active = 1
GROUP BY dept_id
ORDER BY dept_id;

Filtering active = 1 in WHERE removes the inactive employee before any averaging. Doing it in HAVING would be wrong here — by then the average has already been computed with that person included.

The one exception worth knowing

SELECT appears fifth, but the database's planner does not run these steps literally in sequence. It rewrites the query freely, as long as the result is identical to what this order would produce. The table describes the semantics you can rely on, not the machine instructions.

Common mistakes

• Alias in WHERE — repeat the expression instead.
• Aggregate in WHERE — use HAVING.
• Row filter in HAVING — correct but slower, and sometimes wrong, as above.

Interview question

Why can ORDER BY use a SELECT alias when WHERE cannot?

ORDER BY runs after SELECT; WHERE runs before it. Anyone who has the execution order answers this in a sentence.

Check yourself

1. List the eight steps in order.
2. Why is WHERE COUNT(*) > 1 an error?
3. Give a case where moving a filter from WHERE to HAVING changes the answer.

Module complete

You can now select, rename, compute, deduplicate, sort and limit — and you know the order the database applies them in. Next comes filtering in earnest: WHERE, the operators, and the three-valued logic that NULL introduces.