Subqueries

Game: chapters VII and VIII

A query returns a table, a list or a single value, and each of those can be used inside another query. That is a subquery: a question asked in the middle of a bigger one.

A query in brackets

A subquery is a complete SELECT written between parentheses inside another statement. The database works it out and uses its result where the brackets stand. What you may do with it depends on the shape of that result:

The examples run on the bakery database from the bakery database page.

One value: the scalar subquery

Which products cost more than the average product? The average is a fact about the whole table, while the filter works row by row, so a single query level cannot do both: WHERE price > AVG(price) is refused, because WHERE runs before any aggregate exists (see the GROUP BY page). A subquery computes the average first and hands the number to the outer query.

SELECT name, price, ROUND(price - (SELECT AVG(price) FROM product), 2) AS above_average
FROM product
WHERE price > (SELECT AVG(price) FROM product)
ORDER BY price DESC, id;

The same subquery appears twice, once to filter and once to display the difference; both evaluate to the same 2.87. A scalar subquery must return at most one row and one column. With no row it gives NULL. With several rows PostgreSQL stops with an error ("more than one row returned by a subquery used as an expression"), while SQLite quietly takes the first row and carries on, which is worse: the query runs and the answer is wrong. If a subquery could ever return more than one row, it does not belong after = or >.

A list: IN

x IN (subquery) is true when x equals one of the values the subquery returns. Which customers bought a Hot cross bun, the pastry added to the shelves on Monday 16 March? The subquery walks from the order lines to the orders and collects the customer ids; the outer query lists those customers.

SELECT name, town
FROM customer
WHERE id IN (SELECT o.customer_id
             FROM orders AS o
             JOIN order_item AS oi ON oi.order_id = o.id
             JOIN product AS p ON p.id = oi.product_id
             WHERE p.name = 'Hot cross bun')
ORDER BY id;

A plain join would answer too, but it returns one row per Hot cross bun line: 15 lines from 12 customers, three of whom bought one on two different orders and would appear twice, so you would need DISTINCT to clean up. IN only asks a yes-or-no question about each customer, so every customer appears at most once. Choose the join when you need columns from the other tables in the result (how many, on which day); choose IN when the other tables only decide which rows to keep.

NOT IN is the opposite, with one serious trap: if the subquery's column contains a single NULL, NOT IN keeps no rows at all. The NULL page shows it happening and how to avoid it.

Is there one? EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row, whatever is in it; NOT EXISTS is true when it returns none. What the subquery selects does not matter, which is why people write SELECT 1. The interesting part is its WHERE, which usually refers to the row of the outer query. Which customers have never placed an order?

SELECT c.name, c.joined
FROM customer AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id)
ORDER BY c.id;

Read it the way it is written: keep each customer c for whom there does not exist an order whose customer_id is c.id. NOT EXISTS does the same job as the LEFT JOIN ... IS NULL pattern of the JOIN page, and unlike NOT IN it is never fooled by NULL: a NULL simply matches nothing, so it never stops a row from qualifying.

Correlated subqueries

The subquery in the last example cannot run on its own: c.id belongs to the outer query. Such a subquery is correlated. Logically it is worked out again for every row of the outer query, with that row's values plugged in. (The database is free to find a faster plan, but the result must be the same as if it had.)

Correlation answers questions that compare a row with its own group. Which products cost more than the average of their own category? The inner query uses a second alias, q, for the same table, and its condition ties it to the category of the outer row p.

SELECT p.name, p.category, p.price
FROM product AS p
WHERE p.price > (SELECT AVG(q.price)
                 FROM product AS q
                 WHERE q.category = p.category)
ORDER BY p.category, p.id;

A correlated subquery in SELECT computes one figure per row. Counting each customer's orders this way gives 0 for a customer who never came, with no join and no COALESCE: COUNT of no rows is 0.

SELECT c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) AS orders
FROM customer AS c
WHERE c.town = 'Hillcrest'
ORDER BY c.id;

Always qualify the columns inside a correlated subquery. If the inner condition said WHERE q.category = category, the bare category would be read as q.category, the nearest table that has one, and the condition would compare each row with itself: always true, no error, wrong answer.

A table: subqueries in FROM

A subquery in FROM, often called a derived table, is used like a table: the outer query selects from it, filters it, joins it. It is the classic answer to "an aggregate of an aggregate". How many lines does an order have, on average? First count the lines of each order, then average those counts.

SELECT COUNT(*) AS orders, ROUND(AVG(lines), 2) AS average_lines, MAX(lines) AS most_lines
FROM (SELECT order_id, COUNT(*) AS lines
      FROM order_item
      GROUP BY order_id) AS per_order;

Give the derived table an alias (AS per_order): PostgreSQL required one until version 16, and a name says what the step holds. The columns of the outer query are those the subquery selects, under the names it gives them, so lines exists out here only because the inner query wrote AS lines.

When a JOIN or a CTE reads better

Subqueries nest, and nesting is where they stop being pleasant: a derived table inside a derived table with an IN inside that is read from the inside out, which is the opposite of how people read. A CTE takes the same subquery out of the brackets, gives it a name and puts it first, so the query reads top to bottom. It also lets you use a step twice without writing it twice, and look at each step on its own while you build.

The category comparison above, rewritten with a CTE and a join, computes each category's average once and can show it next to each product, which the correlated version could not do without repeating itself.

WITH category_average AS (
  SELECT category, AVG(price) AS average
  FROM product
  GROUP BY category
)
SELECT p.name, p.category, p.price, ROUND(a.average, 2) AS category_average
FROM product AS p
JOIN category_average AS a ON a.category = p.category
WHERE p.price > a.average
ORDER BY p.category, p.id;

A rough guide. Use a scalar subquery for one number you compare against. Use IN or EXISTS when other tables only decide which rows to keep. Use a join when you need their columns in the result. Use a CTE as soon as a subquery grows past a few lines, appears twice, or would sit inside another one. All of them can express most questions; pick the one a reader will understand first.

Common mistakes

A scalar subquery that can return several rows, which PostgreSQL refuses and SQLite quietly cuts down to its first row. NOT IN over a column that may hold NULL. Unqualified columns inside a correlated subquery, which bind to the inner table. Forgetting the alias of a derived table, or referring outside it to a column it did not select. Writing = (SELECT ...) where IN (SELECT ...) was meant. Nesting three levels deep when three named steps would read in one pass.

Try it: Above their category, No bread, thank you and Shutters down, on the exercises page.

In the game: Chapter VII, The Quietest Hour, and Chapter VIII, The First Order Before Dawn, are where a subquery, or the CTE it can become, earns its place.