A Query Inside a Query

The manager of Pixel Shop wants to know which games cost more than the average game. You know how to find the average:

SELECT AVG(price) FROM games;

So you could write a second query with WHERE price > 28.5. It works, but you've copied the number by hand: when the shop changes a price, the average changes too, and your query quietly gives a wrong answer. And the obvious shortcut doesn't work at all:

SELECT title
FROM games
WHERE price > AVG(price);

Error on line 1: misuse of aggregate function AVG()

WHERE decides about one row at a time, before any groups are made (remember the order in which the parts of a query run), so there's no average yet that it could compare with.

The way out is to put the first query, in parentheses, right where the number was:

SELECT title, price
FROM games
WHERE price > (SELECT AVG(price) FROM games)
ORDER BY price DESC, title;

The query in parentheses is a subquery: a query inside another query. The database runs it first, gets one value, 28.5, and then runs the outer query as if you had typed 28.5 in its place. But unlike a typed number, the subquery computes the average again every time the query runs, so it's always right.

A subquery is a complete query, with its own SELECT and FROM, and it can have its own WHERE. When it returns a single value (one row with one column), you can use it wherever a number, a text or a date could stand. This query finds the latest order: the subquery finds the latest date, and the outer query the order placed on that day:

SELECT *
FROM orders
WHERE ordered_at = (SELECT MAX(ordered_at) FROM orders);

A subquery can be part of a calculation too: price - (SELECT AVG(price) FROM games) AS difference in SELECT shows how much more or less than the average each game costs.

Typical mistake: a subquery that finds several rows where one value is expected. Which games cost the same as an Action game?

SELECT title, price
FROM games
WHERE price = (SELECT price FROM games WHERE genre = 'Action')
ORDER BY title;

There are four Action games, for $25, $35, $30 and $40, but the query found only the games for $25. It isn't an error in SQLite: when a subquery used as a value finds several rows, SQLite quietly takes the first one (here the price of Shadow Ninja, the first Action game in the table) and ignores the others. Other databases stop with an error. A subquery used as a value should therefore return exactly one value: usually an aggregate function such as AVG() or MAX(), or a condition that matches exactly one row. For a whole list of values, there's IN, which comes next.