Поделиться через

  • Статья

EXCEPT и INTERSECT (Transact-SQL)

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

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

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

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

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

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

Значок ссылки на разделСинтаксические обозначения в 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 нельзя использовать в определениях распределенных секционированных представлений, в уведомлениях об изменениях результатов запроса и вместе с предложениями COMPUTE и COMPUTE BY.

Операторы 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 AdventureWorks;
GO
SELECT ProductID 
FROM Production.Product ;
--Result: 504 Rows

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

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

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

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

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

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