Why Is Null Ignored In Query Results
SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition:

WHERE valid NOT IN ('Green', 'Blue', 'White')


it expands to:

WHERE valid &lt;&gt; 'Green' AND valid &lt;&gt; 'Blue' AND valid &lt;&gt; 'White'


When valid is NULL, all these 3 sub-conditions evaluate to UNKNOWN (neither TRUE nor FALSE). The whole condition evaluates to UNKNOWN as well and thus the row rejected (WHERE keeps only the rows that the condition evaluates to TRUE).

When comparing nulls, you can think this way. I don't know the exact value of valid so I can't be sure if it's going to be different to 'Green' or not. It might be and it might be not. The result is UNKNOWN.



If the intention is to get in the result the rows where valid IS NULL, you can use either:

SELECT * FROM @Fiesty 
WHERE valid IS NULL
   OR valid NOT IN ('Green', 'Blue', 'White') ;


or EXISTS / EXCEPT (or NOT EXISTS / INTERSECT):

SELECT f.* FROM @Fiesty AS f 
WHERE EXISTS
      ( SELECT f.valid 
      EXCEPT 
        SELECT v.valid 
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
      ) ;


There is also a NOT EXISTS way which is my opinion to be preferred to NOT IN, especially because the behaviour regarding nulls is more intuitive. (Notice also the similarity with the INTERSECT version):

SELECT f.* FROM Fiesty AS f
WHERE NOT EXISTS
      ( SELECT *
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
        WHERE v.valid = f.valid
      ) ;


Test at dbfiddle.uk.