The previous lesson covered INNER JOIN and LEFT JOIN — the two you'll use most. This one rounds out the family: RIGHT, FULL, CROSS, and the trick of joining a table to itself.
The seed has a small organization: employees (with a manager_id pointing back into the same table) and departments (one of which has no employees). There are also two tiny shirt_sizes/shirt_colors tables for the CROSS JOIN example.
RIGHT JOIN: LEFT JOIN, mirrored
RIGHT JOIN keeps every row from the right table, NULL-padding the left when there's no match. It's LEFT JOIN with the tables swapped — and that's almost always how people write it instead, because reading order matters.
SELECT d.name AS department, e.full_name
FROM employees e
RIGHT JOIN departments d ON d.id = e.department_id
ORDER BY d.name, e.full_name;
Operations has no employees — it shows up with full_name as NULL. Same shape if you reversed the tables and used LEFT JOIN. In practice: pick LEFT and put the "keep all of these" table on the left. Reads more naturally.
FULL JOIN: keep both sides
FULL OUTER JOIN (the OUTER is optional) keeps every row from both tables: matched pairs where possible, plus left rows with no right match, plus right rows with no left match — each padded with NULLs on the side that didn't have a partner.
SELECT e.full_name, d.name AS department
FROM employees e
FULL JOIN departments d ON d.id = e.department_id
ORDER BY e.full_name NULLS LAST, d.name;
Three flavors in the result:
- Most rows: an employee with a department.
- Edsger and Donald (contractors):
departmentis NULL. - Operations: is NULL.