IN, NOT IN, NULL, IS NULL, IS NOT NULL
====================================
IS NULL and IS NOT NULL are not comparision operators.

expression IS [ NOT ] NULL

The expression will not be compared with NULL.
--------------------------------------------------
expression IN (1,4,6,NULL) means that:
It will return true when expression =1; or
It will return true when expression =4; or
It will return true when expression =6;

expression NOT IN (1,4,6, NULL) will return NULL.


DECLARE @a int;
set @a=33;
SELECT 'TRUE' where @a IN(1,2,3,null)
SELECT 'FALSE' where @a NOT IN (1,2,3,NULL)

No output.

expression<>1 and
expression<>2 and
expression<>3 and
expression<>NULL ==>NULL