EXISTS vs IN in SQL: Which to Use and When

Both answer the question “does a matching row exist?”, and most of the time both give the same answer. They are not interchangeable, though, and the difference shows up in exactly two places: what happens when NULLs are involved, and what you can put in the subquery.

This page shows both forms side by side, then the one case where choosing wrong produces a silently empty result.

The same question, two ways

Which customers have had an order shipped? With IN, the subquery returns a list of ids and the outer query checks membership:

SELECT company
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Orders
    WHERE status = 'Shipped'
);

With EXISTS, the subquery is a yes/no test that refers back to the current row:

SELECT company
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'Shipped'
);

Identical results. The difference in shape is that IN compares a value against a list, while EXISTS asks whether a correlated subquery returns any row at all — it stops at the first one it finds and never looks at what that row contains.

The one that actually matters: NOT IN and NULL

The positive forms behave the same. The negative forms do not, and this is the single most useful thing on this page.

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. That last comparison can never be true — nothing is known to be unequal to an unknown value — so the whole AND chain is unknown, and the row is not returned. One NULL anywhere in the list and the query returns nothing at all. No error, no warning, just an empty result.

SELECT company
FROM Customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM Orders
    WHERE status = 'Shipped'
);

That query is safe only because Orders.customer_id happens to have no NULLs. Change the subquery to a nullable column and it becomes a trap.

NOT EXISTS has no such problem, because it never compares anything — it only asks whether a row came back:

SELECT company
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'Shipped'
);

When you cannot use the other one

• IN can take a literal list. WHERE category IN ('Hardware', 'Software') needs no subquery at all, and EXISTS has no equivalent for that.
• EXISTS can match on several columns. A correlated subquery can compare as many columns as it likes. Standard SQL allows row constructors with IN, but support is patchy and the syntax is awkward, so EXISTS is the practical choice.
• IN reads better for a small fixed set. Nobody should write an EXISTS to check three known values.
• EXISTS reads better when the subquery is a condition. If the inner query exists purely to answer yes or no, EXISTS says that out loud.

The literal-list form, which is where most people meet IN first:

SELECT name, category, unit_price
FROM Products
WHERE category IN ('Hardware', 'Software')
ORDER BY unit_price DESC;

And its negation, which is safe here because the list has no NULL in it:

SELECT name, category
FROM Products
WHERE category NOT IN ('Hardware', 'Software');

What about performance?

The honest answer for any database written in the last fifteen years: usually nothing to choose between them. Query planners recognise both shapes and turn them into the same semi-join internally, so the old advice that “EXISTS is faster because it stops early” is mostly folklore now.

What does still matter:

• A subquery returning a very large list can be worse with IN on some databases, because the list may be materialised.
• A correlated EXISTS with no index on the joining column is slow for the ordinary reason — it is doing a lookup per outer row. Index the column and it is fast. See how indexes work.
• Choose for correctness and readability first. If a query is genuinely slow, read the plan rather than guessing at the operator.

Or use a JOIN instead

If you need columns from the other table in your output, neither operator is right — use a join:

SELECT DISTINCT c.company, o.status
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Shipped';

Note the DISTINCT, which is the price of the join: a customer with three shipped orders appears three times. That is the rule of thumb for choosing:

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Customers with a shipped orderMedium
• Customers still waitingMedium
• Put a name to the departmentMedium
• Who is closing the deals?Hard

See all 42 exercises →

Frequently asked questions

Related reading

• SQL subqueries explained: nested SELECT statements
• SQL JOINs explained: INNER, LEFT, RIGHT and FULL
• The WHERE clause and the NULL traps
• UNION vs UNION ALL
• How indexes make a subquery fast