Why NOT IN behaves unexpectedly with NULL

NOT IN can produce unexpected results when its list or subquery contains NULL because SQL uses three-valued logic:

• TRUE

• FALSE

• UNKNOWN

Any normal comparison with NULL results in UNKNOWN, not TRUE or FALSE.

Example data

Customers

Orders

We want to find customers who have not placed an order.

Query using NOT IN

SELECT CustomerId, CustomerName
FROM Customers
WHERE CustomerId NOT IN
(
    SELECT CustomerId
    FROM Orders
);

The subquery returns:

1, NULL

SQL interprets the condition approximately as:

CustomerId <> 1
AND CustomerId <> NULL

For customer 2:

2 <> 1       -- TRUE
2 <> NULL    -- UNKNOWN

Final result:

TRUE AND UNKNOWN = UNKNOWN

The WHERE clause returns only rows whose condition is TRUE. Therefore, customer 2 is not returned.

The same happens to customer 3. As a result, the query may return no rows.

Why comparing with NULL produces UNKNOWN

NULL means that the value is unknown.

SQL cannot confidently say that:

2 <> NULL

is true, because the unknown value could theoretically be 2. Therefore, the result is UNKNOWN.

You must use:

IS NULL

or:

IS NOT NULL

to check for NULL.

Solution 1: Use NOT EXISTS—recommended

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

Result

NOT EXISTS checks whether a matching order exists for each customer. An unrelated NULL in the Orders table does not affect the result.

Solution 2: Remove NULL from the subquery

SELECT
    CustomerId,
    CustomerName
FROM Customers
WHERE CustomerId NOT IN
(
    SELECT CustomerId
    FROM Orders
    WHERE CustomerId IS NOT NULL
);

This is safe because the subquery returns only known values.

Important additional case

If the outer value itself is NULL, it will also not pass a NOT IN condition:

NULL NOT IN (1, 2, 3)

This produces UNKNOWN.

If outer NULL values must be included, handle them explicitly:

WHERE DepartmentId NOT IN (10, 20)
   OR DepartmentId IS NULL;

Key points

• NULL represents an unknown value.

• Comparisons such as = NULL and <> NULL produce UNKNOWN.

• NOT IN is effectively a series of AND comparisons.

• One NULL in the subquery can cause the entire condition to become UNKNOWN.

• A WHERE clause returns only rows where the condition is TRUE.

• Prefer NOT EXISTS when checking for missing related records.

• If using NOT IN, remove NULL values inside the subquery.

Interview answer:
“NOT IN can return no rows when its subquery contains NULL because comparisons with NULL produce UNKNOWN. Since WHERE returns only TRUE results, rows evaluating to UNKNOWN are excluded. I normally use NOT EXISTS, or filter out NULL values from the NOT IN subquery.”