IN With NULL: Show the Unknown Comparison

I make IN with NULL truth states visible before using the expression as a filter. A positive match can be true. An unmatched value can be unknown, rather than the false result a caller expects.

Keep the unknown state visible

The example compares three inputs with a list containing one and NULL. It reports text labels instead of using WHERE to discard rows. That keeps all three cases available for inspection.

I use separate true and false branches, with UNKNOWN as the remaining state. An ELSE false branch alone would hide the distinction. A filter returns only rows meeting its condition, so it isn’t the clearest first view of three-valued comparison behavior.

WITH Inputs AS
(
 SELECT CaseId, CAST(InputValue AS int) AS InputValue
 FROM (VALUES (1,1),(2,2),(3,CAST(NULL AS int))) v(CaseId,InputValue)
)
SELECT CaseId, InputValue,
       CAST(CASE WHEN InputValue IN (1,CAST(NULL AS int)) THEN N'TRUE'
                 WHEN InputValue NOT IN (1,CAST(NULL AS int)) THEN N'FALSE'
                 ELSE N'UNKNOWN' END AS nvarchar(7)) AS InTruth,
       CAST(CASE WHEN InputValue NOT IN (1,CAST(NULL AS int)) THEN N'TRUE'
                 WHEN InputValue IN (1,CAST(NULL AS int)) THEN N'FALSE'
                 ELSE N'UNKNOWN' END AS nvarchar(7)) AS NotInTruth
FROM Inputs
ORDER BY CaseId;

Read the positive match

Input one matches the known list value one. Its IN result is true, and its NOT IN result is false. The list’s NULL doesn’t erase that established positive match.

I’d retain this row beside the unmatched example. A blanket statement that a NULL list makes every IN result unknown would be misleading. The query needs to show how a known match differs from a comparison that cannot establish membership or nonmembership.

Read an unmatched known value

Input two doesn’t match the known value one. Its comparison with the missing list value remains unknown. Both displayed truth labels are therefore UNKNOWN, because negating unknown doesn’t turn it into true.

This matters when a caller expects NOT IN to return every ordinary nonmatching value. I’d inspect the list’s nullability before adopting that expectation. The supplied number is known, but the list includes an unknown value that prevents the intended exclusion conclusion.

Preserve a missing test value

The last input is itself NULL. Neither expression establishes true or false membership for it. The original typed InputValue stays visible beside the two UNKNOWN labels.

I don’t replace that missing number with a sentinel merely to make the comparison return a simpler result. A sentinel needs its own domain guarantee and business meaning. Keeping the missing state explicit prevents an arbitrary substitute from silently becoming a legitimate identifier.

Choose a missing-value policy separately

A caller can require that list entries be non-NULL, or can define a separate relationship test. That decision should follow the intended membership rule. It isn’t established by the friendly syntax of IN.

I’d document what happens to missing inputs and missing list entries before rewriting a production predicate. An expression that returns the desired count on one sample can still classify individual records incorrectly. Complete case results make that policy easier to assess.

Separate semantics from plan advice

The query reads only inline integers and creates no objects. It makes no claim about indexes, anti-join performance or large-list resource usage. The lesson concerns the truth states of a small explicit comparison.

I’d compare all three complete rows during validation, including both truth columns. Then review the production query’s source nullability and actual plan separately. A faster predicate doesn’t make an unresolved NULL policy correct, and a correct small truth table doesn’t establish production performance.

Show all three truth states, and the filter stops surprising you.

Unknown membership is not false membership, it is a separate truth state that needs an explicit policy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.