EXISTS vs IN: Which Is Faster?

For the common case — a subquery that does not reference the outer row — the two produce identical plans and identical runtimes. The optimizer rewrites an IN subquery into a semi-join, and a semi-join is exactly what EXISTS expresses. MySQL calls it a semijoin optimisation and enables it by default; PostgreSQL rewrites IN into a hashed semi-join when it can, and uses a nested-loop semi-join with an index otherwise; SQLite transforms IN into a membership loop over a subquery.

The rewrite has an important consequence. When the optimizer converts your IN into a semi-join, it stops deduplicating the subquery's rows — because a semi-join only needs to know whether a match exists, not how many. This is why IN on a large subquery can be fast: it stops at the first match per outer row rather than materialising and deduplicating the whole set.

Where the two genuinely differ is when the subquery is correlated. A correlated EXISTS has to run per outer row, and so does a correlated IN, but the EXISTS version can stop at the first matching row inside the inner query while the IN version has to evaluate the inner expression for every candidate. On large tables that difference is real.

There is also the NULL asymmetry to remember. NOT IN with a subquery that can return NULL returns no rows at all, because every NOT UNKNOWN is still UNKNOWN. NOT EXISTS has no such failure mode. So the practical recommendation is to use EXISTS when the subquery might contain NULLs, and IN when you are matching a known, small, non-null list — for which IN is clearer.

EXPLAIN QUERY PLAN shows how SQLite executes a semi-join, and the output makes the strategy visible.

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);

CREATE INDEX idx_orders_customer ON orders (customer_id);

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

The plan shows the customers table being scanned once and the orders table being searched with the index on customer_id, driven as a correlated subquery. That is the shape you want: one pass over customers, and for each one an index lookup that stops as soon as it finds a matching order.

Two features of that plan are worth naming. The subquery is not materialised — it appears as a correlated loop, which is exactly the behaviour EXISTS is designed for. And the index lookup means the cost per customer is logarithmic in the number of orders rather than linear, so the total work is proportional to the number of customers rather than to customers multiplied by orders.

Compare it with the plan for the same query written as a subquery in the FROM clause, WHERE c.id IN (SELECT customer_id FROM orders). SQLite materialises that subquery once into an ephemeral index and then probes it, which is also efficient but has a fixed setup cost. On a small table the difference is irrelevant; on a large one, the correlated version can avoid materialising a very large set when only a few matches exist.

The general lesson for reading plans: look for the node that decides whether the semi-join materialises or loops, and check that the correlated side has an index. A correlated subquery over an unindexed column is the plan that turns a fast query into a slow one, and it is invisible in the query text.

Yes, and this is where the difference between the two becomes real rather than theoretical.

An uncorrelated IN subquery has to be evaluated once, and its result set has to be held somewhere so the outer query can test membership. If the subquery returns a million ids, the engine either materialises them into a hash table or a sorted structure, or it converts the whole thing into a semi-join and probes the underlying table with an index instead. Whether the rewrite happens depends on the engine and on the subquery's shape.

EXISTS avoids the materialisation problem in the correlated case, because it never needs to hold the whole set — it just needs to know whether one match exists, and it stops looking the moment it finds one. For a query like "find the customers who have placed more than 1000 orders", EXISTS with a LIMIT 1 inside is dramatically better than a version that gathers every matching order id first, because the inner query stops after the first row.

The trap is the opposite shape. An EXISTS whose inner query has to scan the whole table to find a match is slower than an IN that materialises a small set once. If the subquery has no index on the correlated column, each of the outer row's executions is a full scan, and the total cost is quadratic in the worst case.

So the rule of thumb is not "EXISTS is faster". It is: EXISTS is better when the inner query can stop early and the correlation is indexed; IN is better when the value set is small and known, or when the list genuinely needs to be materialised. And for a large uncorrelated subquery, the best answer is often neither — rewrite it as a join with a DISTINCT, which gives the optimizer a shape it can index into.