A WHERE clause condition that uses NOT IN with a subquery will have unexpected results if that subquery returns NULL. On the other hand NOT EXISTS
subqueries work reliably under the same conditions.
This rule raises an issue when NOT IN is used with a subquery where the selected column is nullable.
Noteworthy
This rule raises issues only when a Data Dictionary is provided during the analysis. See https://docs.sonarqube.org/latest/analysis/languages/plsql/
Noncompliant code example
SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table) -- Noncompliant; "nullable_column" may contain 'NULL' value and the whole SELECT query will return nothing
Compliant solution
SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT 1 FROM another_table WHERE nullable_column = my_table.my_column)
SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table WHERE nullable_column IS NOT NULL)
