SQL · Chapter 17 of 42

LEFT JOIN

LEFT JOIN returns ALL rows from the left table, plus matching rows from the right. Where no match exists, right-side columns are NULL.

Use it to answer 'which of X have no Y' with `WHERE right.id IS NULL`.

Anti-join pattern

LEFT JOIN + WHERE right.pk IS NULL finds rows on the left with no match — perfect for 'users who never ordered'.

Filtering right-side columns in WHERE turns a LEFT JOIN back into an INNER JOIN. Put right-side filters in the ON clause instead.

Example 1 (sql)

SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Output

every user; order id or NULL

Keeps users without orders.

Example 2 (sql)

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Output

users with zero orders

Anti-join.

💡 Note: LEFT OUTER JOIN and LEFT JOIN are the same thing — the OUTER keyword is optional.