Not in not return null value

Lylyy 300 Reputation points

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,610 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,459 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 17,231 Reputation points Microsoft Vendor

    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

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jingyang Li 5,806 Reputation points

    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') )

    0 comments No comments