Forum Discussion
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_RECH)
"
, I get an empty result. This is not expected and therefore incorrect.
The SQL command "Select AkteID from RW_RECH" returns about 10000 results.
Rewriting the SQl command to join
"Select *
from S_Akte A
left join RW_RECH B
on A.ID = B.AkteID
where B.AkteID is null"
gives me the correct result.
Also the SQL
"Select *
from S_Akte A
where A.ID not in (Select Top(30) AkteID from RW_RECH)
"
gives me a result which I expect.
Is there any way to make the first SQL command work correctly?
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
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
