JOIN: two tables into one result
A JOIN pairs rows from two tables wherever a column matches. INNER keeps only the pairs; LEFT keeps every row of the first table.
Rows meet where the keys match
players has names. scores has points, with a player_id saying whose they are. JOIN ... ON players.id = scores.player_id walks through the pairs and keeps the ones where the two numbers are the same. Pick a result row to see which two rows made it. Then switch to LEFT JOIN and watch Dev appear.
players joined with scores on id = player_id.
Ana has two scores, so she appears twice. Dev has none, so INNER JOIN drops him. The score for player 5 has no player, so it is dropped too: a join only keeps rows that found a partner.
LEFT JOIN keeps the left side
LEFT JOIN keeps every row of the first table. Where there is no partner, the columns from the second table are NULL. That is how you find things that are missing: players who never played, songs nobody streamed, students with no marks.
SELECT p.name
FROM players p
LEFT JOIN scores s ON s.player_id = p.id
WHERE s.player_id IS NULL;Run it: names next to points
Run it, then change one thing and run it again.
Tables players, scores
| id | name |
|---|---|
| 1 | Ana |
| 2 | Ben |
| 3 | Cleo |
| 4 | Dev |
| player_id | game | points |
|---|---|---|
| 1 | Rocket Run | 820 |
| 3 | Rocket Run | 640 |
| 1 | Pixel Golf | 310 |
| 2 | Pixel Golf | 455 |
| 5 | Rocket Run | 990 |
SELECT p.name, s.game, s.points FROM players p JOIN scores s ON s.player_id = p.id ORDER BY s.points DESC;
Every player, played or not
The query below only lists players who have a score. Change it so every player appears once with their number of scores, including Dev with 0.
SELECT p.name, COUNT(*) AS games FROM players p JOIN scores s ON s.player_id = p.id GROUP BY p.id;