join

LEFT JOIN to Find Missing Relationships

Use LEFT JOIN plus a NULL check to find customers who have never placed an order.

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

Store (Customers/Orders/Products)

Loading database engine...

Step-by-Step Breakdown

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

Anti-join

This "LEFT JOIN … WHERE right IS NULL" idiom is called an anti-join and is usually faster and clearer than the equivalent NOT EXISTS / NOT IN form.

Variations

Find products that have never been ordered:

Store (Customers/Orders/Products)

Loading database engine...

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.