Odd SQL behavior, I'm wondering why this works the way it does.
Consider the following Transact sql.

    DECLARE @table TABLE(val VARCHAR(255) NULL)
    
    INSERT INTO @table (val) VALUES('a')
    INSERT INTO @table (val) VALUES('b')
    INSERT INTO @table (val) VALUES('c')
    INSERT INTO @table (val) VALUES('d')
    INSERT INTO @table (val) VALUES(NULL)
    
    select val 
    from @table
    where val not in ('a') 

I would expect this to return 

    b, c, d, NULL

but instead it returns 

    b, c, d

Why is this the case?  Is NULL not evaluated? Is NULL somehow in the set 'a'?
Odd SQL behavior, I'm wondering why this works the way it does.
===

Consider the following Transact sql.

    DECLARE @table TABLE(val VARCHAR(255) NULL)
    
    INSERT INTO @table (val) VALUES('a')
    INSERT INTO @table (val) VALUES('b')
    INSERT INTO @table (val) VALUES('c')
    INSERT INTO @table (val) VALUES('d')
    INSERT INTO @table (val) VALUES(NULL)
    
    select val 
    from @table
    where val not in ('a') 

I would expect this to return 

    b, c, d, NULL

but instead it returns 

    b, c, d

Why is this the case?  Is NULL not evaluated? Is NULL somehow in the set 'a'?