Finding Rows Without a Match

"Which customers never ordered?" is one of the most common questions in SQL. The answer is a LEFT JOIN plus IS NULL.

This lesson uses the E-commerce database: customers and their orders.

LEFT JOIN keeps every customer. When someone has no orders, the order columns come back as NULL:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Keep only those NULL rows and you have the customers with no match:

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

This pattern is called an anti-join. NOT EXISTS, which you'll meet in Advanced, does the same job.

Test a column that is never NULL in a real match, like the other table's primary key. Otherwise a real order with a missing value would look like no order at all.