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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Эти операторы возвращают различные строки, сравнивая результаты двух запросов.Returns distinct rows by comparing the results of two queries.

Оператор EXCEPT возвращает уникальные строки из левого входного запроса, которые не выводятся правым входным запросом.EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

Оператор INTERSECT возвращает уникальные строки, выводимые левым и правым входными запросами.INTERSECT returns distinct rows that are output by both the left and right input queries operator.

Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:To combine the result sets of two queries that use EXCEPT or INTERSECT, the basic rules are:

  • количество и порядок столбцов должны быть одинаковыми во всех запросах;The number and the order of the columns must be the same in all queries.

  • типы данных должны быть совместимыми.The data types must be compatible.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

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

АргументыArguments

<query_specification> | ( <query_expression> )<query_specification> | ( <query_expression> )
Спецификация запроса или выражение запроса, возвращающее данные для сравнения с данными, возвращенными другой спецификацией запроса или выражением запроса.Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. Определения столбцов, обрабатываемых при операции EXCEPT или INTERSECT, могут быть разными.The definitions of the columns that are part of an EXCEPT or INTERSECT operation don't have to be the same. Тем не менее они должны поддерживать возможность сравнения путем неявного преобразования типов.But, they must be comparable through implicit conversion. Если типы данных различаются, то тип данных для сравнения определяется на основе правил очередности типов данных.When data types differ, the rules for data type precedence determine the data type that is run for comparison.

Если типы одинаковы, но различаются по точности, масштабу или длине, результат определяется на основе тех же самых правил, которые действуют при объединении выражений.The result is based on the same rules for combining expressions when the types are the same but differ in precision, scale, or length. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).For more information, see Precision, Scale, and Length (Transact-SQL).

Спецификация или выражение запроса не может возвращать столбцы типа xml, text, ntext, image или недвоичного пользовательского типа данных CLR, потому что эти типы данных не поддерживают сравнение.The query specification or expression can't return xml, text, ntext, image, or nonbinary CLR user-defined type columns because these data types aren't comparable.

EXCEPTEXCEPT
Возвращает все различные значения, возвращенные запросом, указанным слева от оператора EXCEPT.Returns any distinct values from the query left of the EXCEPT operator. Эти значения возвращаются, если они отсутствуют в результатах выполнения правого запроса.Those values return as long the right query doesn't return those values as well.

INTERSECTINTERSECT
Возвращает все различные значения, входящие в результаты выполнения запросов, указанных как слева, так и справа от оператора INTERSECT.Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

RemarksRemarks

Типы данных сравниваемых столбцов возвращаются запросами слева и справа от операторов EXCEPT или INTERSECT.The data types of comparable columns are returned by the queries left and right of the EXCEPT or INTERSECT operators. Эти типы данных могут содержать символьные типы данных с различными параметрами сортировки.These data types can include character data types with different collations. При этом необходимое сравнение выполняется в соответствии с правилами очередности параметров сортировки.When they do, the required comparison is run according to the rules of collation precedence. Если нужное преобразование выполнить не удается, Компонент SQL Server Database EngineSQL Server Database Engine возвращает ошибку.If you can't run this conversion, the Компонент SQL Server Database EngineSQL Server Database Engine returns an error.

Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.When comparing column values for determining DISTINCT rows, two NULL values are considered equal.

Операторы EXCEPT и INTERSECT возвращают имена столбцов результирующего набора, совпадающие с именами столбцов, которые возвращает запрос слева от оператора.EXCEPT and INTERSECT return the result set's column names that are the same as the column names that the query on the operator's left side returns.

Имена столбцов или псевдонимы в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемых запросом, указанным слева от оператора.Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

Возможность хранения пустых значений в каком-либо столбце результирующего набора, возвращаемого оператором EXCEPT или INTERSECT, зависит от того, поддерживает ли это соответствующий столбец, возвращаемый запросом, указанным слева от оператора.The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column that is returned by the query on the operator's left side.

