NOT IN explained

Whenever the column returned by the subquery contains a NULL value, NOT IN will always return an empty set. Filtering out the NULL values in the subquery will solve this problem.

select
    *
from
    table1
where
    col1 not in(select col2 from table2 where col2 is not null)

Read SQL Server: JOIN vs IN vs EXISTS - the logical difference for a detailed explanation.