RIGHT and FULL JOIN
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — RIGHT JOIN: the mirror image
RIGHT JOIN is the mirror of LEFT JOIN: it keeps every row of the right table (the one after JOIN).
SELECT t.name AS trainer, c.name AS codemon
FROM trainers t
RIGHT JOIN codemon c ON c.trainer_id = t.trainer_id
ORDER BY c.name;Every Codémon is there, and Plantachu has NULL as trainer. This is exactly the result of FROM codemon c LEFT JOIN trainers t from the previous chapter — the same question asked from the other side.
Many people never write RIGHT JOIN
Since you can always swap the two tables and use LEFT JOIN, most developers only use LEFT JOIN and read queries from left to right. Know RIGHT JOIN exists, but don't feel obliged to use it.
Exercise 2 — FULL OUTER JOIN: nobody left behind
FULL OUTER JOIN keeps the rows of both tables: the matches, plus the unmatched rows of the left side, plus the unmatched rows of the right side.
SELECT t.name AS trainer, c.name AS codemon
FROM trainers t
FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id
ORDER BY t.name, c.name;Nine rows: the 7 real pairs, Jordan with no Codémon, and Plantachu with no trainer. Each unmatched row has NULL on the side where nothing matched.
Four joins, one picture
Think of the two circles: INNER = the overlap only, LEFT = the whole left circle, RIGHT = the whole right circle, FULL = both circles.
Exercise 3 — Count the full join
4 trainers (one owns nothing), 8 Codémons (one is wild), 7 real pairs. How many rows does this query return?
SELECT t.name, c.name
FROM trainers t
FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id;97812Hint : The matches, plus one row per unmatched trainer, plus one row per unmatched Codémon.
Exercise 4 — Protect the right side
Complete the query so that every Codémon appears, with its trainer when it has one, without swapping the two tables.
Hint : The Codémons are on the right of the JOIN keyword.
Exercise 5 — What each join keeps
Match each join with the rows it keeps.
INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINExercise 6 — Pick the join
You want exactly one row per Codémon, with the trainer's name when there is one, and you start the query with FROM trainers t. Which join do you need?
RIGHT JOIN codemon c ON c.trainer_id = t.trainer_idLEFT JOIN codemon c ON c.trainer_id = t.trainer_idINNER JOIN codemon c ON c.trainer_id = t.trainer_idFULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_idHint : LEFT would lose Plantachu, FULL would add a Jordan row with no Codémon.
Exercise 7 — A friendlier NULL
Complete the query so that every Codémon is listed with its trainer's name, or the word wild when it has no trainer.
Hint : Keep every Codémon, then replace the missing trainer name with a fallback — you learned that function in the lesson on conditional logic and NULL functions.
Exercise 8 — The complete picture
The league wants one list with everybody: every trainer and every Codémon, paired when possible.
Write a query that returns trainer (the trainer's name, or nobody when a Codémon has no trainer) and codemon (the Codémon's name, or none when a trainer owns nothing), using a FULL OUTER JOIN. Sort by trainer, then codemon.
-- Write your query below