Anti-join with LEFT JOIN and IS NULL

An anti-join returns rows from one table that have no matching row in another - it finds the gaps rather than the connections.

About 5–10 minutes · no sign-up needed

Example

Finding rows that don't match

SELECT customers.* FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL

Result:

id	name
C002	Jordan Lee
C004	Tomas Klein

After SELECT, list the column names you want, separated by commas.
```
SELECT customers.*
```
FROM tells SQL which table to pull the data from.
```
SELECT customers.*
FROM customers
```

LEFT JOIN keeps every row from the left table, even when no match exists.

SELECT customers.*
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id

Try each one in your head first, then open the answer.

1. Find customers who have never placed an order, using LEFT JOIN with an IS NULL filter.

SELECT customers.* FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.customer_id IS NULL

2. Find every row in orders that has NO matching row in shipments, using LEFT JOIN with an IS NULL filter.

SELECT orders.* FROM orders LEFT JOIN shipments ON orders.order_id = shipments.order_id WHERE shipments.order_id IS NULL

3. Find every row in orders that has NO matching row in returns, using LEFT JOIN with an IS NULL filter.

SELECT orders.* FROM orders LEFT JOIN returns ON orders.order_id = returns.order_id WHERE returns.order_id IS NULL

4. Find every row in products that has NO matching row in reviews, using LEFT JOIN with an IS NULL filter.

SELECT products.* FROM products LEFT JOIN reviews ON products.product_id = reviews.product_id WHERE reviews.product_id IS NULL

5. Find every row in products that has NO matching row in inventory, using LEFT JOIN with an IS NULL filter.

SELECT products.* FROM products LEFT JOIN inventory ON products.product_id = inventory.product_id WHERE inventory.product_id IS NULL

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed