Subqueries

A subquery is a query inside another query, written in parentheses. It lets you use the result of one question to answer another: products priced above average, customers who ordered a particular item, each department's share of the payroll. Subqueries can return a single value, a list or a whole table.

Scalar subqueries: a single value

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

The inner query runs first and returns one value (the average price, 36.65); the outer query then uses it like a constant. A subquery used this way must return exactly one row and one column.

Subqueries in the select list

SELECT name, price,
       round(price - (SELECT AVG(price) FROM products), 2) AS vs_average
FROM products
WHERE category = 'Books';

The most expensive product

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

This is the correct way to get "the row with the maximum value" — unlike SELECT name, MAX(price) (lesson 18), it works in every database and returns all products if several share the top price.

List subqueries with IN

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

The inner query returns a list of customer ids; IN checks membership. Remember the NOT IN and NULL trap from lesson 9.

SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

Table subqueries in FROM

SELECT category, round(AVG(line_total), 2) AS avg_line_value
FROM (
  SELECT p.category, oi.quantity * oi.unit_price AS line_total
  FROM order_items AS oi
  JOIN products AS p ON p.id = oi.product_id
) AS lines
GROUP BY category
ORDER BY avg_line_value DESC;

A subquery in FROM (a derived table) acts like a temporary table. It must have an alias in most databases. Derived tables let you aggregate in stages — for example, averaging per-customer totals:

SELECT round(AVG(total), 2) AS avg_spend_per_customer, MAX(total) AS biggest_spender
FROM (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  GROUP BY o.customer_id
) AS per_customer;

Comparing to a group's value

SELECT d.name AS department, e.first_name, e.salary
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
JOIN (SELECT department_id, MAX(salary) AS top FROM employees GROUP BY department_id) AS m
  ON m.department_id = e.department_id AND m.top = e.salary
ORDER BY d.name;

The highest-paid employee in each department: join each employee to their department's maximum. Window functions (lesson 34) offer another way.

Subqueries in other statements

UPDATE products SET price = price * 0.9
WHERE id IN (SELECT product_id FROM order_items GROUP BY product_id HAVING SUM(quantity) < 2);

Subqueries work in UPDATE, DELETE and INSERT … SELECT statements too (lessons 35–37).

Try it yourself

Find employees who earn more than the average salary of their own department. (Hint: this needs the inner query to refer to the outer row — the subject of the next lesson.)