Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 09 July 2020 20:18:02


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Советы Oracle: Как NULL-значения влияют на оценку предикатов IN и EXISTS

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-значений в данных подзапроса.

22-08-2004

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.