Oracle. not in и null

Если в колонке внутри подзапроса not in возможно появление null то это может привести к неверному поведению.

Рассмотрим пример, сделаем две таблицы:

create table test.table1 
  as select value v 
     from table(sys.odcinumberlist(1, 2, 3));

create table test.table2 
  as select value v 
     from table(sys.odcinumberlist(1, 3, null));

Запрос с in возвращает то что ожидается

select 
  t.value 
from 
  test.table1 t 
where 
  t.value in (select t.value from test.table2);

1 3

А вот запрос с not in не возвращает вообще ничего

select 
  t.value 
from 
  test.table1 t 
where 
  t.value not in (select t.value from test.table2);

И так будет всегда если в результате подзапроса присутствует null. Таким образом, если делается запрос с not in, всегда нужно проверить что колонка в подзапросе является not null. Или заменить условие, например на not exists.

Подробная статья про поведение null в Oracle, в том числе разобрана ситуация с not in.