Lists from Subqueries: IN

In lesson 3, you used IN with a list of values: WHERE id IN (1, 3, 5). The list doesn't have to be typed by hand: a subquery can make it. Which games have at least one five-star review? The IDs of those games are in the table reviews:

SELECT game_id FROM reviews WHERE stars = 5;

Put this query in parentheses after IN, and the outer query finds the titles of the games:

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

The subquery runs first and returns a list: 1, 1, 3, 6, 7, 10, 14, 15. Then IN checks each game: is its id in the list? Dragon Forge (1) is in the list twice, but that doesn't matter: IN only asks whether the value is there, so each game is in the result at most once.

Unlike the subqueries of the previous exercise, a subquery after IN may return any number of rows, even none (then IN finds nothing). But it must return exactly one column, because IN compares one value with a list of values:

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

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

You could find the games with a join too:

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;

A join makes one row for every matching review, so Dragon Forge, with two five-star reviews, is there twice, and you'd need DISTINCT. Which one to use? When you need columns of the other table in the result, such as the stars or the comment, you need a join. When the other table only decides which rows to keep, IN with a subquery says exactly what you mean: “the games whose ID is among these”.

Typical mistake: = instead of IN. WHERE id = (SELECT game_id FROM reviews WHERE stars = 5) isn't an error in SQLite, but as you saw in the previous exercise, it takes only the first value of the list, so it finds just Dragon Forge.