SQL EXISTS vs IN: Differences, NULL Traps & Examples — sqlfmt

sqlfmt▍

SQL EXISTS vs IN: Differences, NULL Traps & Examples — sqlfmt

Key takeaways

EXISTS asks whether a related row exists.
IN tests whether a value belongs to a list or one-column subquery.
Prefer NOT EXISTS when a NOT IN subquery can contain NULL.

What is the semantic difference?

EXISTS evaluates whether a subquery produces any row. IN compares one expression with values from a list or a one-column subquery. EXISTS is often the clearest expression of a correlated relationship.

Customers who have placed an order

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

PostgreSQL explains that EXISTS tests whether a subquery returns at least one row, while IN compares the left expression with a one-column subquery result.

The NOT IN and NULL trap

If a NOT IN subquery returns NULL, a non-matching value can evaluate to UNKNOWN instead of TRUE. WHERE retains only TRUE rows, so expected results may disappear.

A NULL-safe anti-join

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

How should you choose?

Use IN for a clear literal list such as status IN ('new', 'paid').
Use EXISTS when only a related match matters.
Use NOT EXISTS for nullable anti-join keys.

Frequently asked questions

Does SELECT 1 inside EXISTS matter?

No selected value is used; only row presence matters. SELECT 1 is a convention that clearly signals an existence test.

Is EXISTS always faster than IN?

No. Data, indexes, statistics, and the optimizer decide the plan. Start with the clearest semantics, then measure a representative workload.