Customers with no orders (LEFT JOIN)
Find the rows of one table that have no match in another table.
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;How it works
A LEFT JOIN keeps every customer, and fills the order columns with NULL when there is no matching order. Filtering for o.order_id IS NULL keeps exactly those customers.
This pattern is often called an anti join. NOT EXISTS gives the same result and is sometimes clearer.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
customers
customer_id | customer_name | region
1 | Ava | North
2 | Ben | South
3 | Cara | North
4 | Dan | East
5 | Ava | North
(orders as in the first snippet)Expected result
customer_id | customer_name
4 | Dan
5 | AvaCommon mistakes and limitations
- Putting the filter on the right-hand table in WHERE (for example WHERE o.status = 'paid') turns the LEFT JOIN into an inner join. Put such conditions in the ON clause instead. - Testing a column that can be NULL for other reasons. Test the join key or the primary key of the right-hand table.