The LEFT and the RIGHT joins are the inner joins with one promise added. They keep every row from the side that is named first, even when there is nothing on the other side to match it. The unmatched rows come back with the NULLs in the columns of the other table. That single change is the difference between "show me the loans with their books" and "show me every book, including the ones that nobody has borrowed".
The previous lesson in this module covered "How Does an INNER JOIN Match Rows?", and the next lesson in this module is about "When Are FULL OUTER and CROSS Joins Right?".
What do the LEFT and the RIGHT joins preserve?
An inner join answers a question about the intersection. Those are the rows that exist on both of the sides. A left outer join answers a different question. It is all of the left side, and whatever on the right happens to match. The unmatched left rows survive with the NULL columns, and a NULL is not a value that equals anything, so those rows fail every later comparison unless you test them with IS NULL.
What does a LEFT JOIN keep?
You start with the books and you count their loans. A History of Fermentation has never been borrowed, and an inner join would drop it entirely.
SELECT b.book_id, b.title, COUNT(l.loan_id) AS loans
FROM books b
LEFT JOIN loans l ON l.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY b.book_id;book_id title loans
------- --------------------------- -----
1 The Salt Ledger 2
2 Harbour of Small Boats 1
3 Cartography for Beginners 1
4 The Copper Wire 1
5 Winter Light 1
6 The Long Thaw 1
7 Signals and Noise 2
8 The Cartographer's Daughter 1
9 Letters from the Delta 1
10 Orbit of Dust 1
11 The Quiet Harbour 1
12 Field Notes on Birdsong 1
13 A History of Fermentation 0All thirteen of the books are present, and the untouchable one shows a loan count of zero rather than vanishing.
The counting detail matters. The COUNT(l.loan_id) counts the loan identifiers, and the unmatched row has none, so it counts the zero. The COUNT(*) would have returned the 1 for that row instead, because the row itself exists. That is a difference that silently corrupts every "how many" column that is built on an outer join.
How do you find the rows that did not match?
Filtering the outer-join result for the NULL is the standard technique for finding the orphans. You look for the members who joined the library and never borrowed a book.
SELECT m.member_id, m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
WHERE l.loan_id IS NULL
ORDER BY m.member_id;member_id name
--------- ---------
9 Freya AnderssonFreya Andersson is the only one. There are nine members and there is one silent non-borrower.
The filter has to be the IS NULL and not the = NULL, because the NULL = NULL is never true. The entire three-valued logic of the SQL exists to stop a missing value from pretending to match itself.
If the outer side had a genuine NULL in the join column, then the filter could not tell the two cases apart, and that is one more reason to keep the key columns non-nullable.
What does a RIGHT JOIN do?
A RIGHT JOIN keeps every row of the right-hand table instead. It is the same fact seen from the other direction. Every book, with its loans where they exist.
SELECT l.loan_id, b.book_id, b.title
FROM loans l
RIGHT JOIN books b ON l.book_id = b.book_id
WHERE l.loan_id IS NULL
ORDER BY b.book_id;loan_id book_id title
------- ------- -----------------------
NULL 13 A History of FermentationThe unborrowed book appears with a NULL loan identifier.
The RIGHT JOIN and the LEFT JOIN are the same operation with the tables swapped. The loans RIGHT JOIN books is exactly the books LEFT JOIN loans. That is why most of the style guides prefer the LEFT JOIN. It is supported by every engine and every version, while the RIGHT JOIN was missing from SQLite until the version 3.39 of the year 2022, and the older MySQL versions spell it as the RIGHT OUTER JOIN.
You write the LEFT JOIN, and if the order of the tables reads badly, then the fix is to reorder the FROM clause rather than to reach for the RIGHT.
What else does the NULL side break?
Once a column can be a NULL because of the join, every expression on it has to cope. The authors table has one writer with no books at all, and a left join from the authors keeps her.
SELECT a.author_id, a.name, COUNT(b.book_id) AS books
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id
GROUP BY a.author_id, a.name
ORDER BY a.author_id;author_id name books
--------- ----------------- -----
1 Ursula Vane 2
2 Tomas Guerrero 2
3 Iris Lindqvist 2
4 Nnamdi Obi 2
5 Marguerite Aubert 3
6 Kenji Mori 2
7 Selina Voss 0Selina Voss survives with the zero books, and she would have vanished under an inner join.
There are three practical consequences. A NULL column makes the condition WHERE b.genre = 'fiction' drop the unmatched rows anyway, which turns your outer join back into an inner one. That is why the unmatched-row filter has to be written with the IS NULL. A NULL also poisons the arithmetic. The expression b.pages * 1.1 is a NULL and not a zero, so a revenue column that is computed across an outer join silently loses those rows unless you wrap it in the COALESCE. And a NULL in a join column means that the row could never have matched in the first place.
Quick reference
- The LEFT JOIN keeps every row of the left table, and the unmatched right columns are NULL.
- The RIGHT JOIN is the mirror image and it equals a LEFT JOIN with the tables swapped. You prefer the LEFT for the portability.
- You count with the COUNT(right_key) and never with the COUNT(*), on an outer join.
- You find the unmatched rows with the WHERE right_key IS NULL and never with the = NULL.
- A WHERE filter on the right side silently converts an outer join into an inner one.
Outer-join drill: you list every member of the library with the number of the books that they have borrowed, including the member with the zero. Then you write the same query with an inner join, and you explain in one sentence which one of them you would put in a staff report.