When using the NOT IN filter, query results do not include rows where the column value used in the comparison is NULL, even though NULL is not in the list of values to match.
This is expected behavior and is due to the special nature of NULL. In general, SQL uses a three-valued logic. The result of a comparison can be TRUE, FALSE, or UNKNOWN. A comparison with NULL always results in UNKNOWN.
SELECT statements discard rows for which the WHERE predicate is FALSE or UNKNOWN.
In order to include the NULL values, it is necessary to add a specific check for NULL
EXAMPLE
/************************************************************************/* This returns the row in table1, since code1 (8783) is not a value/* that matches code3*************************************************************************/WITH table1 AS (SELECT 8783 AS code1,NULL as code2),table2 AS (SELECT 5555 AS code3)SELECT * FROM table1 WHERE code1 NOT IN(SELECT code3 FROM table2);/************************************************************************/* This returns no rows, since it is UNKNOWN if code2 (NULL) is a value/* that matches code3/************************************************************************/WITH table1 AS (SELECT 8783 AS code1,NULL as code2),table2 AS (SELECT 5555 AS code3)SELECT * FROM table1 WHERE code2 NOT IN(SELECT code3 FROM table2);/************************************************************************/* This returns the row in table1, since the specific OR...IS NULL check was/* added/************************************************************************/WITH table1 AS (SELECT 8783 AS code1,NULL as code2),table2 AS (SELECT 5555 AS code3)SELECT * FROM table1 WHERE (code2 NOT IN(SELECT code3 FROM table2)OR code2 IS NULL)