The order a query runs in

Lesson 10 of 21 · Part 2 · Summarizing · about 15 minutes

Put the clauses of the last few lessons together and a query can get long. Here is one that uses them all:

main.sql

1SELECT category,2    COUNT(*) AS product_count,3    ROUND(AVG(price), 2) AS avg_price4FROM products5WHERE stock > 06GROUP BY category7HAVING SUM(stock) >= 508ORDER BY avg_price DESC9LIMIT 2;

Output

category	product_count	avg_price
Electronics	3	27.50
Kitchen	2	10.95

2 rows

It says: group the products in stock by category, keep only the categories with at least 50 in stock, sort by average price, highest first, and take the first 2.

When we write it, SELECT comes first. But when the database runs it, SELECT comes almost last.

The real order

The database works through these steps in order, each one starting from what the step before it left:

FROM: take the products table, 10 rows.
WHERE: look at each row; the desk lamp has 0 in stock, so drop it, leaving 9 rows.
GROUP BY: split them into 4 groups by category.
HAVING: Bags has only 40 in stock in all, so drop the whole group, leaving 3.
SELECT: work out category, product_count and avg_price for each group; only now do the aliases exist.
ORDER BY: sort by avg_price, highest first.
LIMIT: keep only the first 2 rows.

Press Step on the query above and walk through these 7 steps one at a time, watching what the table looks like after each.

A clause you didn't write is skipped. The shortest query has just two steps, FROM and SELECT.

No aliases in WHERE

Keep this order in mind and many errors start to make sense. Work out each product's stock value (price × stock), and keep only those over 500:

main.sql

1SELECT name, price * stock AS stock_value2FROM products3WHERE stock_value > 500;

Output

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

ERROR 1054: there's no column called stock_value. WHERE is step 2 and SELECT, which gives the alias, is step 5: when WHERE runs, the name doesn't exist yet.

Write the calculation out again in WHERE:

main.sql

1SELECT name, price * stock AS stock_value2FROM products3WHERE price * stock > 5004ORDER BY stock_value DESC;

Output

name	stock_value
USB cable	1700.00
Backpack	598.50
Coffee mug	594.00
Headphones	590.00
Notebook	540.00
Phone case	525.00

6 rows

ORDER BY can use them

The ORDER BY stock_value DESC above gave no error: ORDER BY is step 6, after SELECT, so the alias already exists. The same goes for grouped queries:

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4ORDER BY order_count DESC, customer_id5LIMIT 3;

Output

customer_id	order_count
1	3
2	2
4	2

3 rows

Guess first

What happens when you run it?

1SELECT name, price AS p2FROM products3WHERE p < 104ORDER BY p;

WHERE runs before SELECT, when there is no name p yet. ORDER BY p would have been fine, but the query stops at WHERE.

When aggregates are worked out

The order also explains lesson 9's ERROR 1111: aggregates can only be worked out after the groups are made in step 3, so WHERE, at step 2, can't use them; HAVING at step 4 and the later SELECT and ORDER BY can. So to sort by a group's result, you can write the aggregate right there:

main.sql

1SELECT category, SUM(stock) AS total_stock2FROM products3GROUP BY category4ORDER BY SUM(stock) DESC;

Output

category	total_stock
Electronics	245
Stationery	200
Kitchen	100
Bags	40

4 rows

Tip: MySQL is lenient and also accepts SELECT aliases in GROUP BY and HAVING. Many other databases don't. For queries that work everywhere, stick to the standard rule: use aliases only in ORDER BY.

LIMIT comes last

LIMIT is the very last step: it cuts only after the rows are sorted. That's why "the 3 most expensive" is written like this: first sort by price, highest first, then take the first 3 rows.

main.sql

1SELECT name, price2FROM products3ORDER BY price DESC4LIMIT 3;

Output

name	price
Headphones	59.00
Backpack	39.90
Desk lamp	25.00

3 rows

If it cut first and sorted afterwards, you'd get whichever 3 rows happened to come first in the table, not the 3 most expensive.

The order you write clauses in

The order things run in is one thing; the order you write them in is another, and it is fixed. You can't swap clauses around:

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …

Put one in the wrong place, say WHERE after GROUP BY, and you get ERROR 1064: a syntax error. Exercise 3 below is just that.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Fix it: the alias

This should get the order lines whose amount (quantity * unit_price) is at least 30, but it gives ERROR 1054. Fix it; the columns are order_id and amount, by amount from largest to smallest, then by order_id.

It should print

order_id	amount
3	59.00
8	59.00
2	39.90
7	39.90
12	39.60

5 rows

exercise1.sql

1SELECT order_id, quantity * unit_price AS amount2FROM order_items3WHERE amount >= 304ORDER BY amount DESC, order_id;

Output

Press Run or Ctrl+Enter

Exercise 2

Best-selling cheap items

From order_items, looking only at lines with a unit price under 20, group by product and work out how many of each were sold; keep those that sold at least 3, sort by that number, most first (ties by product_id), and take the first 3. The columns are product_id and sold.

It should print

product_id	sold
1	10
8	6
2	3

3 rows

exercise2.sql

1SELECT product_id, quantity, unit_price FROM order_items;

Output

Press Run or Ctrl+Enter

Exercise 3

Fix it: the order

This should count the products over 10 in each category, but it gives ERROR 1064. Fix it; the columns are category and COUNT(*), sorted by category.

It should print

category	COUNT(*)
Bags	2
Electronics	3
Kitchen	1

3 rows

exercise3.sql

1SELECT category, COUNT(*)2FROM products3GROUP BY category4WHERE price > 105ORDER BY category;

Output

Press Run or Ctrl+Enter

What you learned

The order a query runs in: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
The written order is fixed: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT; a clause in the wrong place is ERROR 1064.
A SELECT alias doesn't exist yet in WHERE (ERROR 1054), but ORDER BY can use it.
Aggregates exist only after grouping, so not in WHERE, but yes in HAVING, SELECT and ORDER BY.
LIMIT comes last: sort first, then cut.