LEFT and RIGHT joins are inner joins with one promise added: keep every row from the side that is named first, even when there is nothing on the other side to match. The unmatched rows come back with NULLs in the other table's columns. That single change is the difference between "show me loans with their books" and "show me every book, including the ones nobody has borrowed".
What do LEFT and RIGHT joins preserve?
An inner join answers a question about the intersection: rows that exist on both sides. A left outer join answers a different question: all of the left side, and whatever on the right happens to match. The unmatched left rows survive with NULL columns — and 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?
Start with books and 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 0
All thirteen books are present, and the untouchable one shows a loan count of zero rather than vanishing. The counting detail matters: COUNT(l.loan_id) counts loan identifiers, and the unmatched row has none, so it counts zero. COUNT(*) would have returned 1 for that row instead, because the row itself exists — a difference that silently corrupts every "how many" column 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 orphans. 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 Andersson
Freya Andersson is the only one — nine members, one silent non-borrower. The filter has to be IS NULL and not = NULL, because NULL = NULL is never true; the entire SQL three-valued logic exists to stop a missing value from pretending to match itself. If the outer side had a genuine NULL in the join column, the filter could not distinguish the two cases — which is one more reason to keep key columns non-nullable.
What does a RIGHT JOIN do?
A RIGHT JOIN keeps every row of the right-hand table instead. 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 Fermentation
The unborrowed book appears with a NULL loan identifier. RIGHT JOIN and LEFT JOIN are the same operation with the tables swapped: loans RIGHT JOIN books is exactly books LEFT JOIN loans. That is why most style guides prefer LEFT JOIN — it is supported by every engine and every version, while RIGHT JOIN was missing from SQLite until version 3.39 (2022) and older MySQL versions spell it RIGHT OUTER JOIN. Write the LEFT JOIN and, if the table order reads badly, the fix is to reorder the FROM clause rather than to reach for RIGHT.
What else does the NULL side break?
Once a column can be NULL because of the join, every expression on it has to cope. The author table has one writer with no books at all, and a left join from 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 0
Selina Voss survives with zero books, and she would have vanished under an inner join. Three practical consequences follow. A NULL column makes WHERE b.genre = 'fiction' drop the unmatched rows anyway, turning your outer join back into an inner one — which is why the unmatched-row filter has to be written with IS NULL. A NULL also poisons arithmetic: b.pages * 1.1 is NULL, not zero, so a revenue column computed across an outer join silently loses those rows unless you wrap it in COALESCE. And a NULL in a join column means the row could never have matched in the first place.
Quick reference
- LEFT JOIN keeps every row of the left table; unmatched right columns are NULL.
- RIGHT JOIN is the mirror image and equals a LEFT JOIN with the tables swapped — prefer LEFT for portability.
- Count with COUNT(right_key), never COUNT(*), on an outer join.
- Find unmatched rows with WHERE right_key IS NULL, never with = NULL.
- A WHERE filter on the right side silently converts an outer join into an inner one.
Outer-join drill: list every member of the library with the number of books they have borrowed, including the member with zero — then write the same query with an inner join and explain in one sentence which one you would put in a staff report.