SQL Server Interview Question #75

What is the difference between NOT EXISTS and NOT IN?

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

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
);