В SQL Server 2005 работает вариант, описанный для DB2 и PostgreSQL, т. к. в нем уже поддерживается конструкция EXCEPT. - Примеч. науч.ред.

Обсуждение

DB2 и PostgreSQL

Встроенные функции, предоставляемые DB2 и PostgreSQL, существенно упрощают решение поставленной задачи. Оператор EXCEPT принимает первое результирующее множество и удаляет из него все строки, обнаруженные во втором результирующем множестве. Операция очень похожа на вычитание.

Существуют некоторые ограничения на использование операторов над множествами, включая EXCEPT: типы данных и количество сравниваемых значений в списках обоих операторов SELECT должны совпадать. Кроме того, EXCEPT не возвращает дубликаты значений и, в отличие от подзапроса, использущего NOT IN, значения NULL не представляют проблемы (смотрите обсуждение для MySQL и SQL Server). Оператор EXCEPT возвращает строки, полученные в результате верхнего запроса (запроса, предшествующего EXCEPT), которых нет в запросе ниже (запросе, следующим за EXCEPT).

Oracle

Решение для Oracle идентично решению для DB2 и PostgreSQL за исключением того, что в Oracle оператор вычитания множеств называется MINUS, а не EXCEPT. Во всем остальном предыдущее объяснение применимо и к Oracle.

MySQL и SQL Server1

Подзапрос возвращает все значения столбца DEPTNO таблицы EMP. Внешний запрос возвращает все значения столбца DEPTNO таблицы DEPT, которых нет или которые не включены в результирующее множество, возвращенное подзапросом.

Исключение дубликатов является важным аспектом решений для MySQL и SQL Server. Функции EXCEPT и MINUS, используемые в решениях для других платформ, обеспечивают устранение дублирующихся строк из результирующего множества, что гарантирует однократное представление каждого значения столбца DEPTNO. Конечно, это произойдет в любом случае, поскольку DEPTNO является ключевым полем в данных моего примера. Если DEPTNO - не ключевое поле, использование DISTINCT, как в примере ниже, гарантирует, что каждое значение DEPTNO, отсутствующее в EMP, будет выведено всего один раз:

select distinct deptno from dept

where deptno not in (select deptno from emp)

Теперь рассмотрим тот же пример с использованием NOT IN и NOT OR:

select deptno from dept where deptno not in ( 10,50,null )

(возвращает пустое множество)

select deptno from dept

where not (deptno=10 or deptno=50 or deptno=null)

(возвращает пустое множество) Как видите, условие DEPTNO NOT IN (10, 50, NULL) эквивалентно:

При использовании оператора NOT IN не забывайте о значениях NULL. Рассмотрим следующую таблицу NEW DEPT:

create table new dept(deptno integer)

insert into new dept values (10)

insert into new dept values (50)

insert into new dept values (null)

Если попытаться найти в таблице DEPT значения DEPTNO, которых нет в таблице NEW DEPT, и использовать для этого подзапрос с оператором NOT IN, запрос не возвратит ни одной строки:

select * from dept

where deptno not in (select deptno from new dept)

В таблице NEWDEPT нет значений 20, 30 и 40 столбца DEPTNO, тем не менее они не были возвращены в результате запроса. Причина в значении NULL, присутствующем в таблице NEW DEPT. Подзапрос возвращает три строки со значениями DEPTNO 10, 50 и NULL. По сути, IN и NOT IN - операции логического ИЛИ. Формируемый ими результат зависит от того, как интерпретируются значения NULL при вычислении логического ИЛИ. Рассмотрим пример использования IN и эквивалентный ему пример с применением оператора OR (ИЛИ):

select deptno

from dept where deptno in ( 10,50,null )

DEPTNO 10

select deptno from dept

where (deptno=10 or deptno=50 or deptno=null)

DEPTNO

По существу, внешний запрос в данном решении рассматривает все строки таблицы DEPT. С каждой строкой DEPT происходит следующее:

1. Выполняется подзапрос с целью проверки существования данного номера отдела в таблице EMP. Обратите внимание на условие D.DEPTNO = E.DEPTNO, с помощью которого сопоставляются номера отделов из двух таблиц.

2. Если подзапрос возвращает результаты, выражение EXISTS (... ) возвращает значение TRUE, а NOT EXISTS (...) соответственно FALSE, и строка, рассматриваемая внешним запросом, не включается в результирующее множество.

3. Если подзапрос не возвращает результатов, выражение NOT EXISTS (... ) возвращает TRUE, и рассматриваемая внешним запросом строка включается в результирующее множество (поскольку она соответствует отделу, который еще не представлен в таблице EMP).

При использовании связанного подзапроса с EXISTS/NOT EXISTS не важно, какие элементы перечислены в операторе SELECT подзапроса.

not (deptno=10 or deptno=50 or deptno=null)

Вот как вычисляется это выражение в случае, когда DEPTNO 50:

not (deptno=10 or deptno=50 or deptno=null) (false or false or null) (false or null) null

В SQL выражению TRUE or NULL соответствует TRUE, а FALSE or NULL - NULL! Полученный результат NULL обеспечит NULL при последующих вычислениях (если только не провести проверку на NULL, применяя технику, представленную в рецепте Поиск NULL значений главы 1). Об этом необходимо помнить при использовании предикатов IN и осуществлении вычислений логического ИЛИ, когда присутствуют значения NULL.

Во избежание проблем с NOT IN и значениями NULL применяются связанные подзапросы в сочетании с предикатом NOT EXISTS. Термин связанные подзапросы возник потому, что подзапрос использует строки, возвращаемые внешним запросом. Следующий пример является альтернативным решением, в котором строки со значениями NULL не представляют никакой проблемы (возвращаясь к исходному запросу из раздела Задача ):

select d.deptno

from dept d where not exists ( select null from emp e where d.deptno = e.deptno )

DEPTNO