Если оператор EXCEPT или INTERSECT используется в выражении вместе с другими операторами, оно обрабатывается в следующем порядке:If EXCEPT or INTERSECT is used together with other operators in an expression, it's evaluated in the context of the following precedence:

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

  2. Оператор INTERSECTThe INTERSECT operator

  3. Операторы EXCEPT и UNION обрабатываются слева направо в соответствии с их позицией в выражении.EXCEPT and UNION evaluated from left to right based on their position in the expression

Оператор EXCEPT или INTERSECT можно использовать для сравнения более двух наборов запросов.You can use EXCEPT or INTERSECT to compare more than two sets of queries. При этом преобразование типов данных выполняется на основе сравнения двух запросов сразу с соблюдением вышеупомянутых правил обработки выражений.When you do, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.

Операторы EXCEPT и INTERSECT нельзя использовать в определениях распределенных секционированных представлений и уведомлениях о запросах.EXCEPT and INTERSECT can't be used in distributed partitioned view definitions, query notifications.

Операторы EXCEPT и INTERSECT можно применять в распределенных запросах, но они будут выполнены только на локальном сервере и не будут распространены на связанный сервер.EXCEPT and INTERSECT may be used in distributed queries, but are only executed on the local server and not pushed to the linked server. Таким образом, использование операторов EXCEPT и INTERSECT в распределенных запросах может сказаться на производительности.As such, using EXCEPT and INTERSECT in distributed queries may affect performance.

При выполнении операции EXCEPT или INTERSECT в результирующем наборе можно использовать быстрые однонаправленные и статические курсоры.You can use fast forward-only and static cursors in the result set when they're used with an EXCEPT or INTERSECT operation. В операции EXCEPT и INTERSECT можно использовать курсор, управляемый набором ключей, или динамический курсор.You can also use a keyset-driven or dynamic cursor together with an EXCEPT or INTERSECT operation. В таком случае курсор результирующего набора преобразуется в статический курсор.When you do, the cursor of the operation result set is converted to a static cursor.

При выводе данных об операции EXCEPT с помощью средства графического отображения плана в среде SQL Server Management StudioSQL Server Management Studio операция представляется как left anti semi join, а операция INTERSECT — как left semi join.When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management StudioSQL Server Management Studio, the operation appears as a left anti semi join, and an INTERSECT operation appears as a left semi join.

ПримерыExamples

В следующих примерах демонстрируется использование операторов INTERSECT и EXCEPT.The following examples show using the INTERSECT and EXCEPT operators. Первый запрос возвращает все значения из таблицы Production.Product для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT.The first query returns all values from the Production.Product table for comparison to the results with INTERSECT and EXCEPT.

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

Следующий запрос возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса оператора INTERSECT.The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

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

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT, но отсутствующие в результатах выполнения правого запроса.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

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

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT, но отсутствующие в результатах выполнения правого запроса.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query. Таблицы расположены в порядке, обратном предыдущему примеру.The tables are reversed from the previous example.

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

Примеры: Azure Synapse Analytics (хранилище данных SQL)Azure Synapse Analytics (SQL DW) и Параллельное хранилище данныхParallel Data WarehouseExamples: Azure Synapse Analytics (хранилище данных SQL)Azure Synapse Analytics (SQL DW) and Параллельное хранилище данныхParallel Data Warehouse

Следующий пример демонстрирует использование операторов INTERSECT и EXCEPT.The following examples show how to use the INTERSECT and EXCEPT operators. Первый запрос возвращает все значения из таблицы FactInternetSales для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT.The first query returns all values from the FactInternetSales table for comparison to the results with INTERSECT and EXCEPT.

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

Следующий запрос возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса оператора INTERSECT.The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

-- 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.)  

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT, но отсутствующие в результатах выполнения правого запроса.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

-- 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.)