EXISTS vs IN — Choosing the Right Subquery Pattern

EXISTS and IN

Both EXISTS and IN let you filter rows based on a related subquery. They often produce the same results, but they differ in how they work — and in some situations one is significantly faster than the other.

IN with a Subquery

IN evaluates the subquery, collects all its values into a list, and checks whether the outer row's column appears in that list. It is intuitive and readable.

-- Students enrolled in course 1
SELECT name FROM students
WHERE id IN (
  SELECT student_id FROM enrolments WHERE course_id = 1
);

EXISTS

EXISTS checks whether a *correlated* subquery returns at least one row. It stops searching as soon as the first match is found, which can make it faster when the inner table is large.

-- Same result with EXISTS
SELECT name FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrolments e
  WHERE e.student_id = s.id AND e.course_id = 1
);

-- Students who have NOT enrolled in anything
SELECT name FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrolments e WHERE e.student_id = s.id
);

Key Differences

• IN can behave unexpectedly when the subquery returns NULL values — the entire IN check becomes NULL (unknown). NOT IN with NULLs silently returns no rows.
• EXISTS short-circuits on the first match; IN builds the full list first.
• For large inner result sets, EXISTS is often faster. For small lists, IN is fine and more readable.
• Modern query optimisers sometimes rewrite one into the other automatically.