Join Pitfalls

Joins are powerful, and that's why their mistakes are dangerous: most of them give no error, just a wrong result that looks believable. Here are the three most common ones.

1. A forgotten ON. SQLite lets you write JOIN without a condition. Then nothing says which rows belong together, so it pairs every row of the left table with every row of the right one:

SELECT COUNT(*) AS row_count
FROM customers AS c
JOIN orders AS o;

14 customers times 30 orders: 420 rows, every possible combination. Such a join is called a cross join. Sometimes it's what you want (every customer with every game, say), but usually it's a forgotten ON. When a result has far more rows than you expected, check the ON of each join. You may also meet an older way of joining, with the tables separated by commas and the condition in WHERE: FROM customers c, orders o WHERE o.customer_id = c.id. Forget the WHERE there, and you get the same 420 rows.

2. Rows that multiply. A join gives a row for each pair. When you join two tables to the games at once, such as the reviews and the order items, each review of a game is paired with each of its items. Dragon Forge has 4 reviews and was in 4 orders:

SELECT g.title, COUNT(r.stars) AS review_count, SUM(i.quantity) AS copies
FROM games AS g
JOIN reviews AS r ON r.game_id = g.id
JOIN order_items AS i ON i.game_id = g.id
WHERE g.id = 1
GROUP BY g.id, g.title;

16 reviews and 16 copies, four times too many: each of the 4 reviews met each of the 4 items, and that makes 16 rows. Nothing tells you that something's wrong; the numbers are just too big. A count can be saved with COUNT(DISTINCT …) of a key: each review is one customer's (the key of reviews is the game and the customer), so COUNT(DISTINCT r.customer_id) counts each review once, however many times its row was repeated. A sum can't be saved this way, because two different items can have the same quantity. Get it with a separate query, or with a subquery, which you'll learn in the next lesson.

3. A condition in ON or in WHERE. With JOIN, it doesn't matter whether a condition is in ON or in WHERE. With LEFT JOIN, it does. How many five-star reviews does each game have?

SELECT g.title, COUNT(r.stars) AS five_stars
FROM games AS g
LEFT JOIN reviews AS r ON r.game_id = g.id
WHERE r.stars = 5
GROUP BY g.id, g.title
ORDER BY g.title;

The games without a five-star review are missing, although that's what LEFT JOIN was there for. WHERE runs after the join: the rows with NULLs and the rows of the other reviews don't pass r.stars = 5, and WHERE throws them out, games and all. The condition belongs in ON:

SELECT g.title, COUNT(r.stars) AS five_stars
FROM games AS g
LEFT JOIN reviews AS r ON r.game_id = g.id AND r.stars = 5
GROUP BY g.id, g.title
ORDER BY g.title;

A condition in ON decides which rows of the right table count as a match, and LEFT JOIN keeps every row of the left table anyway: a game without a five-star review stays, with NULLs, and COUNT gives it 0. So with LEFT JOIN, a condition on the right table goes in ON, and a condition on the left table in WHERE.