Skip to main content
Practice

IN vs EXISTS

IN and EXISTS are both used in subqueries to filter rows, but they behave differently and are not always interchangeable.

Understanding how they work helps you write more efficient and accurate SQL.


How are they different?​

At first glance, they seem to do the same thing:

Return rows from one table that match values from another.

But under the hood, they process data differently, which affects performance, accuracy, and scalability.


Core Difference​

  • IN works like a lookup: it collects all subquery results first, then compares.
  • EXISTS checks if any row satisfies a condition, row-by-row — and stops at the first match.

This difference becomes important when:

  • Subqueries are large or correlated
  • NULLs are present in the result
  • You care about query speed

Practical Implication​

While both may give the same result, they have trade-offs:

  • IN can fail unexpectedly with NULLs
  • EXISTS is often faster in correlated subqueries
  • Many SQL engines optimize EXISTS better under load

When to Use What​

Use IN when:

  • The subquery is small and returns a fixed list
  • You want a clean value comparison

Use EXISTS when:

  • You're filtering using another table with many rows
  • The subquery is correlated to the outer query
  • You want to avoid NULL-related issues

Check out the slide deck on the right to see visual comparisons and practical examples of IN vs EXISTS in action.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.