select * from (
    select 1 as a union
    select 2 union
    select 3 union
    select 4 union
    select 5
)tb where tb.a not in (1,2,null)


Please can any one tell me why is the above query not returning any row.
As per my expectation it should return 3 row.
Problem here is that when i use  null it not works, but why?
if I remove null it works fine.
How NOT IN works ?
Thanks in advance

I am using sql server 2005

what will this gives you ?
select * from
(
    select 1 as a union
    select 2 union
    select 3 union
    select 4 union
    select 5
)tb where tb.a not in (1,2)

3,4,5 right ? NOT IN basically can be interpret as
WHERE tb.a <> 1
AND     tb.a  <> 2

so with tb.a not in (1, 2, null)
it will be
WHERE tb.a <> 1
AND     tb.a  <> 2
AND    tb.a <> NULL

Remember that when comparing with NULL, it will always be FALSE