Not in not return null value

Question

I have this query

Declare @tbl table (id int,val varchar(10)); 
insert into @tbl values (1,null), (2,'aaa'), (3,'bbb'), (4,null), (5,'ccc')

select * from @tbl 
where val not in ('aaa','bbb') 

The result is only 5 ccc. Why id 1 and 4 are not in the result?

Answer 1

Hi @Lylyy

Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN.

Please refer to this doc for more details: IN (Transact-SQL).

To return null value, you may try this query:

select * from @tbl
where isnull(val,'') not in ('aaa','bbb') 

Best regards,

Cosmog Hong

Answer 2

select * from @tbl

where val not in ('aaa','bbb') or val is null

select * from @tbl a

where not exists (select 1 from @tbl where a.val in ('aaa','bbb') )