Mengenoperatoren – EXCEPT und INTERSECT (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Gibt durch den Vergleich zweier Abfragen eindeutige Zeilen zurück.

EXCEPT gibt eindeutige Zeilen aus der linken Eingabeabfrage zurück, die nicht von der rechten Eingabeabfrage ausgegeben werden.

INTERSECT gibt eindeutige Zeilen zurück, die durch die linken und rechten Eingabeabfragen ausgegeben werden.

Für das Kombinieren der Resultsets zweier Abfragen, die EXCEPT oder INTERSECT verwenden, gelten folgende Grundregeln:

• Die Anzahl und die Reihenfolge der Spalten müssen für alle Abfragen identisch sein.

• Die Datentypen müssen kompatibel sein.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

<Abfragespezifikation> | ( <Abfrageausdruck> )
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. Die Definitionen der Spalten, die Bestandteil eines EXCEPT- oder INTERSECT-Vorgangs sind, müssen nicht identisch sein. Aber sie müssen mithilfe impliziter Konvertierung vergleichbar sein. Wenn sich die Datentypen unterscheiden, bestimmen die Regeln für die Rangfolge der Datentypen den für den Vergleich verwendeten Datentyp.

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. Weitere Informationen finden Sie unter Genauigkeit, Dezimalstellen und Länge (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.

EXCEPT
Gibt alle eindeutigen Werte aus der Abfrage links vom EXCEPT-Operator zurück. Diese Werte werden zurückgeben, solange die rechte Abfrage diese Werte nicht auch zurückgibt.

INTERSECT
Gibt alle eindeutigen Werte zurück, die von den Abfragen auf der linken und rechten Seite des INTERSECT-Operators zurückgegeben werden.

Hinweise

Die Datentypen vergleichbarer Spalten werden von den Abfragen links und rechts vom EXCEPT- oder INTERSECT-Operator zurückgegeben. Diese Datentypen können Zeichendatentypen mit unterschiedlichen Sortierungen enthalten. Wenn dies der Fall, wird der erforderliche Vergleich gemäß den Regeln der Rangfolge von Sortierungen ausgeführt. Wenn Sie diese Konvertierung nicht ausführen können, gibt die SQL Server-Datenbank-Engine einen Fehler zurück.

Wenn Sie zum Bestimmen EINDEUTIGER Zeilen Spaltenwerte vergleichen, werden zwei NULL-Werte als identisch betrachtet.

EXCEPT und INTERSECT geben die Spaltennamen des Resultsets zurück, die mit den Spaltennamen identisch sind, die die Abfrage links vom Operator zurückgibt.

Spaltennamen oder -aliasse in ORDER BY-Klauseln müssen auf Spaltennamen verweisen, die von der linken Abfrage zurückgegeben werden.

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.

Wenn EXCEPT oder INTERSECT zusammen mit anderen Operatoren in einem Ausdruck verwendet wird, wird dieser in der folgenden Rangfolge ausgewertet:

1. Ausdrücke in Klammern

2. Der INTERSECT-Operator

3. EXCEPT und UNION werden auf der Grundlage ihrer Position im Ausdruck von links nach rechts ausgewertet.

Sie können mit EXCEPT oder INTERSECT mehr als zwei Sätze von Abfragen vergleichen. Wenn Sie dies tun, wird die Datentypkonvertierung bestimmt, indem zwei Abfragen nacheinander verglichen werden. Dies erfolgt gemäß der zuvor erwähnten Regeln der Ausdrucksauswertung.

EXCEPT und INTERSECT können nicht in verteilten partitionierten Sichtdefinitionen und Abfragebenachrichtigungen verwendet werden.

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. Daher kann sich das Verwenden von EXCEPT und INTERSECT in verteilten Abfragen auf die Leistung auswirken.

Sie können Vorwärtscursor und statische Cursor im Resultset verwenden, wenn sie mit einem EXCEPT- oder INTERSECT-Vorgang verwendet werden. Sie können auch einen keysetgesteuerten oder dynamischen Cursor zusammen mit einem EXCEPT- oder INTERSECT-Vorgang verwenden. Wenn Sie dies tun, wird der Cursor des Resultsets des Vorgangs in einen statischen Cursor konvertiert.

Wenn ein EXCEPT-Vorgang mithilfe dem Feature des grafischen Showplans von SQL Server Management Studio angezeigt wird, wird der Vorgang als Left Anti Semi Join angezeigt, und ein INTERSECT-Vorgang wird als Left Semi Join angezeigt.

Beispiele

In den folgenden Beispielen wird die Verwendung der Operatoren INTERSECT und EXCEPT veranschaulicht. Die erste Abfrage gibt alle Werte aus der Production.Product-Tabelle zum Vergleich mit den Ergebnissen mit INTERSECT und EXCEPT zurück.

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

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

-- 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. Die Tabellen sind die Umkehrung des vorherigen Beispiels.

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

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

In den folgenden Beispielen wird die Verwendung der Operatoren INTERSECT und EXCEPT veranschaulicht. Die erste Abfrage gibt alle Werte aus der FactInternetSales-Tabelle zum Vergleich mit den Ergebnissen mit INTERSECT und EXCEPT zurück.

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

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

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