Операторы Set — EXCEPT и INTERSECT (Transact-SQL)

Статья
05/23/2023

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики аналитики Synapse Analytics Analytics (PDW)SQL Analyticsв Microsoft FabricХранилище в Microsoft Fabric

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

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

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

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

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

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

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

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

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

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

ИСКЛЮЧЕНИЯ
Возвращает все различные значения, возвращенные запросом, указанным слева от оператора EXCEPT. Эти значения возвращаются, если они отсутствуют в результатах выполнения правого запроса.

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

Замечания

Типы данных сравниваемых столбцов возвращаются запросами слева и справа от операторов EXCEPT или INTERSECT. Эти типы данных могут содержать символьные типы данных с различными параметрами сортировки. При этом необходимое сравнение выполняется в соответствии с правилами очередности параметров сортировки. Если вы не можете выполнить это преобразование, sql Server ядро СУБД возвращает ошибку.

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

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

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

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

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

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

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

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

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

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

Если операция EXCEPT отображается с помощью функции графического шоуплана в СРЕДЕ SQL Server Management Studio, операция отображается как левое антизаверховое соединение, а операция INTERSECT отображается как левое полусоединение.

Примеры

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

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product ;  
--Result: 504 Rows

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

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

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

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

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

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

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

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales;  
--Result: 60398 Rows

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
INTERSECT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer.Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9133 Rows (Sales to customers that are female.)

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
EXCEPT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer.Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9351 Rows (Sales to customers that are not female.)