設定運算子 - EXCEPT 和 INTERSECT (Transact-SQL)Set Operators - EXCEPT and INTERSECT (Transact-SQL)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel 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> ) }  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

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

查詢規格或運算式無法傳回 xmltextntextimage 或非二進位 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 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 所傳回結果集中任何資料行的 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 作業時,此作業會顯示為左方反半聯結,而 INTERSECT 作業會顯示為左方半聯結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

下列範例示範如何使用 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 AnalyticsAzure Synapse Analytics平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics 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.)