EXCEPT и INTERSECT (Transact-SQL)

Эти операторы возвращают различные строки, сравнивая результаты двух запросов.

Оператор EXCEPT возвращает уникальные строки из левого входного запроса, которые не выводятся правым входным запросом.

Оператор INTERSECT возвращает уникальные строки, выводимые левым и правым входными запросами.

Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:

• количество и порядок столбцов должны быть одинаковыми во всех запросах;

• типы данных должны быть совместимыми.

Cинтаксические обозначения в Transact-SQL

Синтаксис

{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Аргументы

• <query_specification> | ( <query_expression> )
  Спецификация запроса или выражение запроса, возвращающее данные для сравнения с данными, возвращенными другой спецификацией запроса или выражением запроса. Определения столбцов, обрабатываемых при операции EXCEPT или INTERSECT, могут быть разными, но они должны поддерживать возможность сравнения путем неявного преобразования типов. Если типы данных различаются, тип, используемый при выполнении сравнения и возврате результатов, определяется на основе правил приоритета типов данных.

  Если типы одинаковы, но различаются по точности, масштабу или длине, результат определяется на основе тех же самых правил, которые действуют при объединении выражений. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).

  Спецификация или выражение запроса не может возвращать столбцы типа xml, text, ntext, image или недвоичного пользовательского типа данных CLR, потому что эти типы данных не поддерживают сравнение.

• EXCEPT
  Оператор EXCEPT возвращает уникальные строки из левого входного запроса, которые не выводятся правым входным запросом.

• INTERSECT
  Возвращает все различные значения, входящие в результаты выполнения запросов, указанных как слева, так и справа от оператора INTERSECT.

Замечания

Если типы данных сравниваемых столбцов, возвращенных запросами, указанными слева и справа от оператора EXCEPT или INTERSECT, являются символьными типами с разными режимами сопоставления, сравнение выполняется в соответствии с правилами очередности параметров сортировки. Если нужное преобразование выполнить не удается, компонент Компонент SQL Server Database Engine возвращает ошибку.

Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.

Имена столбцов в результирующем наборе, возвращаемом оператором EXCEPT или INTERSECT, совпадают с именами, возвращаемыми запросом, который указан слева от оператора.

Имена столбцов или псевдонимы в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемых запросом, указанным слева от оператора.

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

Если оператор EXCEPT или INTERSECT используется в выражении вместе с другими операторами, оно обрабатывается в следующем порядке:

1. Выражения в скобках.

2. Оператор INTERSECT

3. Операторы EXCEPT и UNION обрабатываются слева направо в соответствии с их позицией в выражении.

Если оператор EXCEPT или INTERSECT используется для сравнения более двух наборов запросов, преобразование типов данных выполняется на основе сравнения двух запросов сразу с соблюдением вышеупомянутых правил обработки выражений.

Операторы EXCEPT и INTERSECT нельзя использовать в определениях распределенных секционированных представлений и уведомлениях о запросах.

Операторы EXCEPT и INTERSECT можно применять в распределенных запросах, но они будут выполнены только на локальном сервере и не будут распространены на связанный сервер. Таким образом, использование операторов EXCEPT и INTERSECT в распределенных запросах может сказаться на производительности.

При использовании в операции EXCEPT или INTERSECT в результирующем наборе полностью поддерживаются быстрые однонаправленные и статические курсоры. Если в операции EXCEPT или INTERSECT применяется курсор, управляемый набором ключей, или динамический курсор, то курсор результирующего набора преобразуется в статический курсор.

При выводе данных об операции EXCEPT с помощью средства графического отображения плана в среде Среда SQL Server Management Studio операция представляется как left anti semi join, а операция INTERSECT — как left semi join.

Примеры

Следующий пример демонстрирует использование операторов INTERSECT и EXCEPT. Первый запрос возвращает все значения из таблицы Production.Product для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT.

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Production.Product ;
--Result: 504 Rows

Следующий запрос возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса оператора INTERSECT.

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Production.Product
INTERSECT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от операнда EXCEPT, но отсутствующие в результатах выполнения правого запроса.

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Production.Product
EXCEPT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от операнда EXCEPT, но отсутствующие в результатах выполнения правого запроса. Таблицы расположены в порядке, обратном предыдущему примеру.

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Production.WorkOrder
EXCEPT
SELECT ProductID 
FROM Production.Product ;
--Result: 0 Rows (work orders without products)