SQL query that uses not in with a subquery

tsql

(Matty) #1

Consider the query:

select *
from Some_Table
where some_column not in (select nullable_column from Other_Table)

If the subquery has a column with null in there, this where always evaluates in either false or null, so the whole query never returns a result.
For the technical reason why, see (for example) this blogpost

This behaviour is quite counter intuitive. A correct approach would either use a where not exists or would filter out the nulls from the subquery.
I’d suggest the type for this rule to be ‘Bug’.

Non compliant code:

select *
from My_Table
where my_column not in (select some_column from Other_Table)

Compliant code:

select *
from My_Table
where not exists (select 1 from Other_Table where some_column = my_column)

or also compliant code:

select *
from My_Table
where my_column not in (select some_column from Other_Table where some_column is not null)

This should also be compliant code:

select *
from My_Table
where my_column not in (1, 2, 3, 42)

(Alexandre Gigleux) #2

Hello Matty,

Thanks for suggesting this rule. I agree with you and I created this specification: https://jira.sonarsource.com/browse/RSPEC-3641 and the related implementation tickets for T-SQL and PL/SQL:

Regards