Subqueries

A query inside a query

Sometimes the answer to one question depends on the answer to another. Which products cost more than the average? You first need the average, then compare. A subquery lets you do both in one statement: a complete SELECT in parentheses, used inside another statement.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard         |  89.50 |
| Standing Desk Mat           |  55.00 |
| Building Blocks Set         |  49.99 |
+-----------------------------+--------+

MySQL evaluates the inner query first (the average is 46.21), then runs the outer query with that number. Because the inner query returns exactly one value, it is called a scalar subquery and can be used anywhere a single value fits.

If a scalar subquery returns more than one row, MySQL stops with an error:

SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category_id = 1);

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

Subqueries that return a list: IN

When the inner query returns a column of values, test membership with IN. Customers who have placed at least one order:

SELECT id, first_name FROM customers WHERE id IN (SELECT customer_id FROM orders) ORDER BY id;

+----+------------+
| id | first_name |
+----+------------+
|  1 | Asha       |
|  2 | Ben        |
|  3 | Chloe      |
|  4 | Daniel     |
|  5 | Elena      |
|  6 | Farid      |
|  7 | Grace      |
|  8 | Hiro       |
| 10 | Jack       |
| 11 | Kavya      |
+----+------------+

Products in the two cheapest categories by average price, or products ordered on a particular day, follow the same pattern. Here, products that appear in any order placed in June 2024:

SELECT name FROM products
WHERE id IN (
  SELECT oi.product_id FROM order_items oi JOIN orders o ON o.id = oi.order_id
  WHERE o.order_date >= '2024-06-01' AND o.order_date < '2024-07-01'
)
ORDER BY name;

+---------------------+
| name                |
+---------------------+
| Building Blocks Set |
| Mechanical Keyboard |
| SQL Basics Handbook |
| Water Bottle        |
+---------------------+

Be careful with NOT IN. If the inner list can contain NULL, the whole condition returns no rows (see the NULL lesson). Prefer NOT EXISTS, covered in the next lesson.

Subqueries in the SELECT list

A scalar subquery can also compute a column:

SELECT name, price, ROUND(price - (SELECT AVG(price) FROM products), 2) AS difference_from_average
FROM products
WHERE category_id = 5;

+---------------------+-------+-------------------------+
| name                | price | difference_from_average |
+---------------------+-------+-------------------------+
| Building Blocks Set | 49.99 |                    3.78 |
| Puzzle 1000 Pieces  | 19.99 |                  -26.22 |
+---------------------+-------+-------------------------+

Derived tables: subqueries in FROM

A subquery in FROM acts as a temporary table. It must have an alias. This is the way to filter or combine on an aggregate result:

SELECT AVG(order_total) AS average_order_value, MAX(order_total) AS biggest_order
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
) AS totals;

+---------------------+---------------+
| average_order_value | biggest_order |
+---------------------+---------------+
|          102.490500 |        249.97 |
+---------------------+---------------+

The inner query totals every order. The outer query then averages those totals, something you cannot do directly because you cannot nest aggregate functions like AVG(SUM(...)).

Correlated subqueries

So far each inner query ran once, independently. A correlated subquery refers to a column of the outer query, so its result depends on the current outer row. Products that cost more than the average of their own category:

SELECT p.name, p.category_id, p.price
FROM products p
WHERE p.price > (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC;

+-----------------------------+-------------+--------+
| name                        | category_id | price  |
+-----------------------------+-------------+--------+
| Noise-Cancelling Headphones |           1 | 199.99 |
| Mechanical Keyboard         |           1 |  89.50 |
| Data Structures Illustrated |           2 |  45.00 |
| Standing Desk Mat           |           3 |  55.00 |
| Yoga Mat                    |           4 |  27.50 |
| Building Blocks Set         |           5 |  49.99 |
+-----------------------------+-------------+--------+

For each product p, the inner query computes the average price of products in that same category. Conceptually the inner query runs once per outer row, though the optimiser often finds a faster way. It is the SQL equivalent of a nested loop.

Another example counts orders per customer in the select list:

SELECT c.first_name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
WHERE c.id <= 4;

+------------+--------+
| first_name | orders |
+------------+--------+
| Asha       |      4 |
| Ben        |      2 |
| Chloe      |      2 |
| Daniel     |      2 |
+------------+--------+

Subquery or join?

Many problems can be solved either way. "Customers who have ordered" can be an IN subquery, a join with DISTINCT, or an EXISTS. A good guide:

• Use a join when you need columns from both tables in the result.
• Use a subquery when you only need to test or calculate something, and the other table's columns are not needed in the result.
• Use derived tables (or the WITH clause in a later lesson) to break a complicated query into steps.

Since MySQL 5.6, the optimiser rewrites many subqueries into joins automatically, so write whichever is easier to read, then check the speed with EXPLAIN if it matters.

Common mistakes

• A scalar subquery that returns several rows ("Subquery returns more than 1 row").
• Forgetting the alias on a derived table, giving "Every derived table must have its own alias".
• Using NOT IN with a nullable column.
• Writing a correlated subquery on a huge table without an index on the correlated column, which gets very slow.
• Updating a table while selecting from it in a subquery. MySQL refuses with "You can't specify target table for update in FROM clause"; wrap the subquery in a derived table to work around it.

Exercise

Write queries that return: (1) products priced above the average price of the Books category, (2) the customers who have a pending order, using IN, and (3) for each category, the name of its most expensive product, using a correlated subquery.

SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = 2)
ORDER BY price;

SELECT id, first_name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending');

SELECT p.category_id, p.name, p.price
FROM products p
WHERE p.price = (SELECT MAX(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id;

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| USB-C Hub                   |  39.00 |
| Data Structures Illustrated |  45.00 |
| Building Blocks Set         |  49.99 |
| Standing Desk Mat           |  55.00 |
| Mechanical Keyboard         |  89.50 |
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+
+----+------------+
| id | first_name |
+----+------------+
|  2 | Ben        |
|  6 | Farid      |
|  7 | Grace      |
+----+------------+
+-------------+-----------------------------+--------+
| category_id | name                        | price  |
+-------------+-----------------------------+--------+
|           1 | Noise-Cancelling Headphones | 199.99 |
|           2 | Data Structures Illustrated |  45.00 |
|           3 | Standing Desk Mat           |  55.00 |
|           4 | Yoga Mat                    |  27.50 |
|           5 | Building Blocks Set         |  49.99 |
+-------------+-----------------------------+--------+