Поради 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-значень в даних підзапиту.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*