In what order does SQL actually execute the clauses of a SELECT query?
The logical order is FROM and JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. It differs from the written order, which is why a SELECT alias works in ORDER BY but not in WHERE.
Explain it simply
Think of a canteen kitchen. First the cook gathers the ingredients (FROM), throws out the bad ones (WHERE), groups what is left into plates (GROUP BY), removes small plates (HAVING), then writes the menu names on them (SELECT), and finally arranges them in order (ORDER BY) and serves only a few (LIMIT).
You write SELECT first, but the database reads it nearly last.
Example
SELECT dept, COUNT(*) AS total
FROM employees
WHERE salary > 30000
GROUP BY dept
HAVING COUNT(*) > 2
ORDER BY total DESC
LIMIT 3;Logical order here:
FROM employeesloads the rows.WHEREkeeps salaries above 30000.GROUP BYmakes one group per department.HAVINGdrops groups with 2 or fewer rows.SELECTcomputesdeptand the aliastotal.ORDER BY totalcan use the alias because SELECT already ran.LIMITcuts the result to 3 rows.
What to say in the interview
"A query is written SELECT first but evaluated FROM first: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. That is why WHERE cannot see a SELECT alias or an aggregate, while ORDER BY can. The optimizer may physically reorder work, but the result must match this logical order."
Common mistakes
- Using a SELECT alias in WHERE. Repeat the expression or wrap the query in a subquery.
- Filtering on
COUNT(*)in WHERE instead of HAVING.