SQL IN and NOT IN: NULL, Empty Lists and Parameters

Keep null separate from the string NULL

The text value "NULL" is a string, while SQL NULL represents an unknown or absent value. Comparing a null column through an ordinary IN predicate does not make it match a null member as if they were equal. If the product requirement includes null rows, express that with an explicit IS NULL condition and test the full predicate with parentheses.

Be careful with NOT IN and unknown results

When a NOT IN list or subquery includes null, a nonmatching comparison can evaluate to unknown instead of true. A WHERE clause keeps true rows, so records may disappear from the output unexpectedly. For exclusion logic, consider a correctly correlated NOT EXISTS query and define how null keys should behave. Do not change operators without checking the intended matching semantics.

Handle empty application lists explicitly

For an inclusion filter, an empty set often means match no rows, so a constant FALSE predicate is a clear result. For an exclusion filter it may mean impose no restriction, which is a different decision. Our generator produces inclusion predicates only. In application code, bind values with the database driver; the PostgreSQL parameter output gives placeholder order and the corresponding string array without executing SQL.

Worked example

-- Explicitly include null column values when that is intended:
WHERE customer_id IN ($1, $2) OR customer_id IS NULL

-- Empty inclusion list:
WHERE FALSE

Does putting NULL inside IN match a null column?

Not as ordinary equality. Use an explicit IS NULL condition when null rows are part of the intended result.

Reference and further reading

Read the official documentation ↗