Why SQL's Written Order ≠ Execution Order: The Hidden Logic Behind Query Processing

SQL Writing Order vs. Execution Order

A typical SQL query is written as

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

, but the database executes it in a different logical sequence:

1. FROM        → locate the employee table, determine data source
2. WHERE       → filter rows, discard rows where age ≤ 25
3. GROUP BY    → group by department
4. HAVING      → filter groups, discard groups where COUNT(*) ≤ 3
5. SELECT      → choose output columns, compute COUNT(*), assign alias cnt
6. ORDER BY    → sort by cnt
7. LIMIT       → take first 10 rows

Because WHERE runs at step 2 while SELECT runs at step 5, the alias cnt does not exist when WHERE is evaluated, causing an "Unknown column" error.

Why HAVING Can Use Aggregate Functions

HAVING

executes at step 4, after GROUP BY has formed groups. The database specially evaluates aggregate functions (e.g., COUNT(*)) during the HAVING phase. However, HAVING cannot reference SELECT aliases in standard SQL; writing HAVING cnt > 3 is invalid in PostgreSQL, though MySQL permits it as an extension.

In contrast, ORDER BY runs at step 6, after SELECT (step 5), so aliases like cnt are available.

WHERE vs. HAVING: Filtering Rows vs. Filtering Groups

WHERE runs before GROUP BY; it filters raw rows. Aggregates cannot be used here because grouping hasn't happened yet.

HAVING runs after GROUP BY; it filters grouped results. Aggregates are allowed because groups are already formed.

Example: exclude interns with WHERE position <> '实习生', then group by department, then keep only departments with AVG(age) > 30 via HAVING.

JOIN and ON in the Execution Sequence

With joins, the logical order becomes:

FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

JOIN

and ON execute after FROM but before WHERE. For an INNER JOIN, placing a condition in ON vs. WHERE yields the same result. For a LEFT JOIN, the difference is critical:

Condition in ON : LEFT JOIN department d ON e.dept_id = d.id AND d.status = 1 — all employees appear; non-matching departments show NULL.

Condition in WHERE :

LEFT JOIN department d ON e.dept_id = d.id WHERE d.status = 1

— employees without a matching department are filtered out because d.status is NULL, effectively turning the LEFT JOIN into an INNER JOIN.

This is a common bug caught in code reviews.

Verifying Execution Order with EXPLAIN

MySQL's EXPLAIN shows the query plan. Key columns: type — access method (full scan vs. index) key — index used rows — estimated rows scanned Extra — most informative; Using temporary indicates a temporary table (often from GROUP BY or DISTINCT); Using filesort means sorting couldn't use an index.

Logical vs. Physical Execution Order

The sequence described above is the logical execution order defined by the SQL standard. The optimizer may reorder operations physically (e.g., push a WHERE filter before a JOIN to reduce rows early) but must guarantee the final result matches the logical order. Understanding the logical order determines which syntax is legal, where aliases are visible, and how WHERE and HAVING differ.