SELECT Execution Order

The Logical Order in Which a SELECT Executes

You write SELECT first, but logically the server evaluates it sixth: rows come from FROM and its joins, WHERE filters them, groups form and HAVING filters them, window functions run, and only then the select list, DISTINCT, ORDER BY and LIMIT. Each step sees only what earlier steps made.

The logical processing order of a SELECT statement

So a column alias, born in step 6, is invisible to WHERE in step 2:

An alias is invisible to WHERE but visible to HAVING and ORDER BYSQL

SELECT customer_id, COUNT(*) AS n FROM orders WHERE n > 1 GROUP BY customer_id;
SELECT customer_id, COUNT(*) AS n FROM orders WHERE status <> 'cancelled'
GROUP BY customer_id HAVING n > 1 ORDER BY n DESC, customer_id;

Output

ERROR 1054 (42S22): Unknown column 'n' in 'where clause'
+-------------+---+
| customer_id | n |
+-------------+---+
|           1 | 2 |
|           2 | 2 |
|           3 | 2 |
+-------------+---+
3 rows in set (0.000 sec)

Standard SQL allows select-list aliases only in ORDER BY. MySQL 524 also resolves them in GROUP BY and HAVING, which is convenient and not portable. In WHERE, repeat the expression instead.

The order is logical, not physical: the optimizer may skip a sort by reading an index in order, stop at the LIMIT, or reorder joins, as long as the result is the same (Optimizer and EXPLAIN).

MENU

SELECT Execution Order