← All Kits · SQL Kit

The Anti-Join in SQL: NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

Customers who never ordered. Products never sold. Records that failed to migrate. All of those are one question: which rows in this table have no match in that one. This page gives you the three ways to write it, the one you should default to, and the reason the other two go wrong. On the eight customers below, two of the three return 3 rows and one returns nothing at all.

What you do: write it with NOT EXISTS. It is the only version that behaves the same whether or not the other table contains a NULL, and on modern engines it is as fast as the alternatives. The rest of this page shows what the other two do, because you will meet them in code somebody else wrote.

The short version. An anti-join keeps the rows that found nothing. NOT EXISTS is the safe way to ask.

The two tables the examples run on

Eight customers and eight orders. Small enough that you can work the answer out yourself before any query runs.

CREATE TABLE customers(customer_id INT, name TEXT, city TEXT);
INSERT INTO customers VALUES
(1,'Alvarez','North'),(2,'Brennan','North'),(3,'Cho','South'),(4,'Diaz','South'),
(5,'Ellis','North'),(6,'Fowler','South'),(7,'Grant','North'),(8,'Hale','South');

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'completed'),(102,1, 80,'completed'),(103,2, 50,'cancelled'),
(104,3,300,'completed'),(105,5, 90,'completed'),(106,5, 40,'cancelled'),
(107,8,200,'completed'),(108,NULL,60,'completed');

Read the order rows and the answer is there. Customers 1, 2, 3, 5 and 8 appear. Customers 4, 6 and 7 do not, which is Diaz, Fowler and Grant. So the right answer to "who never ordered" is three people.

One more thing to notice, because the whole page turns on it: order 108 has a NULL customer. A web form let it through, or an import dropped the key. This is not a contrived row. Lookup tables and foreign key columns in real warehouses hold NULL all the time.

NOT EXISTS, the one to default to

Before the query: how many rows should this return?

Three, as worked out above. NOT EXISTS gets it.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1
                  FROM orders o
                  WHERE o.customer_id = c.customer_id);
-- Diaz, Fowler, Grant

Read it out loud and it says what it means. For each customer, look in orders for a row with this customer's id, and keep the customer if you find none.

The SELECT 1 looks strange the first time. It is there because EXISTS never looks at what the subquery selected. It only asks whether any row came back at all, so the 1 is a placeholder. You can write SELECT * or SELECT o.order_id and get exactly the same result and the same plan.

The condition that links the two tables lives inside the subquery. That is what makes it a correlated subquery: it runs against each customer row rather than once for the whole query. If your key is two columns, both go in there, joined with AND.

NOT IN, and the single NULL that empties it

First: the same question with NOT IN. One order row has a NULL customer. What do you expect to come back?

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Zero rows. Not an error, not a warning, not two rows instead of three. Nothing at all, and the query looks completely reasonable in a code review.

Here is the mechanism. NOT IN asks, for each customer, "is this id different from every value in that list?" One of the values is NULL, and SQL cannot say whether 4 is different from an unknown value. The comparison comes back unknown. Unknown is not true, so the row is not kept. That happens for every customer, so every customer is dropped.

Say why this makes the result empty rather than merely wrong before reading on. The reason is that the unknown comparison happens for all eight customers, not just for the one order with the missing key.

You can patch it by excluding the NULL, and the patch does work:

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id
                          FROM orders
                          WHERE customer_id IS NOT NULL);
-- Diaz, Fowler, Grant

It works today, and it goes on working until the day somebody writes the same query without the guard. That is the argument for NOT EXISTS as a habit rather than as a rescue. It needs no guard, so there is nothing for the next person to forget.

LEFT JOIN with IS NULL, the older way

Before the query: a LEFT JOIN keeps every customer and fills the order columns with NULL when there is no match. So which customers have NULL in o.order_id afterwards?

The three who never ordered. That is the whole trick, and it gives the right answer here.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- Diaz, Fowler, Grant

Two rules keep this version honest.

Test a column that cannot be NULL in the real data. Test the joined key or the other table's primary key. If you test o.amount and some orders genuinely have no amount recorded, those customers join fine and still come back as unmatched. The query says "never ordered" and means "never ordered, or ordered with a blank amount".

Any condition on the right-hand table belongs in ON, not in WHERE. This is the one that costs people an afternoon. Ask for customers with no completed order and the difference shows up:

