LEFT JOIN to Find Missing Relationships

Overview

A LEFT JOIN keeps every row from the left table, padding the right side with NULL when there's no match. Filtering on those NULLs (WHERE right.id IS NULL) is the canonical "find things that don't have a related thing" pattern, for example, customers who have placed zero orders.

The Query

Step-by-Step Breakdown

1. FROM customers c LEFT JOIN orders o: every customer is kept, even those with no orders.
2. ON o.customer_id = c.id: match each order back to its customer.
3. WHERE o.id IS NULL: keeps only customers whose left join found no order row, i.e. zero-order customers.

Variations

Find products that have never been ordered:

Common Mistakes

• Filtering the right table in WHERE before the NULL check. Writing WHERE o.status = 'completed' AND o.id IS NULL can never be true.
• Using INNER JOIN. That drops the very unmatched rows you're trying to find.
• NOT IN with NULLs. WHERE c.id NOT IN (SELECT customer_id FROM orders) misbehaves if customer_id is ever NULL; the LEFT JOIN approach is safer.