EXCEPT und INTERSECT (Transact-SQL)

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.

Die grundlegenden Regeln für das Kombinieren der Resultsets zweier Abfragen, die EXCEPT oder INTERSECT verwenden, sind die folgenden:

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

• Die Datentypen müssen kompatibel sein.

Gilt für: SQL Server (SQL Server 2008 bis aktuelle Version), Azure SQL-Datenbank.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

• <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. Die Definitionen der Spalten, die Bestandteil eines EXCEPT- oder INTERSECT-Vorgangs sind, müssen nicht identisch, jedoch mithilfe impliziter Konvertierung vergleichbar sein. Wenn sich die Datentypen unterscheiden, basiert der für den Vergleich und das Zurückgeben von Ergebnissen verwendete Typ auf den Regeln für die Rangfolge der Datentypen.

  Wenn die Typen identisch sind, diese sich aber in der Genauigkeit, Dezimalstellenanzahl oder Länge unterscheiden, wird das Ergebnis basierend auf denselben Regeln wie für das Kombinieren von Ausdrücken bestimmt. 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äre CLR-benutzerdefinierte Typspalten zurückgeben, da diese Datentypen nicht vergleichbar sind.

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

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

Hinweise

Wenn die Datentypen vergleichbarer Spalten, die von den Abfragen links und rechts von den EXCEPT- oder INTERSECT-Operatoren zurückgegeben werden, Zeichendatentypen mit unterschiedlichen Sortierungen sind, wird der erforderliche Vergleich gemäß den Regeln der Rangfolge von Sortierungen ausgeführt. Wenn diese Konvertierung nicht ausgeführt werden kann, gibt SQL Server-Datenbankmodul einen Fehler zurück.

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

Die Spaltennamen des Resultsets, die von EXCEPT oder INTERSECT zurückgegeben werden, sind mit den Namen identisch, die von der linken Abfrage des Operators zurückgegeben wurden.

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.

Wenn EXCEPT oder INTERSECT verwendet wird, um mehr als zwei Sätze von Abfragen zu vergleichen, 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.

Vorwärtscursor und statische Cursor werden im Resultset vollständig unterstützt, wenn Sie mit einem EXCEPT- oder INTERSECT-Vorgang verwendet werden. Wenn ein keysetgesteuerter oder dynamischer Cursor zusammen mit einem EXCEPT- oder INTERSECT-Vorgang verwendet wird, wird der Cursor des Resultsets des Vorgangs in einen statischen Cursor konvertiert.

Wenn ein EXCEPT-Vorgang mithilfe der Funktion des grafischen Showplans von SQL Server Management Studio angezeigt wird, wird der Vorgang als left anti semi join (linker Antisemijoin) angezeigt, und ein INTERSECT-Vorgang wird als left semi join (linker Semijoin) 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.

USE AdventureWorks2012;
GO
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.

USE AdventureWorks2012;
GO
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 linken Abfrage des EXCEPT-Operators zurück, die nicht in der rechten Abfrage gefunden werden.

USE AdventureWorks2012;
GO
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 linken Abfrage des EXCEPT-Operators zurück, die nicht in der rechten Abfrage gefunden werden. Die Tabellen sind die Umkehrung des vorherigen Beispiels.

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