NOT IN filter out NULL values

Question

I was trying to filter out some predefined values from a table sample which has two column col1 and col2.

My query:

select *
from sample
where (col1 is not null or col2 is not null)
and col1 not in (1,2)
and col2 not in (3,4);

However, the above query filter out all the null values (in col1 or col2 ).

Example: the below row is filtered out,

col1 col2
---------
7 null
null 8

I get the expected result when i modify the query to below.

select *
from sample
where (col1 is not null or col2 is not null)
and (col1 not in (1,2) or col1 is null)
and (col2 not in (3,4) or col2 is null);

Why NOT IN filters out rows with NULL value even though I am not specified NULL in NOT IN ?

Answer 1

Nothing is equal to NULL, and is anything not equal to NULL. For an expression like NULL NOT IN (1,2), this evaluates to unknown which (importantly) is not true; meaning that the WHERE is not met. This is why your second query, where you handle your NULLs works.

Alternatively, you could use an EXISTS. It's perhaps not an intuitive, but handles NULL values:

WITH VTE AS(
    SELECT *
    FROM (VALUES(1,3),(2,4),(3,5),(7,NULL),(NULL,8))V(col1,col2))
SELECT *
FROM VTE
WHERE NOT EXISTS(SELECT 1
                 WHERE Col1 IN (1,2)
                   AND Col2 IN (3,4));

Answer 2

Try this

SET ANSI_NULLS OFF
select *
from sample
where (col1 is not null or col2 is not null)
and col1 not in (1,2)
and col2 not in (3,4);

ANSI NULL ON/OFF: This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value (https://blog.sqlauthority.com/2007/03/05/sql-server-quoted_identifier-onoff-and-ansi_null-onoff-explanation/#:~:text=ANSI%20NULL%20ON%2FOFF%3A,null%20returns%20a%20null%20value.).

this discussed in https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause#:~:text=NOT%20IN%20returns%200%20records,not%20the%20value%20being%20tested.