Part 28: SQL Order of Execution: Aliases in WHERE vs ORDER BY

One of the most confusing errors for SQL beginners is: ERROR: column "total_price" does not exist in WHERE clause.

1. The Real SQL Order of Execution

SQL is not executed from top to bottom as written. The database engine executes clauses in this exact order:

1. FROM / JOIN     (Locates tables and joins rows)
2. WHERE           (Filters rows)
3. GROUP BY        (Aggregates rows)
4. HAVING          (Filters grouped rows)
5. SELECT          (Calculates expressions & assigns aliases)
6. DISTINCT        (Removes duplicate rows)
7. ORDER BY        (Sorts the final rows)
8. LIMIT / OFFSET  (Trims row counts)

Why Aliases Fail in WHERE:

Because WHERE (Step 2) runs BEFORE SELECT (Step 5), the alias has not been created yet! ORDER BY (Step 7) runs AFTER SELECT, so aliases work in ORDER BY.

-- ❌ FAILS:
SELECT price * quantity AS total FROM orders WHERE total > 100;

--  WORKS:
SELECT price * quantity AS total FROM orders WHERE (price * quantity) > 100;

Key Takeaways

• WHERE executes before SELECT.
• You cannot reference aliases defined in SELECT inside a WHERE clause.

In Part 29, we will explore Selecting Random Records and Performance Traps!