EXISTS vs IN in SQL

IN compares a value against a list of results from a subquery; EXISTS just checks whether the subquery returns any row at all. They often produce the same result, but NOT IN breaks silently when the subquery contains NULLs — NOT EXISTS doesn't.

What Is the Difference Between EXISTS and IN?

IN materializes the subquery's values and tests membership; EXISTS is a correlated check that stops at the first matching row. Both of these return customers who placed at least one order:

-- IN: build the list, test membership
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS: probe per row, stop at first match
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Why Does NOT IN Return Nothing With NULLs?

If the subquery returns even one NULL, NOT IN yields no rows, because x != NULL evaluates to UNKNOWN and the whole predicate can never be true. This is the single most common bug with NOT IN.

-- orders.customer_id contains a NULL:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- returns 0 rows, always

-- Safe version:
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- returns customers with no orders, as expected

NOT EXISTS uses two-valued row matching, so NULLs in the subquery can't poison the result. See NULL handling in SQL for why UNKNOWN behaves this way.

Which Is Faster, EXISTS or IN?

Modern optimizers usually rewrite both into the same semi-join plan, so for clean, non-NULL data performance is often identical. EXISTS tends to win when the subquery table is large (it can short-circuit on the first match), while IN is fine for small literal lists like IN (1, 2, 3).

-- Check what your database actually does:
EXPLAIN
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Look for "semi join" or "hash semi join" in the plan

Common Pitfalls

• NOT IN + nullable column: the classic silent-empty-result bug. Use NOT EXISTS or add WHERE customer_id IS NOT NULL to the subquery.
• SELECT * inside EXISTS: harmless but noisy — the columns are ignored. Convention is SELECT 1.
• Multi-column checks: IN on multiple columns isn't portable; EXISTS with AND conditions works everywhere.
• Huge IN lists: thousands of literals can blow parser limits and plan quality. Load them into a temp table or CTE and join instead.