Execution Order & Putting It Together

Opening notes

Loading…

You write a query starting with SELECT, but the database does not run it top to bottom. Understanding the real logical execution order explains almost every "why doesn't this work?" moment: why aliases fail in WHERE but work in ORDER BY, why aggregates belong in HAVING, and why TOP needs ORDER BY.

Definition

The logical query processing order is the sequence in which SQL evaluates the clauses of a SELECT statement. Each step works on the output of the step before it: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP.

Written order vs execution order

Written order	Execution order	What happens
1. SELECT (DISTINCT, TOP)	1. FROM	Find the table: `players`
2. FROM	2. WHERE	Keep rows with `runs > 100`
3. WHERE	3. GROUP BY	Group rows by `country`
4. GROUP BY	4. HAVING	Keep groups with `SUM(runs) > 800`
5. HAVING	5. SELECT	Pick columns, calculate `SUM(runs)`, create aliases
6. ORDER BY	6. DISTINCT	Remove duplicate rows
	7. ORDER BY	Sort the final result
	8. TOP	Cut down to N rows

Note: This is the logical order — the rules for what each clause can see. The optimizer may physically do things differently (e.g. use an index), but the result is always as if these steps ran in this order.

Putting it all together

Question: Which countries scored over 800 runs (counting only players with 100+ runs)? Biggest first, top 2.

sql

SELECT TOP 2 country, SUM(runs) AS total_runs
FROM players
WHERE runs > 100
GROUP BY country
HAVING SUM(runs) > 800
ORDER BY total_runs DESC;

country	total_runs
India	1600
England	850

Step by step:

FROM players — all 6 rows.
WHERE runs > 100 — Bumrah (20) is removed → 5 rows.
GROUP BY country — India (Kohli, Rohit, Dhoni), England (Buttler), Australia (Warner).
HAVING SUM(runs) > 800 — India 1600 ✓, England 850 ✓, Australia 700 ✗.
SELECT — country and SUM(runs) AS total_runs.
DISTINCT — not used here.
ORDER BY total_runs DESC — India, then England. The alias works because SELECT already ran.
TOP 2 — keeps 2 rows (both remain).

What the order explains

Puzzle	Explanation from the order
Alias fails in WHERE, GROUP BY, HAVING (SQL Server)	They run before SELECT creates the alias
Alias works in ORDER BY	ORDER BY runs after SELECT
Aggregates not allowed in WHERE	Groups don't exist yet at step 2
WHERE can use columns not in SELECT	WHERE sees the full rows from FROM
TOP without ORDER BY is random	TOP just takes the first N rows of whatever order exists

Bonus: SELECT cheat sheet

Clause	Job	Example
`SELECT *`	all columns	`SELECT * FROM players`
`SELECT col1, col2`	specific columns	`SELECT name, team FROM players`
`AS`	rename a result column	`SUM(runs) AS total_runs`
`WHERE`	filter rows	`WHERE runs > 500`
`GROUP BY`	combine rows into groups	`GROUP BY country`
`HAVING`	filter groups	`HAVING SUM(runs) > 800`
`DISTINCT`	remove duplicates	`SELECT DISTINCT team`
`ORDER BY … DESC`	sort the result	`ORDER BY runs DESC`
`TOP n` / `LIMIT n`	limit the rows	`SELECT TOP 3 …` / `… LIMIT 3`

Common mistakes

❌ Writing clauses in the wrong order: SELECT … FROM … GROUP BY … WHERE … → syntax error. ✅ Written order is always SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY.
❌ HAVING total_runs > 800 in SQL Server. ✅ HAVING SUM(runs) > 800.
❌ Confusing written order with execution order in interviews. ✅ Say: "FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, TOP."

Interview tip

Q: What is the order of execution of a SQL query? A: Logically: FROM (and JOINs) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/LIMIT. That's why a SELECT alias can be used in ORDER BY but not in WHERE.

Execution Order & Putting It Together

Definition

Written order vs execution order

Putting it all together

What the order explains

Bonus: SELECT cheat sheet

Common mistakes

Interview tip

Try it yourself

Execution Order & Putting It Together

Definition

Written order vs execution order

Putting it all together

What the order explains

Bonus: SELECT cheat sheet

Common mistakes

Interview tip

Try it yourself