SQL · Queries that survive scale

When should you use EXISTS instead of IN?

MediumexistsinnullsUpdated Oct 2026

Short answer

Use EXISTS for existence checks because it short-circuits and handles NULLs safely. IN is fine for a small, non-NULL list, but NOT IN with NULLs can return no rows unexpectedly.

Both can express a semi-join, but their semantics differ around NULLs. EXISTS asks whether the subquery returns at least one row. IN asks whether a value equals any value in a list, and NULL comparisons evaluate to unknown rather than true.

sql

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'paid'
);

The database can stop scanning orders as soon as one paid order is found for a customer.

The NOT IN trap

sql

SELECT id
FROM users
WHERE id NOT IN (
  SELECT user_id FROM bans
);

If bans.user_id contains a single NULL, this query returns no rows
NOT EXISTS is NULL-safe because it checks for absence of a matching row
IN with a small literal list is simple and readable
EXISTS can short-circuit and often maps to an anti-join or semi-join
Some optimizers rewrite IN and EXISTS similarly when NULLs are not involved

For positive existence checks, both forms are usually acceptable. For negative checks, prefer NOT EXISTS or add IS NOT NULL inside the subquery. The safest choice is the one whose semantics match the question you are asking.

Do not use IN with a subquery that can return NULL unless you have handled the three-valued logic explicitly. This is a common source of silently empty results.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

1Why can NOT IN return no rows when the subquery contains NULL?
2How does EXISTS short-circuit?
3Does the optimizer treat IN and EXISTS the same way?
4When is IN with a literal list the better choice?

Say it in the interview

EXISTS checks whether a matching row exists, and it handles NULLs safely. IN compares against a list, and if that list contains NULL, NOT IN can return no rows because the comparison is unknown. I use EXISTS or NOT EXISTS for subquery existence checks, especially negative ones. IN is fine for a short literal list where I control the values.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interview Browse all questions

The NOT IN trap

Follow-ups you should expect

Related questions

Now say it out loud.