Excluding Null Values In my syntax why does the NOT IN statement not exclude NULL values?  To me, if NULL does not exist in #GoodAnswers it should return the dv

Below is syntax, can someone help explain this to me?

Create Table #GoodAnswer
(
  answer1 varchar(100)
  ,dv varchar(10)
)

Insert Into #GoodAnswer Values 
('Blue','Yes'), 
('Pink','No'),
('Purple','No')

Create Table #Data
(
  bbc varchar(100)
  ,answer varchar(100)
)

Insert Into #Data Values ('lal', NULL), ('raj', 'Orange'), ('enif', 'Zoo')

Select bbc
    , case 
        when answer not in (
            Select answer1 
            from #GoodAnswer
        ) then (
            Select answer1
            from #GoodAnswer 
            where dv = 'Yes'
        ) 
        else answer 
        end as [Valid Answer]
FROM #Data

Drop table #GoodAnswer
Drop Table #Data


My data returned from this result-set is

+------+--------------+
| BBC  | VALID ANSWER |
+------+--------------+
| lal  | NULL         |
| raj  | Blue         |
| enif | Blue         |
+------+--------------+


I feel that instead of NULL being returned it should return Blue since that is the dv in the table
NULL is special. It is neither equal nor non-equal. 

Given the same temp tables in your example, consider the results from these queries:

--returns 3 rows
SELECT * FROM #Data;

--returns 2 rows
SELECT * FROM #Data
WHERE answer NOT IN (SELECT answer1 FROM #GoodAnswer);

--returns 0 rows
SELECT * FROM #Data
WHERE answer IN (SELECT answer1 FROM #GoodAnswer);


This is because the definition of  NULL is that it is unknown. Since you don't know the value of it, you can't evaluate it's equality or inequality. Instead, you have to explicitly query for IS NULL or IS NOT NULL.