Списки из подзапросов: IN

В уроке 3 вы использовали IN со списком значений: WHERE id IN (1, 3, 5). Список не обязательно набирать вручную: его может составить подзапрос. У каких игр есть хотя бы один отзыв на пять звёзд? Идентификаторы этих игр лежат в таблице reviews:

SELECT game_id FROM reviews WHERE stars = 5;

Поставьте этот запрос в скобки после IN, и внешний запрос найдёт названия игр:

SELECT title
FROM games
WHERE id IN (SELECT game_id FROM reviews WHERE stars = 5)
ORDER BY title;

Подзапрос выполняется первым и возвращает список: 1, 1, 3, 6, 7, 10, 14, 15. Затем IN проверяет каждую игру: входит ли её id в список? Dragon Forge (1) встречается в списке дважды, но это неважно: IN лишь спрашивает, есть ли значение в списке, поэтому каждая игра попадает в результат не больше одного раза.

В отличие от подзапросов из предыдущего упражнения, подзапрос после IN может вернуть любое число строк, даже ни одной (тогда IN ничего не находит). Но он должен вернуть ровно один столбец, потому что IN сравнивает одно значение со списком значений:

SELECT title
FROM games
WHERE id IN (SELECT game_id, stars FROM reviews);

Error on line 1: sub-select returns 2 columns - expected 1

Игры можно найти и с помощью соединения:

SELECT g.title
FROM games AS g
JOIN reviews AS r ON r.game_id = g.id
WHERE r.stars = 5
ORDER BY g.title;

Соединение создаёт по одной строке на каждый подходящий отзыв, поэтому Dragon Forge с двумя отзывами на пять звёзд оказывается в результате дважды, и понадобился бы DISTINCT. Что выбрать? Если в результате нужны столбцы другой таблицы, например оценки или комментарий, нужно соединение. Если другая таблица лишь решает, какие строки оставить, то IN с подзапросом говорит ровно то, что вы имеете в виду: «игры, чей идентификатор есть среди этих».

Типичная ошибка: = вместо IN. WHERE id = (SELECT game_id FROM reviews WHERE stars = 5) не вызывает ошибку в SQLite, но, как вы видели в предыдущем упражнении, берёт только первое значение списка, поэтому находит лишь Dragon Forge.