Subqueries

A subquery is a query inside another query, in parentheses. It lets you use the result of one question to answer another.

A single value

Which products cost more than the average?

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

+-----------------------------+--------+
|            name             | price  |
+-----------------------------+--------+
| 27-inch Monitor             | 249.99 |
| Noise-Cancelling Headphones |    199 |
+-----------------------------+--------+

The inner query computes the average (about 112.57); the outer query uses it as a value. A subquery that returns one value is a scalar subquery, and it can go anywhere a value can — even in SELECT:

SELECT name, price, ROUND(price - (SELECT AVG(price) FROM products), 2) AS vs_average
FROM products
ORDER BY vs_average DESC;

+-----------------------------+--------+------------+
|            name             | price  | vs_average |
+-----------------------------+--------+------------+
| 27-inch Monitor             | 249.99 |     137.43 |
| Noise-Cancelling Headphones |    199 |      86.44 |
| Mechanical Keyboard         |     89 |     -23.56 |
| Webcam                      |   59.9 |     -52.66 |
| Laptop Stand                |     42 |     -70.56 |
| USB-C Hub                   |   35.5 |     -77.06 |
+-----------------------------+--------+------------+

A list of values: IN

Which customers have placed a pending order?

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

+--------------+
|     name     |
+--------------+
| Ada Lovelace |
| Alan Turing  |
+--------------+

A table: subqueries in FROM

A subquery in FROM acts as a temporary table (often called a derived table), which must have an alias:

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

+---------------------+
| average_order_value |
+---------------------+
|           224.75875 |
+---------------------+

First total each order, then average the totals — two levels of aggregation that a single GROUP BY can't do.

The NOT IN trap

NOT IN with a subquery that returns any NULL returns no rows:

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

This returns no rows — even though several employees manage nobody — because Sofia's manager_id is NULL, and "is 5 not in (1, 1, 1, 4, 1, NULL)?" is unknown. Filter out NULLs, or use NOT EXISTS (next lesson):

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

+-------+
| name  |
+-------+
| Omar  |
| Priya |
| Lucia |
| Kofi  |
+-------+

Practice

Find the most expensive product using a subquery (not ORDER BY / LIMIT).

SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

+-----------------+--------+
|      name       | price  |
+-----------------+--------+
| 27-inch Monitor | 249.99 |
+-----------------+--------+