NOT IN и null: скандал

  У оператора NOT IN имеется особенность при работе с выборкой, состоящей из значений, в которых присутствует NULL. При таких условиях, особенность заключается в том, результат оператора будет всегда false, т.е. даже если value отсутствует в выборке.

Пусть есть таблица t1

create table t1 (id integer);

Со записями

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (null);

По житейской логике следующий запрос должен вернуть строку из dual, но этого не происходит

select '5_not_found' from dual
where 5 not  in (select id
                  from t1)

Дело в том, что оператор NOT IN разворачивается в следующее

select '5_not_found' from dual
where 5 <> 1
   and 5  <> 2
   and 5  <> 3
   and 5  <> 4

   and 5  <>  null

 Бороться с этим нужно заменой NOT IN на NOT EXISTS.

select '5_not_found' from dual
where not exists (select 1
                    from t1
                   where t1.id = 5 )