Scott Stephens (оригинал: Oracle Tip: Understand how NULLs affect IN and EXISTS)
Перевод Моисеенко С.И.
С одной стороны, может показаться, что SQL предложения IN и EXISTS взаимозаменяемы. Однако они совершенно различаются в том, как они обрабатывают NULL-значения, и могут давать различные результаты. Возникающие проблемы связаны с тем, что в базе данных Oracle, NULL-значение имеет смысл "неизвестно", поэтому любое сравнение или операция с NULL-значением является также NULL, и любые проверки, которые возвращают NULL, всегда игнорируются. Например, ни один из этих запросов не вернет ни одной строки:
select 'true' from dual where 1 = null;
select 'true' from dual where 1 != null;
(DUAL - таблица, автоматически создаваемая Oracle и доступная для всех пользователей. Она имеет один столбец, DUMMY, содержащий одну строку - прим. перев.)
Значение 1 и ни равно, и ни не равно NULL. Только IS NULL должен дать true на NULL-значении и вернуть строку.
select 'true' from dual where 1 is null;
select 'true' from dual where null is null;
Когда вы используете IN, вы говорите SQL взять значение и сравнить его с каждым значением или набором значений в списке, используя =. Если имеются NULL-значения, строка не будет возвращена - даже если оба значения есть NULL.
select 'true' from dual where null in (null);
select 'true' from dual where (null,null) in ((null,null));
select 'true' from dual where (1,null) in ((1,null));
Функциональность IN эквивалентна использованию предложения = ANY:
select 'true' from dual where null = ANY (null);
select 'true' from dual where (null,null) = ANY ((null,null));
select 'true' from dual where (1,null) = ANY ((1,null));
Когда вы используете эквивалентную форму EXISTS, SQL подсчитывает строки и игнорирует значение(я) в подзапросе - даже если вы возвращаете NULL.
select 'true' from dual where exists (select null from dual);
select 'true' from dual where exists (select 0 from dual where null is null);
IN и EXISTS логически одинаковы. Предложение IN сравнивает значения, возвращаемые подзапросом, и отфильтровывает строки во внешнем запросе; предложение EXISTS сравнивает значения и отфильтровывает строки внутри подзапроса. В случае NULL-значений результирующий набор строк одинаков.
select ename from emp where empno in (select mgr from emp);
select ename from emp e where exists (select 0 from emp where mgr = e.empno);
Однако проблемы возникают, когда логика переворачивается на использование NOT IN и NOT EXISTS, что приводит к различным результирующим наборам строк (первый запрос возвращает 0 строк; второй - возвращает ожидаемые данные; они уже не представляют один и тот же запрос):
select ename from emp where empno not in (select mgr from emp);
select ename from emp e where not exists (select 0 from emp where mgr = e.empno);
Предложение NOT IN виртуально эквивалентно сравнению каждого значения на = и проваливается, если всякое испытание есть FALSE или NULL. Например:
select 'true' from dual where 1 not in (null,2);
select 'true' from dual where 1 != null and 1 != 2;
select 'true' from dual where (1,2) not in ((2,3),(2,null));
select 'true' from dual where (1,null) not in ((1,2),(2,3));
Эти запросы не возвращают строк. Второй - более очевиден, 1 != NULL есть NULL, поэтому все условие в предложении WHERE ложно для данной строки. В то же время нижеприведенные запросы работают:
select 'true' from dual where 1 not in (2,3);
select 'true' from dual where 1 != 2 and 1 != 3;
Как видно, вы все же можете использовать запрос NOT IN, пока вы предотвращаете появление NULL в возвращаемых результатах (опять же, они оба работают, но я предполагаю, что empno is not null, что является хорошим предположением в данном случае):
select ename from emp where empno not in (select mgr from emp where mgr is not null);
select ename from emp where empno not in (select nvl(mgr,0) from emp);
Понимая различие в IN, EXISTS, NOT IN и NOT EXISTS, вы можете избежать довольно общей проблемы при появлении NULL-значений в данных подзапроса.