SQL Execution Order Internals: Why WHERE Fails on Aliases but ORDER BY Succeeds

Ever wondered why this query fails in SQL?

SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE emp_count > 5 -- ❌ Error: Invalid column name 'emp_count'
GROUP BY department_id;

The 6-Stage Execution Engine:

1. FROM & JOIN — Load source tables & evaluate join conditions
2. WHERE — Filter raw rows before grouping
3. GROUP BY — Aggregate rows into buckets
4. HAVING — Filter aggregated buckets
5. SELECT — Compute expressions & column aliases
6. ORDER BY — Sort final output

---

Why It Fails:

Because WHERE executes at Stage 2, the emp_count alias created at Stage 5 (SELECT) does not exist in memory yet!

However, ORDER BY runs at Stage 6 (after SELECT), which is why ORDER BY emp_count DESC works seamlessly.

---

How to Fix:

Use HAVING for aggregated filtering:

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

---

💡 What's your favorite SQL execution order quirk? Drop your thoughts below!

💼 Let's connect: linkedin.com/in/arpitmbangre

Comments

[Rahman]

My favorite is that you can’t use a SELECT alias in WHERE, but you can often use it in ORDER BY. SQL’s logical execution order feels confusing at first, but it becomes much easier once you understand when each clause runs.

[Arpit Bangre]

Totally agree, Rahman! Once that step-by-step flow clicks in your head, SQL errors make so much more sense.