Запрос внутри запроса

Менеджер Pixel Shop хочет узнать, какие игры стоят дороже средней цены игры. Как найти среднее, вы уже знаете:

SELECT AVG(price) FROM games;

Можно написать второй запрос с WHERE price > 28.5. Это работает, но число вы переписали вручную: когда магазин изменит цену, изменится и среднее, и ваш запрос молча начнёт выдавать неверный ответ. А очевидный короткий путь не работает вообще:

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

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

WHERE решает про одну строку за раз, до того как создаются какие-либо группы (вспомните порядок, в котором выполняются части запроса), поэтому среднего, с которым можно было бы сравнить, там ещё нет.

Выход — поставить первый запрос в скобках прямо туда, где стояло число:

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

Запрос в скобках — это подзапрос: запрос внутри другого запроса. База данных выполняет его первым, получает одно значение, 28.5, и затем выполняет внешний запрос так, будто вы вписали 28.5 на его место. Но, в отличие от набранного вручную числа, подзапрос заново вычисляет среднее при каждом запуске запроса, поэтому оно всегда верное.

Подзапрос — это полноценный запрос со своими SELECT и FROM, и у него может быть свой WHERE. Если он возвращает одно значение (одну строку с одним столбцом), его можно использовать везде, где может стоять число, текст или дата. Этот запрос находит последний заказ: подзапрос находит самую позднюю дату, а внешний запрос — заказ, сделанный в этот день:

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

Подзапрос может быть и частью вычисления: price - (SELECT AVG(price) FROM games) AS difference в SELECT покажет, насколько каждая игра дороже или дешевле среднего.

Типичная ошибка: подзапрос находит несколько строк там, где ожидается одно значение. Какие игры стоят столько же, сколько игра жанра Action?

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

Игр жанра Action четыре, за $25, $35, $30 и $40, а запрос нашёл только игры за $25. В SQLite это не ошибка: когда подзапрос, используемый как значение, находит несколько строк, SQLite молча берёт первую (здесь — цену Shadow Ninja, первой игры жанра Action в таблице) и игнорирует остальные. Другие базы данных останавливаются с ошибкой. Поэтому подзапрос в роли значения должен возвращать ровно одно значение: обычно это агрегатная функция, такая как AVG() или MAX(), либо условие, которому соответствует ровно одна строка. Для целого списка значений есть IN, о нём — дальше.