EXISTS vs IN in SQL: When the Difference Actually Matters

The Short Answer

Modern optimizers (PostgreSQL 12+, MySQL 8+, SQL Server 2017+) usually rewrite IN and EXISTS into the same semi-join. So why do teams still argue about it? Two real reasons: the NULL semantics are different, and EXISTS plays better with correlated conditions. Everything else is folklore.

Same Query, Two Forms

Customers who have at least one order:

SELECT * FROM customers c WHERE c.id IN (SELECT o.customer_id FROM orders o);

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Run EXPLAIN on both in a modern database and you will often see the identical plan — a hash semi join. If your plan differs wildly, check version and indexes before blaming the syntax.

The NULL Trap (The Real Difference)

If the subquery can return NULLs, NOT IN silently returns zero rows — a bug that confuses people for years:

-- customers with no orders — returns NOTHING if any order has NULL customer_id SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

Why: NOT IN compares each row against the whole set, and NULL comparisons evaluate to UNKNOWN, never TRUE. One NULL in the list kills the whole result. NOT EXISTS has no such behavior:

-- always correct, NULLs or not SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Rule of thumb: use NOT EXISTS for exclusion queries unless the column is provably NOT NULL. This alone prevents the majority of anti-join bugs.

When EXISTS Is Clearly Better

Correlated logic that IN cannot express: customers with an order in the last 30 days, or total above their own average:

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.created_at > NOW() - INTERVAL '30 days' AND o.total > 100);

EXISTS also short-circuits — it stops scanning the moment one matching row is found. With a good index on (customer_id, created_at), EXISTS on a huge orders table touches only a few index entries per customer.

When IN Is Fine (or Better)

• Small literal lists: status IN ('active', 'trial') — no debate exists here.
• Subquery results that are small and guaranteed non-NULL (e.g., a dimension table primary key).
• Readability: id IN (SELECT id FROM approved) communicates intent instantly.

Performance Reality Check

For the semi-join case, both forms benefit from the same thing: an index on the join column of the subquery table (orders.customer_id). Without it, both are slow and the syntax argument is noise. With it, both are fast. The performance lever is the index, not the keyword.

One case where plans genuinely diverge: older MySQL (5.x) materialized IN subqueries and could lose the short-circuit; EXISTS sometimes won by 10x there. On MySQL 8.1 and PostgreSQL 16 in my testing, the gap vanished.

Conversion Cheat Sheet

Intent	Write this
Rows that have a match	Either — EXISTS if the match condition is complex
Rows with NO match	NOT EXISTS (safe) — NOT IN only if subquery is NOT NULL
Correlated match (dates, thresholds)	EXISTS / NOT EXISTS
Small literal list	IN

Try It Yourself

Use our free SQL generator — type "customers with no orders, ignoring NULL customer ids" and compare the NOT EXISTS query it returns with your NOT IN version.