31 lines
699 B
Plaintext
31 lines
699 B
Plaintext
== How to fix it
|
|
|
|
Use `NOT EXISTS` or `IS NOT NULL` instead of `NOT IN` when the subquery may return `NULL` values.
|
|
|
|
=== Code examples
|
|
|
|
==== Noncompliant code example
|
|
|
|
[source,sql]
|
|
----
|
|
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
|
|
|
|
[source,sql]
|
|
----
|
|
SELECT *
|
|
FROM my_table
|
|
WHERE NOT EXISTS (SELECT 1 FROM another_table WHERE nullable_column = my_table.my_column)
|
|
----
|
|
|
|
[source,sql]
|
|
----
|
|
SELECT *
|
|
FROM my_table
|
|
WHERE my_column NOT IN (SELECT nullable_column FROM another_table WHERE nullable_column IS NOT NULL)
|
|
----
|