Self-joins
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — A table joined to itself
"Which trainers come from the same hometown?" compares trainers with other trainers. Nothing stops you from joining a table to itself — you just need two aliases to tell the two copies apart:
SELECT t1.name AS trainer, t2.name AS neighbour, t1.hometown
FROM trainers t1
INNER JOIN trainers t2 ON t1.hometown = t2.hometown
AND t1.trainer_id < t2.trainer_id;t1 and t2 are the same table read twice. The condition t1.trainer_id < t2.trainer_id does two jobs: it removes the pairs of a trainer with themselves, and it keeps each pair only once (Morgan–Jordan, but not also Jordan–Morgan).
Try it without the second condition
With only t1.hometown = t2.hometown, every trainer matches themselves (4 useless rows) and Morgan–Jordan appears twice. < is the standard trick to get clean pairs.
Exercise 2 — The same table in several roles
The battles table stores two fighters and a winner — three foreign keys that all point to trainers:
To display three names, join trainers three times, once per role, each with its own alias:
SELECT b.battle_date,
t1.name AS fighter_1,
t2.name AS fighter_2,
w.name AS winner
FROM battles b
INNER JOIN trainers t1 ON b.trainer1_id = t1.trainer_id
INNER JOIN trainers t2 ON b.trainer2_id = t2.trainer_id
INNER JOIN trainers w ON b.winner_id = w.trainer_id
ORDER BY b.battle_date;Each alias follows one foreign key: t1 the first fighter, t2 the second, w the winner. Same table, three different rows picked per battle.
Name the aliases after the role
w for winner, t1 / t2 for the fighters: an alias that says what the copy represents makes a self-join readable months later.
Exercise 3 — Why the <?
In a self-join on trainers, why add t1.trainer_id < t2.trainer_id to the ON condition?
Hint : Think about Morgan–Morgan and about Morgan–Jordan vs Jordan–Morgan.
Exercise 4 — Same type
Complete the self-join that lists the pairs of Codémons sharing the same type, each pair once.
Hint : The second copy is the same table; != would list each pair twice.
Exercise 5 — Who is who
In the battles query, match each alias with the row it represents.
FROM battles b
INNER JOIN trainers t1 ON b.trainer1_id = t1.trainer_id
INNER JOIN trainers t2 ON b.trainer2_id = t2.trainer_id
INNER JOIN trainers w ON b.winner_id = w.trainer_idbt1t2wExercise 6 — Count the pairs
Two Codémons are fire type (Pyrochu, Firochu) and two are plant type (Florachu, Plantachu); every other type appears once. How many rows does this query return?
SELECT c1.name, c2.name
FROM codemon c1
INNER JOIN codemon c2 ON c1.type = c2.type
AND c1.codemon_id < c2.codemon_id;2480Hint : One row per unordered pair of same-type Codémons.
Exercise 7 — Name the winner
Complete the query so that each battle shows the date and the winner's name.
Hint : The alias w follows the foreign key that stores the winner.
Exercise 8 — Win counter
The league wants a win count for every trainer — including those who never won, and even Jordan who never fought.
Write a query that returns each trainer's name as trainer and their number of victories as wins (0 when they have none). Sort by wins descending, then by trainer.
-- Write your query below