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)