-- correct: the filter is part of what counts as a match
SELECT c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'completed'
WHERE o.order_id IS NULL;
-- Brennan, Diaz, Fowler, Grant   (4 rows)

-- broken: the filter runs after the join has already filled in NULLs
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed' AND o.order_id IS NULL;
-- 0 rows

The broken one returns nothing for a reason worth keeping. After the join, an unmatched customer has NULL in every order column, so o.status = 'completed' is unknown for exactly the rows you were trying to keep. The WHERE throws them out before IS NULL ever gets a look.

Brennan is the interesting row in the correct version. Brennan has an order, but it was cancelled, so Brennan belongs in a list of customers who have never completed a purchase. That is usually the list the business actually wanted.

The same question in NOT EXISTS needs no placement rule, because there is only one place the condition can go:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id
                    AND o.status = 'completed');
-- Brennan, Diaz, Fowler, Grant

Picture your own two tables for a moment. If you joined them and counted the rows, would the count go up? If it would, the LEFT JOIN version is doing extra work it then has to undo, which is the next section.

The three side by side

Same table, same question, three answers. The middle row is not a small difference. It is a report that comes back empty and gets read as "nobody is missing", which is the most convincing wrong answer a query can give.

Edge cases worth knowing

Speed is not the reason to choose between them. PostgreSQL and SQL Server both recognise NOT EXISTS and the LEFT JOIN pattern and run them as the same anti-join operation, so on an indexed key the plans usually match. NOT IN is the one an engine often cannot turn into an anti-join, precisely because of the NULL rule it has to honour. Correctness and speed point the same way here, which is a rare and pleasant thing.

An index on the joined column matters more than the syntax. Whichever form you pick, the anti-join reads the second table once per candidate row, so an index on orders.customer_id is the difference between instant and slow on a big table. There is more in indexing for analysts.

The LEFT JOIN version builds rows to throw away. A customer with 400 orders produces 400 joined rows, all discarded except the check. NOT EXISTS can stop at the first match it finds. On small tables nobody notices, and on a wide fact table it is worth knowing which one is doing more work.

Keys with more than one column work the same. Put every part in the subquery, joined with AND. NOT IN gets harder here, which is another reason it is not the default.

EXCEPT answers a slightly different question. SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders also finds the missing ids, and it removes duplicates while it does so. It is neat when you want only the key column. It is awkward when you want the rest of the customer row, because the two sides must have matching columns. Oracle calls the same operator MINUS.

An empty second table is not an edge case. If orders holds no rows, an anti-join correctly returns every customer. The one that surprises people is the reverse: a NOT IN subquery returning no rows keeps everything, which is right, while the same subquery returning one NULL keeps nothing.

Why the NULL rule exists

None of this is a bug or an engine quirk. SQL has three truth values rather than two: true, false, and unknown. Any comparison with NULL produces unknown, because NULL means the value is not recorded, and nothing can be proved equal or unequal to a value nobody has.

WHERE keeps a row only when its condition is true. Unknown is not true, so unknown rows are dropped, and they are dropped just as silently as false ones. That single rule explains everything on this page: the empty NOT IN, the broken WHERE o.status = 'completed', and the reason IS NULL exists as its own operator rather than as = NULL.

NOT EXISTS sidesteps the whole thing by never comparing values at the top level. It asks whether a set is empty, and a set is either empty or it is not. There is no third option to leak.

How to apply this to your own work

1. Search your queries for NOT IN (SELECT. Every hit is either already guarded with IS NOT NULL or is one bad import away from returning nothing.
2. Rewrite those as NOT EXISTS. It is a mechanical change and the result is identical on clean data, which makes it easy to verify.
3. For any LEFT JOIN plus IS NULL you keep, check two things: the tested column cannot be NULL in real data, and no condition on the right table sits in WHERE.
4. Sanity check every anti-join with arithmetic. Matched plus unmatched must equal the total. Here that is 5 plus 3 equals 8.
5. When a result comes back empty, do not report it as "none found" until you have run the count of the whole table. Empty is a result that deserves one more query.

If you have paper nearby, draw the eight customers in one column and the orders in another, then draw a line from each order to its customer. The three customers no line reaches are the answer, and the order with no line at either end is the NULL that empties NOT IN.

Cheat sheet

The one habit to keep

When an anti-join returns zero rows, treat it as a question rather than an answer. Count the table, count the matches, and check they add up. An empty result is the one output that looks the same whether you asked correctly or not.

What is the last "nobody is missing" result you reported, and was it written with NOT IN?