Learn SQL: Self join
Joining a table to itself
De Kraaien run a mentoring scheme: every youth player can get a mentor from the first teams. "Who mentors whom?" asks Yasmine. Players and mentors are in the same table.
A key to the same table
In players there's a mentor_id. It doesn't point at another table, but at the id of another player in the same table.
Lars Willems has mentor_id 4. Player 4 is Yannick Diallo, the Men A striker.
SELECT id, name, mentor_id FROM players WHERE mentor_id IS NOT NULL| id | name | mentor_id |
|---|---|---|
| 9 | Lars Willems | 4 |
| 10 | Mila Jacobs | 6 |
| 11 | Senne Vermeulen | 2 |
| 12 | Amir Benali | 7 |
| 13 | Lena Michiels | 5 |
| 14 | Finn Coppens | 3 |
| 15 | Noah Lemmens | 4 |
SELECT id, name FROM players WHERE team_id IN (4, 5)| id | name |
|---|---|
| 1 | Jonas Verhoeven |
| 2 | Wout Hermans |
| 3 | Bram De Smet |
| 4 | Yannick Diallo |
| 5 | Elke Van Dyck |
| 6 | Nora Aerts |
| 7 | Charlotte Maes |
| 8 | Ines Goossens |
The same table twice
For a self join you put the same table in your query twice, with two different aliases. p is the player, m the mentor. To the database they're just two tables that happen to have the same rows.
The ON links the player's mentor_id to the mentor's id.
SELECT p.name AS player, m.name AS mentor FROM players p JOIN players m ON p.mentor_id = m.id| player | mentor |
|---|---|
| Lars Willems | Yannick Diallo |
| Mila Jacobs | Nora Aerts |
| Senne Vermeulen | Wout Hermans |
| Amir Benali | Charlotte Maes |
| Lena Michiels | Elke Van Dyck |
| Finn Coppens | Bram De Smet |
| Noah Lemmens | Yannick Diallo |
Try it yourself
Show every youth player with a mentor. Show the player's name, and the mentor's name and position.
Ready for more?
The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson