SQL IN vs EXISTS: The Difference, the NULL Trap, and When to Use Each

IN and EXISTS often answer the same question — but they work differently under the hood, and one specific case (NOT IN with a NULL in the list) can silently return the wrong answer. Here's how each one actually behaves.

What each one checks

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row at all — it doesn't care what the row contains, only that it exists.

Both are commonly used to answer the same question — "does a matching row exist elsewhere?" — but they're evaluated differently, and that difference matters for both correctness and performance.

IN — membership in a list or subquery

Find employees in a department with an average salary above the company average.

SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);

EXISTS — does a matching row exist

The same idea rewritten with a correlated EXISTS — it re-runs per outer row and stops at the first match.

SELECT first_name, last_name, department_id
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM employees e2
  GROUP BY e2.department_id
  HAVING e2.department_id = e.department_id
     AND AVG(e2.salary) > (SELECT AVG(salary) FROM employees)
);

The NOT IN / NULL trap

This is the single most important reason to know the difference. If the subquery behind a NOT IN can return even one NULL, the entire outer query returns zero rows — silently, with no error.

That's because x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL, and x != NULL is always UNKNOWN, not TRUE — so the whole AND chain can never evaluate to TRUE.

NOT EXISTS doesn't have this problem, because it checks for row existence, not value equality — a NULL in the subquery's selected column is irrelevant.

NOT IN silently returning nothing

commission_pct is NULL for many employees in the HR schema — this makes the NOT IN version below a live example of the trap.

-- Dangerous: if any manager_id is NULL, this returns ZERO rows, not "everyone who isn't a manager"
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

-- Safe: NOT EXISTS is unaffected by NULLs in the subquery
SELECT employee_id, last_name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id
);

IN vs EXISTS at a glance

Does one perform better?

On modern optimizers (Oracle, PostgreSQL, SQL Server), the query planner frequently rewrites IN and EXISTS into the same execution plan when they're logically equivalent, so raw performance differences are often small in practice.

The practical guidance that still holds: prefer EXISTS (or NOT EXISTS) for correlated existence checks against large tables, and reserve IN for short, static, or non-correlated lists. But always check an actual execution plan on your data rather than assuming — see the EXPLAIN PLAN topic.