Mengenoperatoren – EXCEPT und INTERSECT (Transact-SQL)Set Operators - EXCEPT and INTERSECT (Transact-SQL)

GILT FÜR: jaSQL Server (ab 2008) jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Gibt durch den Vergleich zweier Abfragen eindeutige Zeilen zurück.Returns distinct rows by comparing the results of two queries.

EXCEPT gibt eindeutige Zeilen aus der linken Eingabeabfrage zurück, die nicht von der rechten Eingabeabfrage ausgegeben werden.EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

INTERSECT gibt eindeutige Zeilen zurück, die durch die linken und rechten Eingabeabfragen ausgegeben werden.INTERSECT returns distinct rows that are output by both the left and right input queries operator.

Für das Kombinieren der Resultsets zweier Abfragen, die EXCEPT oder INTERSECT verwenden, gelten folgende Grundregeln:To combine the result sets of two queries that use EXCEPT or INTERSECT, the basic rules are:

  • Die Anzahl und die Reihenfolge der Spalten müssen für alle Abfragen identisch sein.The number and the order of the columns must be the same in all queries.

  • Die Datentypen müssen kompatibel sein.The data types must be compatible.

Themenlinksymbol Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

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

ArgumenteArguments

<query_specification> | ( <query_expression> )<query_specification> | ( <query_expression> )
Eine Abfrageangabe oder ein Abfrageausdruck, die bzw. der Daten zurückgibt, die mit den Daten aus einer anderen Abfrageangabe oder einem anderen Abfrageausdruck zu vergleichen sind.Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. Die Definitionen der Spalten, die Bestandteil eines EXCEPT- oder INTERSECT-Vorgangs sind, müssen nicht identisch sein.The definitions of the columns that are part of an EXCEPT or INTERSECT operation don't have to be the same. Aber sie müssen mithilfe impliziter Konvertierung vergleichbar sein.But, they must be comparable through implicit conversion. Wenn sich die Datentypen unterscheiden, bestimmen die Regeln für die Rangfolge der Datentypen den für den Vergleich verwendeten Datentyp.When data types differ, the rules for data type precedence determine the data type that is run for comparison.

Das Ergebnis basiert auf denselben Regeln wie für das Kombinieren von Ausdrücken, wenn die Typen identisch sind, sich aber in Genauigkeit, Dezimalstellen oder Länge unterscheiden.The result is based on the same rules for combining expressions when the types are the same but differ in precision, scale, or length. Weitere Informationen finden Sie unter Genauigkeit, Dezimalstellen und Länge (Transact-SQL).For more information, see Precision, Scale, and Length (Transact-SQL).

Die Abfrageangabe bzw. der Abfrageausdruck kann keine Spalten des Datentyps xml, text, ntext, image bzw. keine nichtbinären benutzerdefinierten CLR-Typspalten zurückgeben, da diese Datentypen nicht vergleichbar sind.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
Gibt alle eindeutigen Werte aus der Abfrage links vom EXCEPT-Operator zurück.Returns any distinct values from the query left of the EXCEPT operator. Diese Werte werden zurückgeben, solange die rechte Abfrage diese Werte nicht auch zurückgibt.Those values return as long the right query doesn't return those values as well.

INTERSECTINTERSECT
Gibt alle eindeutigen Werte zurück, die von den Abfragen auf der linken und rechten Seite des INTERSECT-Operators zurückgegeben werden.Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

RemarksRemarks

Die Datentypen vergleichbarer Spalten werden von den Abfragen links und rechts vom EXCEPT- oder INTERSECT-Operator zurückgegeben.The data types of comparable columns are returned by the queries left and right of the EXCEPT or INTERSECT operators. Diese Datentypen können Zeichendatentypen mit unterschiedlichen Sortierungen enthalten.These data types can include character data types with different collations. Wenn dies der Fall, wird der erforderliche Vergleich gemäß den Regeln der Rangfolge von Sortierungen ausgeführt.When they do, the required comparison is run according to the rules of collation precedence. Wenn Sie diese Konvertierung nicht ausführen können, gibt die SQL Server-Datenbank-EngineSQL Server Database Engine einen Fehler zurück.If you can't run this conversion, the SQL Server-Datenbank-EngineSQL Server Database Engine returns an error.

Wenn Sie zum Bestimmen EINDEUTIGER Zeilen Spaltenwerte vergleichen, werden zwei NULL-Werte als identisch betrachtet.When comparing column values for determining DISTINCT rows, two NULL values are considered equal.

EXCEPT und INTERSECT geben die Spaltennamen des Resultsets zurück, die mit den Spaltennamen identisch sind, die die Abfrage links vom Operator zurückgibt.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.

Spaltennamen oder -aliasse in ORDER BY-Klauseln müssen auf Spaltennamen verweisen, die von der linken Abfrage zurückgegeben werden.Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

