Inner vs outer joins

INNER keeps matches; OUTER keeps the unmatched too

From the last lesson: a join is a filtered product, and the question is what to do with rows that find no partner. That choice is the inner/outer distinction.

• INNER JOIN — keep only the surviving pairs. A user with no orders simply vanishes from the result.
• LEFT OUTER JOIN — keep every left row no matter what. If a left row finds a match, it’s paired normally; if it finds none, it is still emitted once, with every right-side column set to NULL. This is NULL-extension.
• RIGHT OUTER JOIN — the mirror image: keep every right row, NULL-extend the unmatched left. (A RIGHT JOIN B ≡ B LEFT JOIN A; most teams normalize to LEFT for readability.)
• FULL OUTER JOIN — keep everything: matched pairs, unmatched left rows (right NULL-extended), and unmatched right rows (left NULL-extended).

-- Every user, plus their orders if any; users with none get NULL order columns
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

A user who never ordered appears once here with order_id and total as NULL. That row is the entire reason to use an outer join — it carries the information “this left row had no partner.”

The trap: WHERE on the outer table demotes the join

Here is the bug from the hook, distilled. The placement of a predicate decides whether it filters the product (in ON) or the result (in WHERE) — and for an outer join those are not the same.

-- WRONG: this is secretly an INNER JOIN
SELECT u.id, u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'shipped';   -- runs AFTER NULL-extension

For a user with no orders, o.status is NULL. The comparison NULL = 'shipped' is UNKNOWN, which WHERE treats as false, so that NULL-extended row is discarded. Every unmatched left row is thrown away — the LEFT JOIN now behaves exactly like an INNER JOIN. The fix is to move the condition into ON, so it filters which orders count as a match before NULL-extension:

-- RIGHT: filter the match in ON, keep the outer semantics
SELECT u.id, u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'shipped';

Now users with no shipped order are still emitted, NULL-extended. The rule: a predicate on the outer (right) table belongs in ON, not WHERE. A WHERE on a NULLable outer column is almost always a demoted outer join. The one legitimate exception is WHERE o.id IS NULL, which deliberately keeps only the unmatched rows — that’s the anti-join pattern in the next lesson.

Reading and counting outer results

Because unmatched rows carry NULLs, you usually wrap outer columns in COALESCE for display or aggregation:

SELECT u.id,
       COUNT(o.id)                  AS order_count,   -- COUNT ignores NULLs → 0 for non-orderers
       COALESCE(SUM(o.total), 0)    AS lifetime_value -- SUM of no rows is NULL, not 0
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Two senior details hide here. COUNT(o.id) counts non-NULL values, so a user with no orders correctly gets 0 — but COUNT(*) would count the NULL-extended row as 1, a classic off-by-one. And SUM over zero matching rows returns NULL, not 0, so COALESCE(SUM(...), 0) is required for a sane total. To split matched from unmatched, count o.id IS NULL: that’s your “users who never ordered” number.