NOT IN - парадоксальный результат

Коллеги обратились с вопросом: "Почему NOT IN" не дает выборки, хотя она быть должна??

Рассмотрим тестовый пример:

select level as x from dual connect by level <= 5
;
1
2
3
4
5

select level as y from dual connect by level <= 3
;
1
2
3

-- получили две последовательности натуральных чисел, все хорошо. Сделаем NOT IN:

with
s as (select level as x from dual connect by level <= 5),
d as (select level as y from dual connect by level <= 3)
select * from s where x not in (select y from d)
;
4
5

-- абсолютно очевидно; ожидаемый результат.

Добавим пустое значение:

with
s as (select level as x from dual connect by level <= 5),
d as (select level as y from dual connect by level <= 3
union all
select null from dual)
select * from s where x not in (select y from d)
;
-- NO ROWS FOUND (!!!)

Дело в том, что оператор IN - это сравнение равенством, а равенство с пустым значением не допускается.

Проверим совсем простой пример:

select 1 from dual where 1 = null
;
select 1 from dual where 1 != null
;

-- в обоих случаях NO ROWS FOUND; и 1 = null, и 1 != null - оба ЛОЖНЫ!

В задаче коллег в одной из таблиц был (выяснилось: ВСЕГО ОДИН!) null. Он надежно остановил выборку. В таких случаях лучше применять NOT EXISTS или MINUS. Можно, также, исключить null'ы. В любом случае, null на обратной логике способен доставить дискомфорт. Прямая и обратная логика работают вовсе не симметрично.