Die NULL-Zulässigkeit aller Spalten des Resultsets, die von EXCEPT oder INTERSECT zurückgegeben wird, entspricht der NULL-Zulässigkeit der entsprechenden Spalte, die von der linken Abfrage des Operators zurückgegeben wird.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.

Wenn EXCEPT oder INTERSECT zusammen mit anderen Operatoren in einem Ausdruck verwendet wird, wird dieser in der folgenden Rangfolge ausgewertet:If EXCEPT or INTERSECT is used together with other operators in an expression, it's evaluated in the context of the following precedence:

  1. Ausdrücke in KlammernExpressions in parentheses

  2. Der INTERSECT-OperatorThe INTERSECT operator

  3. EXCEPT und UNION werden auf der Grundlage ihrer Position im Ausdruck von links nach rechts ausgewertet.EXCEPT and UNION evaluated from left to right based on their position in the expression

Sie können mit EXCEPT oder INTERSECT mehr als zwei Sätze von Abfragen vergleichen.You can use EXCEPT or INTERSECT to compare more than two sets of queries. Wenn Sie dies tun, wird die Datentypkonvertierung bestimmt, indem zwei Abfragen nacheinander verglichen werden. Dies erfolgt gemäß der zuvor erwähnten Regeln der Ausdrucksauswertung.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 und INTERSECT können nicht in verteilten partitionierten Sichtdefinitionen und Abfragebenachrichtigungen verwendet werden.EXCEPT and INTERSECT can't be used in distributed partitioned view definitions, query notifications.

EXCEPT und INTERSECT können in verteilten Abfragen verwendet werden. Sie werden jedoch nur auf dem lokalen Server ausgeführt und nicht mithilfe eines Push-Vorgangs an den Verbindungsserver übertragen.EXCEPT and INTERSECT may be used in distributed queries, but are only executed on the local server and not pushed to the linked server. Daher kann sich das Verwenden von EXCEPT und INTERSECT in verteilten Abfragen auf die Leistung auswirken.As such, using EXCEPT and INTERSECT in distributed queries may affect performance.

Sie können Vorwärtscursor und statische Cursor im Resultset verwenden, wenn sie mit einem EXCEPT- oder INTERSECT-Vorgang verwendet werden.You can use fast forward-only and static cursors in the result set when they're used with an EXCEPT or INTERSECT operation. Sie können auch einen keysetgesteuerten oder dynamischen Cursor zusammen mit einem EXCEPT- oder INTERSECT-Vorgang verwenden.You can also use a keyset-driven or dynamic cursor together with an EXCEPT or INTERSECT operation. Wenn Sie dies tun, wird der Cursor des Resultsets des Vorgangs in einen statischen Cursor konvertiert.When you do, the cursor of the operation result set is converted to a static cursor.

Wenn ein EXCEPT-Vorgang mithilfe dem Feature des grafischen Showplans von SQL Server Management StudioSQL Server Management Studio angezeigt wird, wird der Vorgang als Left Anti Semi Join angezeigt, und ein INTERSECT-Vorgang wird als Left Semi Join angezeigt.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.

BeispieleExamples

In den folgenden Beispielen wird die Verwendung der Operatoren INTERSECT und EXCEPT veranschaulicht.The following examples show using the INTERSECT and EXCEPT operators. Die erste Abfrage gibt alle Werte aus der Production.Product-Tabelle zum Vergleich mit den Ergebnissen mit INTERSECT und EXCEPT zurück.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  

Die folgende Abfrage gibt alle eindeutigen Werte zurück, die von den Abfragen auf der linken und rechten Seite des INTERSECT-Operators zurückgegeben werden.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)  

Die folgende Abfrage gibt alle eindeutigen Werte aus der Abfrage links vom EXCEPT-Operator zurück, die nicht auch in der rechten Abfrage gefunden werden.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)  

Die folgende Abfrage gibt alle eindeutigen Werte aus der Abfrage links vom EXCEPT-Operator zurück, die nicht auch in der rechten Abfrage gefunden werden.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query. Die Tabellen sind die Umkehrung des vorherigen Beispiels.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)  

Beispiele: Azure SQL Data WarehouseAzure SQL Data Warehouse und Parallel Data WarehouseParallel Data Warehouse.Examples: Azure SQL Data WarehouseAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

In den folgenden Beispielen wird die Verwendung der Operatoren INTERSECT und EXCEPT veranschaulicht.The following examples show how to use the INTERSECT and EXCEPT operators. Die erste Abfrage gibt alle Werte aus der FactInternetSales-Tabelle zum Vergleich mit den Ergebnissen mit INTERSECT und EXCEPT zurück.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  

Die folgende Abfrage gibt alle eindeutigen Werte zurück, die von den Abfragen auf der linken und rechten Seite des INTERSECT-Operators zurückgegeben werden.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.)  

Die folgende Abfrage gibt alle eindeutigen Werte aus der Abfrage links vom EXCEPT-Operator zurück, die nicht auch in der rechten Abfrage gefunden werden.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.)