LEFT JOIN: keeping unmatched rows

Keep every row on the left, matched or not.

Keep the left side

An INNER JOIN drops rows with no match. A LEFT JOIN keeps every row from the left table; where there's no match on the right, the right columns come back NULL.

That makes it the tool for "find the things with nothing attached". Customers who have never ordered:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The WHERE ... IS NULL keeps only the customers whose left join found no order.

Northern Light has a review; Rivers of Salt has none. An inner join answers "which books have a review?"; a left join answers "every book, and its review if there is one". The NULL is the evidence that nothing matched.

The filter tests a right-hand column that can only be NULL when the join found nothing — which is what turns a left join into an answer to "which X has no Y?".