CROSS JOIN and fan-out

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• 32
• 12
• 8
• 7

Hint : 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.

Items to match
• trainers CROSS JOIN gyms
• trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id
• trainers t INNER JOIN codemon c ON c.trainer_id = t.trainer_id
• trainers t INNER JOIN codemon c ON ... INNER JOIN badges bd ON ... for Alex (2 Codémons, 3 badges)
Definitions
• 20
• 10
• 7
• 6

Exercise 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.

Starter code
-- Write your query below
Back to the course