Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › SQL

SELECT processing order

Why can't you use an alias or an aggregate function in WHERE?

You can think of a SELECT statement as being processed not in the order it is written but in the order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Which names you can use depends on where in this order a clause comes.

Checked against real execution · The SQL for the 32 combinations was run as is on MySQL 8.4.11, and the error numbers and messages and every row of the final results were checked against the results. Differences from standard SQL follow the MySQL documentation

Concepts in this lab

Logical processing order of the SELECT statement
The order in which the clauses of a SELECT statement are considered to be processed when you work out its result. It is FROM (including JOIN) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, which differs from the order in which you write the clauses. The only names you can use in a clause are those that were created at a step processed before that clause. So an alias given in SELECT cannot be used in WHERE but can be used in ORDER BY, and an aggregate function can be used from HAVING on, after the groups have been made. This does not mean that the database actually runs the statement in this order. The optimizer changes the execution order as long as the result stays the same.
GROUP BY, WHERE, HAVING
GROUP BY groups rows that have the same value in the specified columns and computes an aggregate (such as a sum or a count) for each group. WHERE filters individual rows before they are grouped, and HAVING filters groups after grouping. So put conditions on aggregate results in HAVING and all other conditions in WHERE.
Column alias (AS)
Another name that you give to a column or an expression in the SELECT list. You write it as SUM(amount) AS total, and it becomes the column name in the result. An alias is created at the SELECT step, so it can be used in ORDER BY, which is processed after SELECT, but not in WHERE, which is processed before it. MySQL also accepts an alias in GROUP BY and HAVING, but this is an extension that standard SQL does not have, so it can cause an error in other databases. To filter on the same value in WHERE, write the expression again instead of the alias.
Aggregate function
A function that takes the values of several rows and returns a single value. COUNT (number of rows), SUM (total), AVG (average), MAX (largest value), and MIN (smallest value) are typical. With GROUP BY, it is computed once for each group. Without GROUP BY, all the rows that satisfy the condition are treated as one group. An aggregate function can be computed only after the groups are made, so it cannot be used in WHERE. You use it in HAVING, SELECT, and ORDER BY. Aggregate functions other than COUNT(*) skip NULL values when they compute.

Try these

Running with the default settings

  1. FROM orders: This is processed first. It takes all 6 rows of the orders table. At this point the only names you can use are the table columns id, member, and amount.
  2. WHERE total >= 300: total is an alias that SELECT gives to SUM(amount), but SELECT is processed after WHERE. At this point the name total does not exist yet, so MySQL rejects the statement with error 1054 (Unknown column 'total' in 'where clause').
  3. A statement that fails returns no rows at all, and the later steps are not processed. MySQL raises this error while it analyzes the statement, before it starts executing it, so in practice it stops before it even reads the table.

Result of this run

Code

CREATE TABLE orders (
  id     INT PRIMARY KEY,
  member VARCHAR(20) NOT NULL,
  amount INT NOT NULL
);

INSERT INTO orders (id, member, amount) VALUES
  (1, 'Alice', 300),
  (2, 'Alice', 150),
  (3, 'Bob', 120),
  (4, 'Bob', 200),
  (5, 'Carol', 250),
  (6, 'Dave', 80);

-- Processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
SELECT member, SUM(amount) AS total
  FROM orders
 WHERE total >= 300
 GROUP BY member;

Assumptions of this model

This is a small example with 6 orders. For all 32 combinations (4 conditions × 2 places to write them × the GROUP BY alias × sorting and limiting), the SQL shown on the screen was run as is on a real MySQL 8.4.11 (default sql_mode, including ONLY_FULL_GROUP_BY), and the error numbers and messages, every row of the final result, and the number of rows counted with the WHERE condition alone were checked against the model and matched. For combinations without ORDER BY, the order in which the rows come out is not guaranteed, so only the contents of the rows were compared, not their order. The seven steps are a logical order: the result is the same as if the statement were processed in this order. Real MySQL raises an error in the analysis phase, before it executes the statement, if it cannot find a name, so for a statement that raises an error, the earlier steps are not executed either. The optimizer may also change the actual execution order, but the result is the same as processing in the logical order. That GROUP BY and HAVING accept the alias from SELECT was checked by running it on MySQL. That this is an extension that standard SQL does not have follows not from running it but from the sections "MySQL Handling of GROUP BY" and "Problems with Column Aliases" of the MySQL 8.4 Reference Manual. What happens in other databases was not run on the PC used to write this lab. DISTINCT and window functions inside the SELECT clause, and the place where JOIN is processed, are not covered in this lab (the results of JOIN and GROUP BY are covered in the "JOIN and GROUP BY" lab).

Previous lab: Normalization · Next lab: JOIN and GROUP BY

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these