EXISTS vs IN in SQL

Both test whether a value appears in a set, but they ask the question differently. IN compares a value for equality against every element of a list. EXISTS asks whether the subquery produced at least one row, and the value inside it is only there to create a correlation. In practice the optimizer rewrites one into the other, so the row sets usually match.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada Lovelace'), (2, 'Grace Hopper'), (3, 'Alan Turing');
INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

SELECT id, name FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY id;

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

Both queries return the same two rows: Ada Lovelace and Grace Hopper. Alan Turing has no orders, so he is excluded by both.

Where they genuinely differ is NULL handling. If orders.customer_id contained a NULL, the IN version would compare every customer id against that NULL, get UNKNOWN, and — because the subquery also produced matching rows — still behave as expected. But in the case where the subquery returns only NULLs and no matches, IN returns UNKNOWN for every row and therefore nothing, while EXISTS also returns nothing. The real divergence appears when you negate: NOT IN (subquery) over a subquery that contains a single NULL returns no rows at all, because every NOT UNKNOWN is still UNKNOWN. NOT EXISTS has no such problem, since EXISTS itself is always TRUE or FALSE.

That single asymmetry is why NOT EXISTS is the safe way to write an anti-join, and why NOT IN with a nullable subquery column is a well-known production bug.

This is the anti-join pattern, and the safest spelling is NOT EXISTS. It reads as the business rule states it, and it does not have the NULL trap that afflicts NOT IN.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing'),
  (4, 'Katherine Johnson');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

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

Two rows come back: Alan Turing and Katherine Johnson, the two customers with no orders at all.

Why NOT EXISTS and not NOT IN? Because NOT IN is defined as the negation of IN, and IN can be UNKNOWN. If the orders.customer_id column ever contains a NULL, WHERE c.id NOT IN (SELECT customer_id FROM orders) evaluates to UNKNOWN for every row and the query returns the empty set — every customer silently disappears. NOT EXISTS has no such failure mode, because EXISTS only ever produces TRUE or FALSE.

The LEFT JOIN spelling — LEFT JOIN orders ON ... WHERE orders.id IS NULL — returns the same rows and is sometimes faster on large tables, but it requires the join to be the outermost thing in the FROM clause, and it is easy to break by adding a WHERE predicate on the right-hand table, which converts the outer join back into an inner one.

One more note: SELECT 1 inside the EXISTS is a convention, not a requirement. The engine only cares whether a row comes back, and SELECT 1 makes that intent explicit and avoids dragging unnecessary columns through the plan.