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 wants to know which customers signed up but never placed an order. An INNER JOIN can't answer this, because it drops rows that have no match. A LEFT JOIN can.
- Start from customers. Start from the customers table, because every customer must be in the answer, whether they ordered or not.
Write the left join
- Customers first. Type SELECT c.customer_id, c.first_name, o.order_id FROM customers AS c. The table you need all of goes first, after FROM. That is the left side.
SELECT c.customer_id, c.first_name, o.order_id FROM customers AS c - LEFT JOIN. Add LEFT JOIN orders AS o ON o.customer_id = c.customer_id, then ORDER BY o.order_id; so customers without an order sort to the top. Some databases, such as PostgreSQL, put NULLs last instead.
LEFT JOIN orders AS o ON o.customer_id = c.customer_id ORDER BY o.order_id; - Every customer kept. Run it. You get 147 rows: 1 for every order, plus 1 for each customer who never ordered.
- Null means no order. In the top row, order_id is NULL. NULL means no value. The LEFT JOIN kept this customer even though nothing matched in orders.
Keep only the gaps
- IS NULL. Now keep only those rows. Replace ORDER BY o.order_id; with WHERE o.order_id IS NULL;. Write IS NULL, not = NULL, because = NULL is never true and returns no rows. Run it again. 7 rows come back: the 7 customers who never ordered.
WHERE o.order_id IS NULL; - The list for marketing. The first_name column now holds 7 names marketing can send a welcome offer. An INNER JOIN could never find them, because they have nothing to match in orders.
Recap
- The LEFT JOIN rule. LEFT JOIN keeps every row from the first table. Where nothing matches, the other table's columns are NULL. Add WHERE ... IS NULL to find what's missing.
Common mistakes
- Writing = NULL instead of IS NULL. WHERE o.order_id = NULL is never true, so the query returns no rows. Always write WHERE o.order_id IS NULL.
- Filtering the orders table in WHERE. A normal WHERE condition on an orders column, such as o.status = 'shipped', is never true for the NULL rows, so it throws them away and the query acts like an INNER JOIN. Put that kind of filter in the ON clause instead.
- Checking IS NULL on a column that can be empty. If you test a column like a ship date that is blank for some real orders, customers who did order slip into the list. Test a column that is never NULL in orders, such as o.order_id.
- Counting rows as customers. A customer with 3 orders gets 3 rows, so the 147 rows are not 147 customers. Use COUNT(DISTINCT c.customer_id) to count customers.
When a business analyst uses this
Before sign-off, check that every requirement has a test case: SELECT r.req_id FROM requirements AS r LEFT JOIN test_cases AS t ON t.req_id = r.req_id WHERE t.test_id IS NULL; The rows that come back are the untested requirements to chase.
Questions people ask
What is the difference between LEFT JOIN and INNER JOIN?
An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row from the first table, with NULL in the other table's columns where there is no match.
Is LEFT JOIN the same as LEFT OUTER JOIN?
Yes. The word OUTER is optional, so LEFT JOIN and LEFT OUTER JOIN give the same result.
Why is my LEFT JOIN adding rows?
A LEFT JOIN returns 1 row for each match, so a customer with 3 orders appears 3 times. Without a WHERE filter, it never returns fewer rows than the first table has.
