Order of Execution

---

In SQL, the order in which you write a query is different from the order in which the database executes it. Understanding this "Logical Query Processing" order is the secret to debugging complex queries and understanding why certain aliases or filters don't work.

SQL Execution Order Simulator - Interactive Learning ToolClaude

---

The Standard Order of Execution

Even though a query starts with SELECT, the database actually starts with the FROM clause to identify where the data is coming from.

Step	Clause	What happens
1	FROM	The database identifies the tables and handles JOINs.
2	WHERE	Individual rows are filtered out based on conditions.
3	GROUP BY	The remaining rows are grouped into buckets (aggregations).
4	HAVING	The grouped buckets are filtered (e.g., SUM(sales) > 100).
5	SELECT	The specific columns are chosen and expressions/aliases are calculated.
6	DISTINCT	Duplicate rows are removed from the final set.
7	ORDER BY	The final results are sorted.
8	LIMIT / TOP	The number of rows returned is restricted.

---

Why this matters

This order explains common SQL "gotchas" that frustrate beginners:

Case A: Using Aliases in the WHERE clause

You cannot use an alias created in the SELECT clause inside the WHERE clause.

• Reason: The WHERE clause (Step 2) happens before the SELECT clause (Step 5) has even calculated the alias.

-- This will fail in most databases
SELECT fare_amount * 0.2 AS tip_calc
FROM taxi_trips
WHERE tip_calc > 5; -- Error: tip_calc unknown

Case B: Using Aliases in the ORDER BY clause

You can use an alias in the ORDER BY clause.

• Reason: The ORDER BY (Step 7) happens after the SELECT (Step 5) has finished its work.

-- This works perfectly
SELECT fare_amount * 0.2 AS tip_calc
FROM taxi_trips
ORDER BY tip_calc DESC;

---

Order in UNION Operations

When you use UNION or UNION ALL to stack two queries, the execution changes slightly. Each individual query follows the steps above, and then the results are combined.

1. Query A executes (Steps 1–5).

2. Query B executes (Steps 1–5).

3. The Result Sets are merged.

4. Global ORDER BY (if present) is applied to the final merged list.

---

Order in CASE WHEN Statements

Inside a single column calculation, the CASE statement executes top-to-bottom.

SELECT 
    CASE 
        WHEN score > 90 THEN 'A'
        WHEN score > 70 THEN 'C' -- This is only checked if score is NOT > 90
        ELSE 'F'
    END AS grade
FROM students;

If a score is 95, it hits the first WHEN, returns 'A', and exits the case logic immediately. It never checks the second condition.

---

Summary Table for Debugging

If you want to filter...	Use this clause	Execution Timing
Raw Rows	WHERE	Very early (Step 2)
Calculated Groups	HAVING	Middle (Step 4)
Final Results	LIMIT	Last (Step 8)

---