TSQL with NULL NOT IN clause, will it ever fail or no?

Question

While submitting code for my graded project I was pointed that this logic with NOT IN could fail if any Flag has NULL value. I've did my homework and testing and could not confirm this, my query below always works fine even dealing with NULL. Is my professor wrong? I read about other bad cases with NOT IN with NULL but they are bit different with NULL inside parenthesis (eg. NOT IN ('Alpha',NULL) in my case I have defined literals. I just want to make sure that current code is 100% NULL proof.

Can anybody break my theory? Or do we all agree that it's correct. I'm on most recent SQL 2016 with default setting, which I think by default has ANSI NULL OFF (if it makes any difference). I actually tested my code for ON/OFF without any difference.

I also put my logical interpretation for each case to support my case. Added: in my case I don't want any NULLs selected, only need to bring single raw `4=ID, 'X-Ray' = Flag

 DROP TABLE If exists #x
  SELECT * INTO #x FROM (
      SELECT 1 ID, 'Alpha'  Flag   UNION  SELECT 777 ID, NULL     Flag  UNION
      SELECT 3 ID, 'Bravo'  Flag   UNION  SELECT 4   ID, 'X-Ray'  Flag  
 ) a 
 
SELECT  * FROM #x   
WHERE Flag  NOT IN ('Alpha','Bravo')  
--Case#1   'Alpha' <> 'Alpha'   AND   'Alpha' <> 'Bravo'     
--                TRUE                       FALSE          ==> FALSE   (based on True AND False)
--Case#2   'X-Ray' <> 'Alpha'   AND   'X-Ray' <> 'Bravo'    
--                TRUE                       TRUE           ==> TRUE    (TRUE and TRUE)
--Case#3   NULL    <> 'Alpha'   AND   NULL    <> 'Bravo'    
--                UNknown                    UNknown        ==> UKNOWN = not TRUE   (never selected )

Answer 1

The problem is with null values that are within the argument to NOT IN(). So the test case would be:

SELECT  * 
FROM #x   
WHERE Flag NOT IN ('Alpha','Bravo', NULL) 

For your sample data, this returns no rows.

Why this behaves like that is because, basically, the database translates NOT IN as:

WHERE Flag <> 'Alpha' AND Flag <> 'Bravo' AND Flag <> NULL

Flag <> NULL returns NULL, regardless of the value of Flag, so the last condition can never be true, and all rows are filtered out. One says that NOT IN is not NULL-safe.

Of course, if you are using a literal list of values that never contain NULL values, then you are fine with NOT IN. The problem often arises when subqueries come into play within NOT IN.