The order a SELECT runs in
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A SELECT is written in one order but worked out in another: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Knowing this order explains most "Unknown column" and "Invalid use of group function" errors before you even run the query.
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
The logical order of a SELECT is the order in which its clauses take effect: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Each step works on the result of the step before it.
A SELECT is written in one order but worked out in another. The query below carries numbered comments, so you can read its clauses in the order they really run.
Example 1 · Every clause in one query
SELECT customer_id, -- 5. output columns COUNT(*) AS orders_placedFROM orders -- 1. all 15 ordersWHERE status <> 'CANCELLED' -- 2. 13 rows leftGROUP BY customer_id -- 3. 8 customersHAVING COUNT(*) >= 2 -- 4. 3 customers leftORDER BY orders_placed DESC, customer_id -- 6. sortLIMIT 2; -- 7. keep two+-------------+---------------+ | customer_id | orders_placed | +-------------+---------------+ | 1 | 3 | | 2 | 3 | +-------------+---------------+
What this code does
- Read the numbered comments from 1 to 7: that is the order in which the clauses take effect, though you write
SELECTfirst. ORDER BY orders_placedcan use the alias because sorting happens afterSELECThas named the column.LIMIT 2acts last, on the 3 groups left afterHAVING. Customers 1 and 2 tie on 3 orders;customer_idbreaks the tie so the result is fixed.
The order is a model of what the result means, not a record of what the server does. MySQL's optimizer, the part that plans how to run a query, may read an index or skip work, but the answer is always the same as if the clauses ran in this order.
LIMIT runs last. ORDER BY ... LIMIT 3 on a grouped query gives the top 3 groups, not the groups made from the first 3 rows.Why an alias cannot be used in WHERE
An alias is the name you give an output column with AS, such as seats * price_per_seat AS amount. It is created in step 5, SELECT, so earlier clauses such as WHERE cannot see it.
WHERE amount > 500 fails because amount does not exist yet when WHERE runs. The first example shows the error; the second repeats the expression in WHERE and works.
Example 2 · Error 1054: alias in WHERE
DROP TABLE IF EXISTS cinema_tickets;CREATE TABLE cinema_tickets ( ticket_id INT PRIMARY KEY, movie VARCHAR(30) NOT NULL, show_date DATE NOT NULL, seats INT NOT NULL, price_per_seat DECIMAL(6,2) NOT NULL);
INSERT INTO cinema_tickets VALUES (1, 'Monsoon Express', '2025-04-05', 2, 180.00), (2, 'Monsoon Express', '2025-04-05', 4, 180.00), (3, 'The Last Over', '2025-04-05', 3, 250.00), (4, 'Hostel Days', '2025-04-06', 1, 150.00), (5, 'The Last Over', '2025-04-06', 5, 250.00), (6, 'Monsoon Express', '2025-04-06', 2, 220.00), (7, 'Hostel Days', '2025-04-06', 6, 150.00), (8, 'The Last Over', '2025-04-07', 2, 250.00);
SELECT ticket_id, movie, seats * price_per_seat AS amountFROM cinema_ticketsWHERE amount > 500;ERROR 1054 (42S22): Unknown column 'amount' in 'where clause'
What this code does
- The table is created and filled, then the
SELECTfails.WHEREruns beforeSELECT, so the nameamountdoes not exist yet. - The message says where the name was not found:
'where clause'. That word tells you which step was too early.
Example 3 · Fix: repeat the expression in WHERE
DROP TABLE IF EXISTS cinema_tickets;CREATE TABLE cinema_tickets ( ticket_id INT PRIMARY KEY, movie VARCHAR(30) NOT NULL, show_date DATE NOT NULL, seats INT NOT NULL, price_per_seat DECIMAL(6,2) NOT NULL);
INSERT INTO cinema_tickets VALUES (1, 'Monsoon Express', '2025-04-05', 2, 180.00), (2, 'Monsoon Express', '2025-04-05', 4, 180.00), (3, 'The Last Over', '2025-04-05', 3, 250.00), (4, 'Hostel Days', '2025-04-06', 1, 150.00), (5, 'The Last Over', '2025-04-06', 5, 250.00), (6, 'Monsoon Express', '2025-04-06', 2, 220.00), (7, 'Hostel Days', '2025-04-06', 6, 150.00), (8, 'The Last Over', '2025-04-07', 2, 250.00);
SELECT ticket_id, movie, seats * price_per_seat AS amountFROM cinema_ticketsWHERE seats * price_per_seat > 500ORDER BY amount DESC, ticket_id;+-----------+-----------------+---------+ | ticket_id | movie | amount | +-----------+-----------------+---------+ | 5 | The Last Over | 1250.00 | | 7 | Hostel Days | 900.00 | | 3 | The Last Over | 750.00 | | 2 | Monsoon Express | 720.00 | +-----------+-----------------+---------+
What this code does
WHEREuses the full expression, which only needs the row's own columns, so it works in step 2.ORDER BYuses the aliasamount, which is fine because sorting is step 6.ticket_idis a tie-breaker, so two tickets with the same amount would always come out in the same order.
WHERE is step 2 but the alias is born in step 5, so MySQL answers error 1054, "Unknown column 'amount' in 'where clause'". The same reason explains error 1111 from the HAVING lesson: an aggregate needs groups, and groups are made in step 3. A later module shows how a subquery or a WITH step can name a value once.
Where MySQL allows aliases and where it does not
ORDER BY can always use an alias, because it runs after SELECT. MySQL also accepts aliases in GROUP BY and HAVING. The first example uses aliases in all three; the second shows one place where an alias fails.
Example 4 · Aliases in GROUP BY, HAVING and ORDER BY
DROP TABLE IF EXISTS cinema_tickets;CREATE TABLE cinema_tickets ( ticket_id INT PRIMARY KEY, movie VARCHAR(30) NOT NULL, show_date DATE NOT NULL, seats INT NOT NULL, price_per_seat DECIMAL(6,2) NOT NULL);
INSERT INTO cinema_tickets VALUES (1, 'Monsoon Express', '2025-04-05', 2, 180.00), (2, 'Monsoon Express', '2025-04-05', 4, 180.00), (3, 'The Last Over', '2025-04-05', 3, 250.00), (4, 'Hostel Days', '2025-04-06', 1, 150.00), (5, 'The Last Over', '2025-04-06', 5, 250.00), (6, 'Monsoon Express', '2025-04-06', 2, 220.00), (7, 'Hostel Days', '2025-04-06', 6, 150.00), (8, 'The Last Over', '2025-04-07', 2, 250.00);
SELECT movie AS film, SUM(seats) AS seats_sold, SUM(seats * price_per_seat) AS revenueFROM cinema_ticketsGROUP BY filmHAVING revenue > 1100ORDER BY revenue DESC;+-----------------+------------+---------+ | film | seats_sold | revenue | +-----------------+------------+---------+ | The Last Over | 10 | 2500.00 | | Monsoon Express | 8 | 1520.00 | +-----------------+------------+---------+
What this code does
- MySQL finds
filmandrevenuein theSELECTlist and uses them inGROUP BYandHAVING. This is a MySQL extension. - The portable form repeats the expressions:
GROUP BY movie HAVING SUM(seats * price_per_seat) > 1100. Hostel Days made Rs 1,050, soHAVINGdrops it.
Example 5 · Error 1054: alias in the same SELECT list
DROP TABLE IF EXISTS cinema_tickets;CREATE TABLE cinema_tickets ( ticket_id INT PRIMARY KEY, movie VARCHAR(30) NOT NULL, show_date DATE NOT NULL, seats INT NOT NULL, price_per_seat DECIMAL(6,2) NOT NULL);
INSERT INTO cinema_tickets VALUES (1, 'Monsoon Express', '2025-04-05', 2, 180.00), (2, 'Monsoon Express', '2025-04-05', 4, 180.00), (3, 'The Last Over', '2025-04-05', 3, 250.00), (4, 'Hostel Days', '2025-04-06', 1, 150.00), (5, 'The Last Over', '2025-04-06', 5, 250.00), (6, 'Monsoon Express', '2025-04-06', 2, 220.00), (7, 'Hostel Days', '2025-04-06', 6, 150.00), (8, 'The Last Over', '2025-04-07', 2, 250.00);
SELECT ticket_id, seats * price_per_seat AS amount, amount * 0.18 AS gstFROM cinema_tickets;ERROR 1054 (42S22): Unknown column 'amount' in 'field list'
What this code does
- All output columns of one
SELECTare worked out together, sogstcannot see the aliasamountnext to it. This time the message says'field list', theSELECTlist. - The fix is again to repeat the expression:
seats * price_per_seat * 0.18 AS gst.
Other databases are stricter: SQL Server refuses aliases in both GROUP BY and HAVING, and PostgreSQL refuses them in HAVING. Only ORDER BY accepts an alias everywhere, so the portable habit is to repeat the expression in every other clause.
Fix the error
Fix the alias error
Read the error, then pick the line that fixes it.
Round 1 of 2
Pick the line that replaces line 1 so the query works.
Error
ERROR 1054 (42S22): Unknown column 'cost' in 'field list'
HAVING (HAVING SUM(amount) > 1000) and use aliases only in ORDER BY.Debugging a query step by step
When a report is wrong, run it in its logical order: first only FROM and WHERE, then add one clause at a time. The example builds a department report in three steps.
Example 6 · Building a report one step at a time
-- Goal: the top 2 departments by average salary, counting only people-- hired since 2019, and only departments with at least 2 such people.
-- Step 1: FROM + WHERE. Are these the right rows?SELECT dept_id, name, salaryFROM employeesWHERE hire_date >= '2019-01-01'ORDER BY dept_id, name;
-- Step 2: + GROUP BY. Do the counts match step 1?SELECT dept_id, COUNT(*) AS people, ROUND(AVG(salary), 2) AS avg_salaryFROM employeesWHERE hire_date >= '2019-01-01'GROUP BY dept_idORDER BY dept_id;
-- Step 3: + HAVING, ORDER BY, LIMITSELECT dept_id, COUNT(*) AS people, ROUND(AVG(salary), 2) AS avg_salaryFROM employeesWHERE hire_date >= '2019-01-01'GROUP BY dept_idHAVING COUNT(*) >= 2ORDER BY avg_salary DESCLIMIT 2;+---------+----------------+-----------+ | dept_id | name | salary | +---------+----------------+-----------+ | NULL | Tanvi Kulkarni | 70000.00 | | 1 | Karthik Raj | 120000.00 | | 1 | Pooja Gupta | 120000.00 | | 1 | Suresh Babu | 95000.00 | | 2 | Amit Shah | 85000.00 | | 2 | Farah Ali | 85000.00 | | 3 | Rohan Das | 60000.00 | | 4 | Manoj Pillai | 75000.00 | +---------+----------------+-----------+ +---------+--------+------------+ | dept_id | people | avg_salary | +---------+--------+------------+ | NULL | 1 | 70000.00 | | 1 | 3 | 111666.67 | | 2 | 2 | 85000.00 | | 3 | 1 | 60000.00 | | 4 | 1 | 75000.00 | +---------+--------+------------+ +---------+--------+------------+ | dept_id | people | avg_salary | +---------+--------+------------+ | 1 | 3 | 111666.67 | | 2 | 2 | 85000.00 | +---------+--------+------------+
What this code does
- Step 1 shows the 8 rows that pass
WHERE. You can check each by eye: nobody hired before 2019 is there. - Step 2 turns them into 5 groups, and the counts (3, 2, 1, 1, plus 1 in the
NULLgroup) add up to 8, so no row was lost. - Step 3 keeps the 2 groups with at least 2 people and sorts them. If the final answer looked wrong, you would know exactly which step to look at.
The mistake almost always shows up at one step: rows you needed were removed by WHERE, the groups are too fine or too coarse, HAVING threw away a group, or LIMIT cut a tie in half. A department with 5 people should show 5 after GROUP BY; if it shows 3, look at WHERE.
Order of execution
Debug in logical order
Arrange the debugging steps for a wrong report.
Next step
Check your understanding
12 quick questions