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 placed an order? Marketing wants a list so they can win them back. To answer this, you look for rows in one table that have no match in another. There are 2 ways to do it.
- Two tables. The customers table has one row per customer. The orders table has one row per order, and each order stores a customer_id that points back to a customer.
LEFT JOIN
- LEFT JOIN. Way 1 is a LEFT JOIN. Select c.customer_id, c.first_name and o.order_id FROM customers AS c, then LEFT JOIN orders AS o ON o.customer_id = c.customer_id. A LEFT JOIN keeps every customer, even the ones with no orders. Run it and you get 147 rows. A customer with many orders shows up once per order. A customer with no orders shows up once, with NULL in 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. Now keep only those empty rows. Replace the final semicolon with WHERE o.order_id IS NULL; so the query keeps only the customers where no order was found. Run it: 7 customers have never placed an order. That is the win-back list for marketing.
WHERE o.order_id IS NULL;
NOT EXISTS
- NOT EXISTS. Way 2 uses NOT EXISTS and reads more like the question. Select c.customer_id and c.first_name FROM customers AS c, WHERE NOT EXISTS a small query that looks for an order with the same customer_id. It returns the same 7 customers. SELECT 1 inside the brackets means we only care whether a matching order 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 2 tables. To find employees who have never handled an order, swap customers for employees AS e and match on o.employee_id = e.employee_id. It returns 2 people: the head of sales and the customer service lead. That makes sense, because 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. To find rows with no match, LEFT JOIN and keep the rows where the other table's key IS NULL. Or use NOT EXISTS, which reads like the question. Here both return the same 7 customers.
Common mistakes
- Using INNER JOIN instead of LEFT JOIN. An INNER JOIN drops customers with no orders, so WHERE o.order_id IS NULL returns nothing. Use LEFT JOIN so those customers stay in the result with NULLs.
- Putting IS NULL in the ON clause. Adding AND o.order_id IS NULL to the ON clause doesn't filter anything, so every customer comes back. Put the IS NULL check in the WHERE clause, after the join.
- Using NOT IN when the other column has NULLs. If the subquery in WHERE customer_id NOT IN (SELECT customer_id FROM orders) returns even one NULL, the query returns no rows at all. NOT EXISTS doesn't have this problem.
- Forgetting to link the subquery in NOT EXISTS. Without WHERE o.customer_id = c.customer_id inside the brackets, the subquery finds an order for every customer, so the result is empty. Always match the subquery back to the outer table.
When a business analyst uses this
Before sign-off, a BA checks that every requirement has at least one test case. A NOT EXISTS query on the requirements table, matched on requirement ID against the test cases table, lists the requirements with no test, which are the gaps to raise in the traceability review.
Questions people ask
How do I find records in one table that are missing from another in SQL?
Use WHERE NOT EXISTS with a subquery that looks for a matching row, or LEFT JOIN the second table and keep the rows where its key IS NULL. Both return the rows with no match.
Should I use LEFT JOIN or NOT EXISTS to find missing rows?
Both return the same rows. NOT EXISTS reads closer to the question and never repeats a row. Which one runs faster depends on the database, so test it on your own data if speed matters.
