Why SQL Writing Order Differs from Execution Order: Explained

Introduction

The article opens with a simple SQL example that fails:

SELECT COUNT(*) AS cnt FROM employee WHERE cnt > 3;
-- Error: Unknown column 'cnt'

The alias cnt is defined in the SELECT clause, yet the WHERE clause claims it does not exist. This sets up the core question: why does the written order of SQL clauses not match the order in which the database actually executes them?

Writing Order vs. Logical Execution Order

A typical query is written in this order:

SELECT department, COUNT(*) AS cnt
FROM employee
WHERE age > 25
GROUP BY department
HAVING COUNT(*) > 3
ORDER BY cnt DESC
LIMIT 10;

However, the logical execution order defined by the SQL standard is:

FROM – locate the employee table, determine the data source.

WHERE – filter rows, discard those with age <= 25.

GROUP BY – group the remaining rows by department.

HAVING – filter groups, discard those with COUNT(*) <= 3.

SELECT – choose output columns, compute COUNT(*), assign alias cnt.

ORDER BY – sort by cnt.

LIMIT – take the first 10 rows.

Because WHERE executes at step 2 and SELECT at step 5, the alias cnt does not yet exist when WHERE runs, causing the error.

Why HAVING Can Use Aggregate Functions

HAVING also runs before SELECT (step 4 vs. step 5), yet it can contain aggregates like COUNT(*) > 3. The database treats aggregates in HAVING specially, allowing them to be computed after grouping. However, HAVING cannot reference SELECT aliases in standard SQL; writing HAVING cnt > 3 is invalid in standard SQL and fails in PostgreSQL, though MySQL permits it as an extension. In contrast, ORDER BY runs after SELECT (step 6), so it can safely use the alias cnt.

WHERE vs. HAVING: Filtering Rows vs. Filtering Groups

Both clauses filter data, but at different stages:

WHERE executes before GROUP BY, filtering raw rows. It cannot use aggregates because grouping has not happened yet.

HAVING executes after GROUP BY, filtering grouped results. It can use aggregates because groups are already formed.

Example:

SELECT department, AVG(age) AS avg_age
FROM employee
WHERE position <> '实习生'
GROUP BY department
HAVING AVG(age) > 30;

Execution flow: WHERE removes interns, then rows are grouped by department, then HAVING keeps only groups with average age over 30.

JOIN and ON Placement in the Execution Order

When JOIN is present, the logical order expands to:

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

JOIN and ON run after FROM but before WHERE. Consider:

SELECT e.name, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.id
WHERE e.age > 25;

Process: load employee, join with department on e.dept_id = d.id, then apply WHERE filter age > 25, finally select output columns.

For INNER JOIN, placing a condition in ON or WHERE yields the same result. For 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 removed because d.status is NULL and fails the WHERE filter, effectively converting 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 execution 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 ORDER BY could not use an index and requires an extra sort pass.

Logical Order vs. Physical Order

The order described above is the logical execution order mandated by the SQL standard. The query optimizer may reorder operations physically (e.g., pushing a WHERE filter before a JOIN to reduce row count) but must guarantee the final result matches the logical semantics. Understanding the logical order determines which SQL constructs are legal, which expressions each clause can use, and why certain queries error out.