BrevFeed
Sign in

17. OUTER JOIN: keeping the rows that don't match

📖 Reading · 8 min
💡 Every code box below is live — edit it and hit Run.

The problem INNER JOIN can't solve

From the previous lesson: INNER JOIN only returns rows where both sides match. That means "which customers have never rented anything" is unanswerable with an inner join — a customer with zero matching rows in rental simply doesn't appear in the result at all. There's nothing to filter; the row was never there to begin with.

Outer joins solve exactly this: they preserve rows from one (or both) sides even when there's no match, filling the unmatched side's columns with NULL.

LEFT (OUTER) JOIN

Keeps every row from the left table, matched with the right table's columns where a match exists, NULL where it doesn't:

SELECT c.first_name, c.last_name, r.rental_id
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
ORDER BY c.customer_id
LIMIT 5;

LEFT OUTER JOIN and LEFT JOIN are the same thing — OUTER is implied and almost always omitted in practice.

The single most useful outer-join pattern: find rows on the left with no match on the right, by adding WHERE <right-side-key> IS NULL after the join:

-- Customers who have never rented anything.
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
WHERE r.rental_id IS NULL;

Read this carefully: r.rental_id IS NULL only happens for a row where LEFT JOIN couldn't find a match — the entire right side was filled with NULLs for that row. This is the "anti-join" pattern, and you'll use it constantly for "find X with no corresponding Y."

RIGHT (OUTER) JOIN

The mirror image — keeps every row from the right table instead:

SELECT c.first_name, r.rental_id
FROM rental r
RIGHT JOIN customer c ON r.customer_id = c.customer_id
ORDER BY c.customer_id
LIMIT 5;

In practice, RIGHT JOIN is rarely used — anything expressible with RIGHT JOIN can be rewritten as a LEFT JOIN by swapping which table is written first, and most style guides (this course included) prefer LEFT JOIN exclusively for consistency, since mixing both makes a query with several joins much harder to read at a glance. Recognize RIGHT JOIN on sight; don't reach for it yourself.

FULL (OUTER) JOIN

Keeps rows from both sides regardless of match — NULL-fills whichever side didn't match:

-- Every customer AND every rental, matched where possible, NULL where not.
-- (In pagila every rental has a valid customer_id due to the FK
-- constraint, so this particular pair won't show unmatched rental rows —
-- but the customers-with-no-rentals case still surfaces exactly like
-- the LEFT JOIN example above.)
SELECT c.customer_id, r.rental_id
FROM customer c
FULL JOIN rental r ON r.customer_id = c.customer_id
ORDER BY c.customer_id
LIMIT 5;

FULL JOIN is the least common of the three in everyday querying — reconciliation-style problems ("what's in table A but not B, AND what's in B but not A, in one result") are its main use case.

The ON-vs-WHERE distinction — where it actually bites

The INNER JOIN lesson mentioned that ON vs WHERE are equivalent for INNER JOIN. They are not equivalent for outer joins, and this is one of the most common real-world join bugs. Compare:

-- Extra condition INSIDE the ON clause: still LEFT JOIN semantics —
-- every customer appears, "PG" filtering only affects which rental ROWS
-- match, not whether the customer row survives.
SELECT c.customer_id, r.rental_id
FROM customer c
LEFT JOIN rental r
    ON r.customer_id = c.customer_id
    AND r.rental_date > '2022-08-22'
ORDER BY c.customer_id
LIMIT 5;

-- The SAME-LOOKING condition moved to WHERE: this silently turns the
-- LEFT JOIN back into something that behaves like an INNER JOIN for
-- customers with no matching rental_date — because WHERE filters AFTER
-- the join, and NULL > '2022-08-22' evaluates to UNKNOWN (recall
-- three-valued logic), which WHERE discards just like FALSE.
SELECT c.customer_id, r.rental_id
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
WHERE r.rental_date > '2022-08-22'
ORDER BY c.customer_id
LIMIT 5;

The rule that resolves this every time: conditions in ON decide what counts as a match (and don't remove already-preserved outer rows); conditions in WHERE filter the joined result afterward, and can silently eliminate the very NULL-filled rows the outer join was meant to preserve. If you want "every customer, but only recent rentals among their matches," it belongs in ON. If you want "only customers with a recent rental" (functionally back to an inner join), WHERE is correct — but at that point you should probably just write INNER JOIN and mean it, rather than accidentally arriving there via a LEFT JOIN.

Check yourself

  1. Write a query, using LEFT JOIN, that finds every film in film that has never been rented (hint: join through inventory and rental, and use the IS NULL anti-join pattern).
  2. Why does RIGHT JOIN rarely appear in real code, given what a LEFT JOIN can already express?
  3. You have a LEFT JOIN meant to preserve every row on the left, but a colleague added a filter on the right-hand table's column in WHERE. What's likely to go wrong, and how do you fix it?
← Previous Next →