SQL Server Interview Question #75

What is the difference between NOT EXISTS and NOT IN?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Quick Interview Answer

Both can express exclusion, but NOT IN can behave unexpectedly when its comparison set contains NULL. Because SQL uses three-valued logic, the presence of NULL can cause the predicate to evaluate as UNKNOWN rather than TRUE. NOT EXISTS is generally safer for anti-join logic when NULLs may be present. Alternatively, NULLs must be explicitly excluded from the NOT IN subquery.

Detailed Explanation

Both can express exclusion, but NOT IN can behave unexpectedly when its comparison set contains NULL. Because SQL uses three-valued logic, the presence of NULL can cause the predicate to evaluate as UNKNOWN rather than TRUE.

NOT EXISTS is generally safer for anti-join logic when NULLs may be present. Alternatively, NULLs must be explicitly excluded from the NOT IN subquery.

Code Example

SELECT c.Id, c.Name
FROM dbo.Customers AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.CustomerId = c.Id
);