SQL Order of Execution: How MySQL Evaluates a SELECT Query

You write a query starting with SELECT. MySQL reads it in a different order. Knowing this order explains many errors that look strange at first.

The logical order

Step	Clause	What happens
1	`FROM` / `JOIN`	Find the tables and combine them
2	`WHERE`	Keep only the rows that pass the condition
3	`GROUP BY`	Form groups of rows
4	`HAVING`	Keep only the groups that pass
5	`SELECT`	Calculate the output columns (and aliases)
6	`DISTINCT`	Remove duplicate rows
7	`ORDER BY`	Sort the result
8	`LIMIT`	Keep only the first rows

This is the logical order. The optimizer may run the query in a different physical order, if the answer stays the same.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE stock > 0
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY avg_price DESC
LIMIT 5;

Output

OUTPUT

+------------+----------+-----------+
| category   | products | avg_price |
+------------+----------+-----------+
| bags       |        2 |    724.50 |
| home       |        2 |    435.00 |
| stationery |        3 |     43.50 |
+------------+----------+-----------+
3 rows in set

Follow it step by step:

FROM products: start with all 8 rows.
WHERE stock > 0: the Marker Set (stock 0) is removed. 7 rows remain.
GROUP BY category: the 7 rows form 3 groups: bags, home, stationery.
HAVING COUNT(*) >= 2: all three groups have at least 2 products here.
SELECT: calculate the count and the average for each group, and name them.
ORDER BY avg_price DESC: sort. Here the alias avg_price exists.
LIMIT 5: keep at most 5 rows.

What the order explains

1. Why WHERE cannot use a column alias. The alias is created in step 5, but WHERE runs in step 2.

query.sql

SQL

SELECT price * 2 AS double_price FROM products WHERE double_price > 100;

Output

OUTPUT

ERROR 1054 (42S22): Unknown column 'double_price' in 'where clause'

2. Why WHERE cannot use an aggregate. Groups do not exist until step 3. Use HAVING.

query.sql

SQL

SELECT category FROM products WHERE COUNT(*) > 1 GROUP BY category;

Output

OUTPUT

ERROR 1111 (HY000): Invalid use of group function

3. Why ORDER BY can use an alias. It runs after SELECT.

4. WHERE vs HAVING. WHERE filters rows before grouping, and HAVING filters groups after. Put row filters in WHERE. They make the groups smaller, which is faster.

query.sql

SQL

1
2

SELECT category, COUNT(*) AS products FROM products GROUP BY category HAVING category = 'bags';
SELECT category, COUNT(*) AS products FROM products WHERE category = 'bags' GROUP BY category;

Output

OUTPUT

+----------+----------+
| category | products |
+----------+----------+
| bags     |        2 |
+----------+----------+
1 row in set

+----------+----------+
| category | products |
+----------+----------+
| bags     |        2 |
+----------+----------+
1 row in set

Both give the same answer. The second one filters first and groups less, so it is the better way to write it.

5. Why LIMIT after ORDER BY matters. LIMIT takes the first rows of the sorted result. Without ORDER BY, “the first rows” are not defined.

Window functions and the order

Window functions (ROW_NUMBER(), RANK()) are computed after GROUP BY and HAVING, and before DISTINCT and ORDER BY. That is why you cannot filter on them in WHERE. Wrap the query and filter outside (Window Functions).

Read queries in this order

When you debug a long query, do not read from the top. Read in execution order: What are the tables (FROM, JOIN)? Which rows survive (WHERE)? How are they grouped? What is shown? You will find mistakes faster.

info

Standard SQL vs MySQL

MySQL lets GROUP BY and HAVING use SELECT aliases, which standard SQL does not. It works, but it blurs the order above. For portable and clear SQL, repeat the expression or use a derived table.