Subqueries

Lesson 15 of 21 · Part 4 · Stronger queries · about 15 minutes

Some questions take two steps: "Which products cost more than the average?" First you need the average price, then you compare with it. A subquery is a query written inside another query, in parentheses, which turns the two steps into one.

The average first

Doing it in two steps, the first step is:

1SELECT AVG(price) FROM products;

The average is 19.08. The second step copies that number into WHERE: WHERE price > 19.08. It works, but as soon as a price changes, the copied number is wrong.

A subquery in WHERE

Instead of copying the number, put the first query straight into parentheses:

1SELECT name, price2FROM products3WHERE price > (SELECT AVG(price) FROM products)4ORDER BY price DESC;

The query in parentheses runs first and gives one value, 19.08; then the outer WHERE compares every product's price with it. A subquery that gives one value can go anywhere a single value can.

1SELECT name2FROM products3WHERE price = (SELECT MAX(price) FROM products);

A subquery with several rows

Say you want the customers who have a pending order. There is more than one pending order:

1SELECT name2FROM customers3WHERE id = (SELECT customer_id FROM orders WHERE status = 'pending');

ERROR 1242 (21000): Subquery returns more than 1 row

ERROR 1242: the subquery returned more than one row. = can only compare with one value, but the parentheses gave 3 customer numbers.

IN (subquery)

IN (1, 2, 4) from lesson 3 means "one of these values". Put a subquery in the parentheses and it means "one of the values this subquery gives":

1SELECT id, name2FROM customers3WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending')4ORDER BY id;

Put NOT in front and it means "not one of them". This gives the customers who have never ordered, the same as LEFT JOIN … IS NULL did in lesson 12:

1SELECT id, name2FROM customers3WHERE id NOT IN (SELECT customer_id FROM orders)4ORDER BY id;

Tip: If the subquery of a NOT IN gives even one NULL, the whole condition finds no rows at all (comparing with NULL is never "true"). When the column might have NULLs, add WHERE column IS NOT NULL to the subquery, or use NOT EXISTS, below.

EXISTS

EXISTS (subquery) asks: does the subquery give at least one row? If so, the row stays; if not, it goes. The customers with a pending order can also be found like this:

1SELECT c.id, c.name2FROM customers AS c3WHERE EXISTS (4    SELECT *5    FROM orders AS o6    WHERE o.customer_id = c.id AND o.status = 'pending'7)8ORDER BY c.id;

Look at c.id inside the subquery: it's a column of the outer table. For each row of the outer customers, the query in parentheses runs once with that row's c.id. At customer 1, Ana Silva, it looks for customer 1's pending orders, finds one, and she stays; at customer 3, Chloe Martin, it finds none, and she goes. You can write anything after SELECT; EXISTS only checks whether there are rows.

NOT EXISTS is the opposite: it keeps the rows for which the subquery finds nothing.

A subquery in FROM

A subquery's result is a table, so it can go after FROM, and you can query it. For example, the average order value: first total each order, then average those totals:

1SELECT ROUND(AVG(total), 2) AS avg_order2FROM (3    SELECT order_id, SUM(quantity * unit_price) AS total4    FROM order_items5    GROUP BY order_id6) AS t;

The parentheses give 12 rows, one per order; the outer query treats those 12 rows as a table called t and averages total. A table like this is called a derived table. It must have a name; AS t can't be left out: remove it and run the query again, and you get ERROR 1248, which says every derived table needs its own alias.

Practice

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

1SELECT name, price2FROM products3ORDER BY price;

1SELECT oi.product_id2FROM order_items AS oi3WHERE oi.order_id IN (7, 9, 12);

1SELECT customer_id, COUNT(*) AS n2FROM orders3GROUP BY customer_id;

What you learned

• A subquery is a query in parentheses; it runs first and its result is used by the outer query.
• A subquery that gives one value can be compared with in WHERE; if it gives several rows, = raises ERROR 1242.
• IN (subquery) and NOT IN (subquery) filter by a column of values; NOT IN finds nothing if a NULL turns up.
• EXISTS (subquery) only asks whether there are rows; the subquery can use the outer row's columns.
• A subquery in FROM is a derived table and needs an alias, or you get ERROR 1248.