NOT IN vs NOT EXISTS

16    Asked by LillianHart in SQL Server , Asked on Jun 25, 2025

This question compares how NOT IN and NOT EXISTS work in SQL queries, especially in terms of performance, handling of NULL values, and best use cases.

Answered by wilson wade

At first glance, NOT IN and NOT EXISTS might seem to do the same thing—they both filter out rows based on some condition in a subquery. But under the hood, they behave quite differently, and choosing the right one can save you from some tricky bugs and performance issues.

 NOT IN

SELECT * FROM employees
WHERE department_id NOT IN (SELECT department_id FROM closed_departments);

  • Compares values from a list.
  • Caution: If the subquery returns even one NULL, NOT IN will return no results due to unknown comparison.
  • Better for small, static lists where you’re confident there are no nulls.

 NOT EXISTS

SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM closed_departments d WHERE d.department_id = e.department_id
);

  • Checks for the absence of matching rows.
  • NULL-safe — it doesn't break if NULLs are present in the subquery.
  • More efficient and reliable for correlated subqueries or large datasets.

 When to Use What

  • Use NOT EXISTS when your subquery might return NULLs or when dealing with large, complex joins.
  • Use NOT IN if you're working with a simple list and are sure there are no NULLs.

So, while both can filter out unwanted rows, NOT EXISTS is generally safer and more predictable—especially when dealing with real-world, messy data.



Your Answer

Interviews

Parent Categories