Skip to main content

NOT IN should not be used with values that may be null

ZPA rule for PL/SQL and Oracle SQL · NotInWithNull

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. When one of the values is null, its comparison is unknown for every x, so the whole condition is never true: a WHERE with it returns no rows, and an IF never takes its branch. Oracle treats '' as null too. x <> ALL (subquery) is the same condition.

This rule reports a NOT IN whose list holds the literal NULL or '', which is never true. It also reports a NOT IN or <> ALL over a subquery whose only column may be null: the database metadata says the column is nullable, or an outer join in the subquery gives nulls for it, and no condition of the subquery's WHERE rejects null for that column. The query is right until a null is selected, and then it silently returns no rows. Without the metadata only the lists are checked.

Examples​

Noncompliant Code Example
-- bin_item.rack_id may be null
select count(*)
into idle_racks
from rack
where id not in (select rack_id from bin_item);
Compliant Solution
select count(*)
into idle_racks
from rack r
where not exists (select 1 from bin_item b where b.rack_id = r.id);
select count(*)
into idle_racks
from rack
where id not in (select rack_id from bin_item where rack_id is not null);