SET 运算符 - EXCEPT 和 INTERSECT (Transact-SQL)Set Operators - EXCEPT and INTERSECT (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 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-SQL 语法约定Topic 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.

备注Remarks

可比较列的数据类型是由 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 数据库引擎SQL Server Database Engine 返回错误。If you can't run this conversion, the SQL Server 数据库引擎SQL 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 返回的结果集中任何列的为 Null 性,与运算符左侧的查询返回的相应列的为 Null 性相同。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. INTERSECT 运算符The INTERSECT operator

  3. 基于在表达式中的位置从左到右求值的 EXCEPT 和 UNIONEXCEPT 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.

使用 SQL Server Management StudioSQL Server Management Studio 中的图形显示计划功能显示 EXCEPT 运算时,该运算显示为 left anti semi join,INTERSECT 运算显示为 left semi joinWhen 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

以下示例说明如何使用 INTERSECTEXCEPT 运算符。The following examples show using the INTERSECT and EXCEPT operators. 第一个查询返回 Production.Product 表中的所有值,以便对 INTERSECTEXCEPT 所返回的结果进行比较。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 DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

以下示例说明如何使用 INTERSECTEXCEPT 运算符。The following examples show how to use the INTERSECT and EXCEPT operators. 第一个查询返回 FactInternetSales 表中的所有值,以便对 INTERSECTEXCEPT 所返回的结果进行比较。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.)