Find Records Missing From Another Table in SQL (LEFT JOIN, NOT EXISTS)
Which customers have never placed an order? Find rows in one table with no match in another, first with LEFT JOIN and IS NULL, then with NOT EXISTS, on a sample online shop database.
Step by step
No match
- Never ordered?. Which customers signed up but never ordered? Marketing wants to win them back. To answer it, you look for rows in one table that have no match in another. Let's find them two ways.
- Two tables. Customers are in one table, and orders are in another. Each order holds a customer ID.
LEFT JOIN
- LEFT JOIN. Way one is a left join. It keeps every customer, even the ones with no orders. Show the customer, and the order ID next to it. A hundred and forty seven rows. Customers with many orders show up many times. A customer with no orders shows up once, with an empty order ID.
SELECT c.customer_id, c.first_name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id;
IS NULL
- Keep the empties. So keep only the rows where the order ID is null. Add where o dot order ID is null. Seven customers have never ordered. That's your win back list.
WHERE o.order_id IS NULL;
NOT EXISTS
- NOT EXISTS. Way two reads more like the question. Select customers where not exists, then a small query that looks for an order with the same customer ID. The same seven customers. Select one just means we only care if a row exists, not what's in it.
SELECT c.customer_id, c.first_name FROM customers AS c WHERE NOT EXISTS ( SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);
Other tables
- Any two tables. The same pattern works for any two tables. Which employees have never handled an order? Swap customers for employees, and match on the employee ID. Two people. The head of sales and the customer service lead. That makes sense, they don't take orders themselves.
SELECT e.employee_id, e.full_name, e.job_title FROM employees AS e WHERE NOT EXISTS ( SELECT 1 FROM orders AS o WHERE o.employee_id = e.employee_id);
Recap
- Find what's missing. So, to find rows with no match, left join and keep the rows where the other side is null. Or use not exists, which reads like the question. Both give the same answer.
