Subqueries as Columns

A subquery can also be a column of the result. How many reviews does each game have?

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;

The subquery is correlated, like the ones with EXISTS: it refers to g.id, the game of the outer row. For each game, the database counts the reviews with that game's ID, and the number goes into the column review_count. Castle Siege and the other games without a review get 0, because COUNT(*) of no rows is 0.

A subquery in SELECT must give one value for each row: it must return one column, and it should find at most one row (of several, SQLite would quietly take the first one), so it's usually an aggregate. When it finds no row at all, the value is NULL. And give the column an alias, otherwise it's named by the whole text of the subquery.

You know another way to count: LEFT JOIN with GROUP BY and COUNT(r.stars), from lesson 6. Both give the same result. A subquery is handy when you need several numbers from different tables: each subquery counts on its own, so no rows multiply, as they do when you join both reviews and order_items to the games at once (one of the join pitfalls of lesson 6).

Typical mistake: forgetting the condition that links the subquery to the row. (SELECT COUNT(*) FROM reviews) counts all the reviews in the table, so every game would show 25.