SQL LEFT JOIN: Find Customers With No Orders
A LEFT JOIN keeps every row from the first table, even when nothing matches. Use it with WHERE ... IS NULL to find customers who signed up but never placed an order.
Step by step
The question
- Who never ordered?. Marketing asks a classic question. Which customers signed up, but never placed an order? Inner join can't answer it, because it drops rows with no match. Left join can.
- Start from customers. We start from customers, because we want every customer in the answer, whether they ordered or not.
Write the left join
- Customers first. Select the customer ID, the first name and the order ID, from customers as c. The table you need all of goes first. That's the left side.
SELECT c.customer_id, c.first_name, o.order_id FROM customers AS c - LEFT JOIN. Then left join orders as o, on the customer ID. I'll sort by order ID, so customers without an order come to the top.
LEFT JOIN orders AS o ON o.customer_id = c.customer_id ORDER BY o.order_id; - Every customer kept. Run it. A row for every order, plus a row for each customer who never ordered.
- Null means no order. Look at the top row. This customer's order ID is null. Null means no value. Left join kept the customer, even though nothing matched in orders.
Keep only the gaps
- IS NULL. Now keep only those. Replace the sort with where order ID is null. Always write is null. Equals null never finds anything. Seven customers who never ordered. That's the list marketing wanted.
WHERE o.order_id IS NULL; - The list for marketing. Seven names for a welcome offer. Inner join could never find them, because they have nothing to match.
Recap
- The LEFT JOIN rule. So, left join keeps every row from the first table. Where nothing matches, the other columns are null. Add where is null to find what's missing.
