SQL Anti Join: NOT EXISTS vs NOT IN vs LEFT JOIN
· 7 min read
An anti join returns the rows of one table that have no matching row in another table: customers with no orders, products with no sales, orders with no shipment. SQL has no ANTI JOIN keyword in the standard, so you write it one of three ways.
SELECT p.product_name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM sales AS s
WHERE s.product_id = p.product_id
AND s.sale_date >= DATE '2025-05-01'
)
ORDER BY p.product_id;NOT EXISTS is the safest default. LEFT JOIN ... IS NULL gives the same result and is just as common. NOT IN looks the simplest but returns nothing at all if the list contains a NULL. This guide runs all three on the same tables so you can see where they agree and where one breaks.
The example tables
| product_id | product_name |
|---|---|
| 1 | Desk lamp |
| 2 | Notebook |
| 3 | Stapler |
| 4 | Whiteboard |
| sale_id | product_id | sale_date |
|---|---|---|
| 1 | 1 | 2025-05-02 |
| 2 | 1 | 2025-05-03 |
| 3 | 2 | 2025-05-03 |
| 4 | NULL | 2025-05-04 |
| 5 | 4 | 2025-04-28 |
The question for every query below: which products had no sales in May 2025? Stapler never sold. Whiteboard sold once, in April. Sale 4 has no product_id, which happens in real data more often than you would hope (a manual entry, a deleted product, a failed import).
The right answer is Stapler and Whiteboard.
Method 1: NOT EXISTS
The query at the top returns:
| product_name |
|---|
| Stapler |
| Whiteboard |
Read it as "keep each product for which no May sale exists". For each product row, the database checks the subquery. If it finds even one matching row, the product is excluded. The SELECT 1 inside is a convention: EXISTS only cares whether a row exists, not what it contains.
Two things make NOT EXISTS the safe default:
- NULLs don't break it. Sale 4's
NULLproduct never equals anyproduct_id, so it simply matches nothing. - Extra conditions are easy. The date filter sits next to the join condition, where it clearly belongs.
The subquery refers to p.product_id from the outer query, which makes it a correlated subquery. More on those in SQL subqueries.
Method 2: LEFT JOIN ... IS NULL
A left join keeps every product and fills in NULL where there is no match. Keep only those rows and you have an anti join:
SELECT p.product_name
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
AND s.sale_date >= DATE '2025-05-01'
WHERE s.sale_id IS NULL
ORDER BY p.product_id;| product_name |
|---|
| Stapler |
| Whiteboard |
Same result. Two details make it work:
- The date filter is in
ON, notWHERE. It limits which sales are allowed to match. Whiteboard's April sale fails theONcondition, so Whiteboard ends up withNULLs and passes theIS NULLtest. IS NULLtests the right table's key (s.sale_id), a column that is neverNULLin a real match. Testing a column that can be empty in real rows lets false matches through.
What goes wrong when the filter moves to WHERE
Put the date condition in WHERE and the query answers a different question, or none at all:
SELECT p.product_name
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
WHERE s.sale_id IS NULL
AND s.sale_date >= DATE '2025-05-01';| product_name |
|---|
No rows. A row with s.sale_id IS NULL is an unmatched row, so its sale_date is NULL too, and NULL >= '2025-05-01' is never true. The two conditions can never hold together. Drop the date condition entirely and you get only Stapler, which answers "which products never sold?", a different question. Whiteboard drops out because its April sale counts as a match.
Method 3: NOT IN, and the NULL trap
NOT IN reads the most naturally:
SELECT p.product_name
FROM products AS p
WHERE p.product_id NOT IN (
SELECT s.product_id
FROM sales AS s
WHERE s.sale_date >= DATE '2025-05-01'
)
ORDER BY p.product_id;| product_name |
|---|
It returns nothing. No error, no warning, just an empty result, which an AI assistant (or a tired analyst) might report as "every product sold in May".
The cause is sale 4. The subquery returns the list 1, 1, 2, NULL. For Stapler, SQL has to decide whether 3 NOT IN (1, 1, 2, NULL) is true. That means 3 <> 1 AND 3 <> 1 AND 3 <> 2 AND 3 <> NULL, and 3 <> NULL is unknown, not true. So the whole condition is unknown, and WHERE only keeps rows where it is true. You can see it directly:
SELECT 3 NOT IN (1, 2, NULL) AS result;| result |
|---|
| NULL |
One NULL anywhere in the list removes every row. This is standard SQL behaviour, so DuckDB, Postgres, MySQL and SQL Server all do it.
The fix is to exclude NULLs from the subquery:
SELECT p.product_name
FROM products AS p
WHERE p.product_id NOT IN (
SELECT s.product_id
FROM sales AS s
WHERE s.sale_date >= DATE '2025-05-01'
AND s.product_id IS NOT NULL
)
ORDER BY p.product_id;| product_name |
|---|
| Stapler |
| Whiteboard |
That works, but you have to remember it every time, and the column might have no NULLs today and some next month. That is the main argument for NOT EXISTS. NOT IN with a short literal list, such as status NOT IN ('refunded', 'cancelled'), is fine. The trap is a subquery on a column that can be NULL. For more on NULL logic, see SQL COALESCE and NULL.
A fourth option: EXCEPT
When you only need the key, EXCEPT does the job as a set operation:
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM sales WHERE sale_date >= DATE '2025-05-01'
ORDER BY product_id;| product_id |
|---|
| 3 |
| 4 |
EXCEPT treats two NULLs as equal, so the stray NULL does no harm. The catch is that both sides must have the same columns, so to show names you would join back to products. More in UNION vs UNION ALL, which also covers INTERSECT.
Which one should you use?
| NOT EXISTS | LEFT JOIN ... IS NULL | NOT IN (subquery) | |
|---|---|---|---|
| Correct with NULLs in the other table | yes | yes | no, returns nothing |
| Extra conditions on the other table | inside the subquery | must go in ON | inside the subquery |
| Can also show columns from the other table | no | yes (all NULL) | no |
| Typical speed | good | good | can be slower |
In DuckDB and Postgres, the optimizer usually turns NOT EXISTS and LEFT JOIN ... IS NULL into the same anti join plan, so speed rarely decides it. Pick NOT EXISTS when the query is only about "has no match". Pick the left join when you are already joining that table for other columns.
DuckDB also accepts a literal ANTI JOIN (and SEMI JOIN) keyword: FROM products AS p ANTI JOIN sales AS s ON ... returns the same two products. It is a DuckDB extension, so it will not run in Postgres or MySQL.
The opposite: a semi join
The inverse question, "which products did sell in May?", is a semi join. A plain inner join answers it with duplicates:
SELECT p.product_name
FROM products AS p
JOIN sales AS s ON s.product_id = p.product_id
WHERE s.sale_date >= DATE '2025-05-01'
ORDER BY p.product_id;| product_name |
|---|
| Desk lamp |
| Desk lamp |
| Notebook |
Desk lamp sold twice, so it appears twice. EXISTS returns each product once:
SELECT p.product_name
FROM products AS p
WHERE EXISTS (
SELECT 1 FROM sales AS s
WHERE s.product_id = p.product_id
AND s.sale_date >= DATE '2025-05-01'
)
ORDER BY p.product_id;| product_name |
|---|
| Desk lamp |
| Notebook |
If you later count or sum these rows, the duplicates from the join would inflate the numbers. See how to join two tables for more on why joins multiply rows.
FAQ
What is an anti join in SQL?
A query that keeps the rows of one table with no match in another. It is written with NOT EXISTS, LEFT JOIN ... WHERE right_key IS NULL, NOT IN or EXCEPT.
Why does my NOT IN query return no rows?
The subquery almost certainly returns at least one NULL. Add WHERE column IS NOT NULL to the subquery, or rewrite it with NOT EXISTS.
Is NOT EXISTS faster than LEFT JOIN IS NULL?
In most modern databases they run the same way. Check the plan with EXPLAIN if it matters on a large table.
How do I find records in one table that are not in another?
That is exactly an anti join. Start with NOT EXISTS, put every condition about the second table inside the subquery, and check the row count against what you expect.
Anti joins come up in nearly every SQL interview. You can practise them on one company's orders, shipments and CRM data on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Q4 orders that never shipped
- Bigger accounts that went quiet in Q4
- Bought in Q1, gone by Q4
- Orphan CRM contacts: too many flaggedAudit