Подзапросы в роли столбцов

Подзапрос может быть и столбцом результата. Сколько отзывов у каждой игры?

SELECT g.title,
       (SELECT COUNT(*) FROM reviews AS r WHERE r.game_id = g.id) AS review_count
FROM games AS g
ORDER BY review_count DESC, g.title;

Подзапрос коррелированный, как и те, что с EXISTS: он обращается к g.id — игре из внешней строки. Для каждой игры база данных считает отзывы с идентификатором этой игры, и число попадает в столбец review_count. Castle Siege и остальные игры без отзывов получают 0, потому что COUNT(*) по пустому набору строк равен 0.

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

Вы знаете и другой способ подсчёта: LEFT JOIN с GROUP BY и COUNT(r.stars) из урока о соединениях. Оба дают один и тот же результат. Подзапросы удобны, когда нужно несколько чисел из разных таблиц: каждый подзапрос считает сам по себе, поэтому строки не умножаются, как при одновременном соединении reviews и order_items с играми (одна из ловушек соединений из урока о них).

Типичная ошибка: забыть условие, которое связывает подзапрос со строкой. (SELECT COUNT(*) FROM reviews) считает все отзывы в таблице, так что у каждой игры было бы 25.