Forum Discussion

schumifrick's avatar
schumifrick
Copper Contributor
Nov 07, 2023
Solved

SQL Server 2022 "select where not in subquery" returns no results

I'm currently using the latest version of SQL Server 2022 with compatibility level 'SQL Server 2008 (100)' If I use this SQL "Select * from S_Akte A where A.ID not in (Select AkteID from RW_R...
  • olafhelper's avatar
    olafhelper
    Nov 08, 2023

     

    I get an empty result. This is not expected and therefore incorrect.

    schumifrick , the empty result is expected and therefore correct, if the sub-query returns a NULL value.

    A compare on NULL returns a undefined/UNKNOWN result, which ends in a FALSE compare and so you get an empty result = correct.

    Simple example:

    select * from sys.objects;
select * from sys.objects where not object_id in (select null);

    See NULL and UNKNOWN (Transact-SQL) - SQL Server | Microsoft Learn

     

     

olafhelper's avatar
olafhelper
Bronze Contributor
Nov 08, 2023

 

I get an empty result. This is not expected and therefore incorrect.

schumifrick , the empty result is expected and therefore correct, if the sub-query returns a NULL value.

A compare on NULL returns a undefined/UNKNOWN result, which ends in a FALSE compare and so you get an empty result = correct.

Simple example:

select * from sys.objects;
select * from sys.objects where not object_id in (select null);

See NULL and UNKNOWN (Transact-SQL) - SQL Server | Microsoft Learn

 

 

Resources