NOT IN vs NOT EXISTS

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.