SQL EXISTS vs IN: Differences, the NOT IN NULL Trap and Performance

A report lists customers who have never placed an order. It works for months. Then a guest checkout feature starts saving orders with no customer ID, and the report suddenly shows nobody. No error, no warning, just an empty result. The cause is one of SQL’s best-known traps: NOT IN meeting a NULL.

We ran every query below in SQLite 3.50, and the results are from that run. The NULL behaviour comes from the SQL standard, so PostgreSQL, MySQL, SQL Server and Oracle behave the same way.

The short answer

The sample tables

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INT, total INT);

INSERT INTO customers VALUES (1,'Asha'), (2,'Ben'), (3,'Chitra'), (4,'Dev');
INSERT INTO orders VALUES (101,1,500), (102,1,200), (103,3,900);

Asha has two orders, Chitra has one, and Ben and Dev have none.

How IN works

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- Asha, Chitra

The subquery produces a list of customer IDs, here 1, 1 and 3, and each customer is kept if its ID appears in that list. IN also works with a literal list, as in WHERE status IN ('paid', 'shipped'), which is often clearer than a chain of OR conditions. Our SQL subqueries guide covers this style in more depth.

How EXISTS works

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- Asha, Chitra

For each customer, the subquery looks for at least one matching order. EXISTS only cares whether a row exists, not what it contains, which is why people write SELECT 1 inside. The database can stop at the first match. The subquery refers to c.id from the outer query, which makes it a correlated subquery.

Why not just use a JOIN?

SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id;
-- Asha, Asha, Chitra

A join returns one row per match, so Asha appeared twice because of the two orders on that account. You could add DISTINCT, but that hides intent and costs extra work. When the question is “which customers have at least one order?”, IN and EXISTS express it directly and never duplicate rows. Use a join when you need columns from both tables.

The NOT IN NULL trap

Before any NULLs, NOT IN and NOT EXISTS agree:

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- Ben, Dev

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Ben, Dev

Now a guest order arrives with no customer:

INSERT INTO orders VALUES (104, NULL, 50);

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- (no rows)

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Ben, Dev

One NULL in a table of orders made NOT IN return nothing at all, while NOT EXISTS still gave the right answer. The query did not fail, so nothing alerted anyone. That is what makes this bug dangerous.

Why NULL breaks NOT IN

SQL uses three-valued logic: true, false and unknown. Comparing anything with NULL gives unknown. id NOT IN (1, 3, NULL) means “id <> 1 AND id <> 3 AND id <> NULL”. For Ben, with ID 2, the first two parts are true, but the last is unknown, so the whole condition is unknown, and WHERE only keeps rows where the condition is true. We checked the pieces directly:

SELECT 2 NOT IN (1, NULL);   -- NULL (unknown)
SELECT 1 IN (1, NULL);       -- 1 (true)
SELECT 2 IN (1, NULL);       -- NULL (unknown)

Positive IN still works when the value is found, which is why the problem only bites with NOT IN. EXISTS never returns unknown: a row either exists or it does not. Our guide to SQL NULL values explains three-valued logic in more detail.

Three safe alternatives

-- 1. NOT EXISTS (recommended)
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 2. Filter NULLs out of the NOT IN subquery
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- 3. LEFT JOIN anti-join
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

All three returned Ben and Dev with the guest order present. NOT EXISTS is the most robust, because it stays correct even if someone later removes the IS NOT NULL filter or the column becomes nullable. In the LEFT JOIN version, check a column that can never be NULL in a real match, such as the primary key o.id.

Correlated conditions

EXISTS makes it easy to add conditions about the related rows:

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.total > 400
);
-- Asha, Chitra

“Customers with at least one order over 400” reads naturally. With IN you would move the condition inside the subquery too, which works, but EXISTS scales better as conditions involve more columns from the outer table, such as matching on both customer and region.

Performance and query plans

An old rule of thumb said “EXISTS is faster than IN” or the reverse. Modern optimizers in PostgreSQL, SQL Server, Oracle and MySQL 8 usually rewrite both into the same internal operation, a semi-join for IN and EXISTS and an anti-join for NOT EXISTS, so performance is often identical. SQLite, which is simpler, showed different plans for our two queries:

IN:     SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)
        LIST SUBQUERY 1
        SCAN orders
        CREATE BLOOM FILTER

EXISTS: SCAN c
        CORRELATED SCALAR SUBQUERY 1
        SCAN o

For IN, SQLite built the list of IDs once, then looked up customers by primary key. For EXISTS, it scanned customers and ran the subquery for each one. With an index on orders.customer_id, each of those lookups is fast. On tiny tables the difference is irrelevant; on large ones, read the plan for your own database and data, as described in our query optimization guide. NOT IN with nullable columns can also prevent some optimizers from using an efficient anti-join, which is another reason to prefer NOT EXISTS.

Which to use

Testing tip

Bugs like the NOT IN trap survive because test data is too clean. When testing reports and filters, always include rows with NULLs in nullable columns, rows with several matches and rows with no matches. Those three cases separate correct queries from ones that only work on tidy data. Our SQL questions for testers includes more checks like this.

Final thoughts

IN and EXISTS are interchangeable for positive checks in most databases, so choose whichever reads better. The negated forms are different: NOT IN can silently return nothing when a NULL appears, while NOT EXISTS stays correct. Make NOT EXISTS your default for “rows without a match”, and keep NULLs in your test data.

Frequently asked questions

Keep learning