Learn SQL: JOIN
Linking tables with JOIN
"I just want a list of every player with the name of their team," sighs Yasmine. "Without looking it up every single time." So you let the database do the looking up.
JOIN ... ON
JOIN sticks two tables together. After ON you say which rows belong together: here the player and the team where players.team_id equals teams.id.
Because both tables have a name column, you put the table name in front: players.name and teams.name.
SELECT players.name, teams.name FROM players JOIN teams ON players.team_id = teams.id| name | name |
|---|---|
| Jonas Verhoeven | Men A |
| Wout Hermans | Men A |
| Bram De Smet | Men A |
| Yannick Diallo | Men A |
| Elke Van Dyck | Women A |
| Nora Aerts | Women A |
| Charlotte Maes | Women A |
| Ines Goossens | Women A |
No partner, no row
JOIN is short for INNER JOIN: you only get rows that have a partner on both sides. Robbe and Fatima aren't in a team, so they drop out: 22 players, but 20 rows.
Remember that. Often it's exactly what you want, but sometimes you lose rows without noticing.
SELECT COUNT(*) AS rows_after_join FROM players JOIN teams ON players.team_id = teams.id| rows_after_join |
|---|
| 20 |
SELECT COUNT(*) AS players FROM players| players |
|---|
| 22 |
Try it yourself
For every match, show the date, the opponent and the name of the team that played.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson