Self-joins

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

▶ Do the exercises

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

Options
• To drop the rows pairing a trainer with themselves and to list each pair only once
• To sort the result by trainer_id
• Because SQL forbids joining a table to itself without it
• To keep only the trainers with a small id

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_id
Items to match
• b
• t1
• t2
• w
Definitions
• One battle
• The trainer stored in trainer1_id
• The trainer stored in trainer2_id
• The trainer who won the battle

Exercise 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;
Options
• 2
• 4
• 8
• 0

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

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