CROSS JOIN and fan-out
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — CROSS JOIN: every combination
CROSS JOIN has no ON: it pairs every row of the first table with every row of the second. 4 trainers × 5 gyms = 20 combinations:
SELECT t.name AS trainer, g.name AS gym
FROM trainers t
CROSS JOIN gyms g
ORDER BY t.name, g.name;Most of the time this is not what you want — but it is perfect to build a grid of possibilities: every challenge a trainer could attempt.
The accidental cross join
Remember: an INNER JOIN whose ON you forgot behaves exactly like a CROSS JOIN. If a result suddenly has far too many rows, check your join conditions first.
Exercise 2 — Gyms still to conquer
Combine the grid with an anti-join and you get every gym a trainer has not beaten yet: all combinations, minus the badges already earned.
SELECT t.name AS trainer, g.name AS gym
FROM trainers t
CROSS JOIN gyms g
LEFT JOIN badges bd ON bd.trainer_id = t.trainer_id
AND bd.gym_id = g.gym_id
WHERE bd.badge_id IS NULL
ORDER BY t.name, g.name;The LEFT JOIN tries to attach a badge to each (trainer, gym) pair; the pairs that stay NULL are the open challenges. Morgan has beaten everything, Jordan has it all ahead.
Three ideas in one query
A cross join for the grid, a LEFT JOIN with two conditions in ON to look up the badge, and the IS NULL filter from the previous lesson. Intermediate SQL is mostly combining simple pieces.
Exercise 3 — Fan-out: when joins multiply rows
Joining a trainer to their Codémons and to their badges in the same query multiplies the rows: Alex has 2 Codémons and 3 badges, so Alex appears 2 × 3 = 6 times. This is called fan-out, and it breaks naive counts:
SELECT t.name AS trainer,
COUNT(*) AS rows_produced,
COUNT(c.codemon_id) AS codemon_wrong,
COUNT(bd.badge_id) AS badges_wrong
FROM trainers t
INNER JOIN codemon c ON c.trainer_id = t.trainer_id
INNER JOIN badges bd ON bd.trainer_id = t.trainer_id
GROUP BY t.name;Every count says 6 for Alex. COUNT(DISTINCT ...) on each table's primary key counts each Codémon and each badge once, whatever the number of rows:
SELECT t.name AS trainer,
COUNT(DISTINCT c.codemon_id) AS codemon_count,
COUNT(DISTINCT bd.badge_id) AS badge_count
FROM trainers t
INNER JOIN codemon c ON c.trainer_id = t.trainer_id
INNER JOIN badges bd ON bd.trainer_id = t.trainer_id
GROUP BY t.name;SUM and AVG are not saved by DISTINCT
COUNT(DISTINCT key) fixes counts, but a SUM(c.level) over fanned-out rows is still multiplied. When you need sums from two one-to-many tables, aggregate them separately (subqueries, next lesson).
Exercise 4 — Size of the grid
4 trainers and 8 Codémons. How many rows does SELECT * FROM trainers CROSS JOIN codemon; return?
321287Hint : Every trainer with every Codémon.
Exercise 5 — Undo the fan-out
Complete the query so that each trainer's number of Codémons and number of badges are correct despite the two joins.
Hint : Count each key only once.
Exercise 6 — How many rows?
4 trainers, 5 gyms, 8 Codémons (one wild), 10 badges. Match each FROM clause with the number of rows it produces.
trainers CROSS JOIN gymstrainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_idtrainers t INNER JOIN codemon c ON c.trainer_id = t.trainer_idtrainers t INNER JOIN codemon c ON ... INNER JOIN badges bd ON ... for Alex (2 Codémons, 3 badges)201076Exercise 7 — Jordan's to-do list
The Azure City trainers want to know which gyms they still have to beat.
Write a query that returns trainer and gym for every gym a trainer has not earned a badge from, only for trainers whose hometown is Azure City. Sort by trainer, then gym.
-- Write your query below