I think it's worth a second here to go through why a SQL NOT IN with a NULL value in it causes no values to match.
SQL Rule 1) A WHERE condition must be true to allow a row to be selected. Not just "not proven false", but absolutely proven true.
SQL Rule 2) A comparison against a NULL does not yield TRUE or FALSE, only NULL.
Ok, that said, run these statements:
SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7) --all rows
SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7, NULL) --no rows!
Probably easiest is to think of NULL as "unknown". When SQL hits the NULL in the value list, is 1 not = NULL? Since NULL is unknown, SQL can't know. An unknown value could be 1, after all. So SQL cannot absolutely say that the NOT IN is true. But if it's not known to be absolutely true, then the WHERE clause has failed, and the row can't be included in the result from the SELECT.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."