EXCEPT und INTERSECT (Transact-SQL)

Gibt beim Vergleich der Ergebnisse zweier Abfragen unterschiedliche Werte zurück.

EXCEPT gibt alle unterschiedlichen Werte aus der linken Abfrage zurück. Diese Werte werden nicht in der rechten Abfrage gefunden.

INTERSECT gibt alle unterschiedlichen Werte zurück, die sowohl von der linken als auch von der rechten Abfrage des INTERSECT-Operanden zurückgegeben 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.

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
  Gibt alle unterschiedlichen Werte aus der linken Abfrage an den EXCEPT-Operanden zurück; diese Werte werden nicht von der rechten Abfrage zurückgegeben.

• INTERSECT
  Gibt alle unterschiedlichen Werte zurück, die sowohl von der linken als auch von der rechten Abfrage des INTERSECT-Operanden zurückgegeben werden.

Hinweise

Wenn die Datentypen der vergleichbaren Spalten, die von der linken und rechten Abfrage des EXCEPT- oder INTERSECT-Operanden 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 Database Engine (Datenbankmodul) einen Fehler zurück.

Wenn Sie zum Bestimmen unterschiedlicher Werte Zeilen 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 Operanden 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 Operanden 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-Operand

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, in Abfragebenachrichtigungen oder zusammen mit COMPUTE- und COMPUTE BY-Klauseln 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 (linke Antisemiverknüpfung) angezeigt, und ein INTERSECT-Vorgang wird als left semi join (linke Semiverknüpfung) angezeigt.

Beispiele

In den folgenden Beispielen wird die Verwendung der Operanden 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 AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product ;
--Result: 504 Rows

Die folgende Abfrage gibt alle unterschiedlichen Werte zurück, die sowohl von der linken als auch von der rechten Abfrage des INTERSECT-Operanden zurückgegeben werden.

USE AdventureWorks2008R2;
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 unterschiedlichen Werte aus der linken Abfrage des EXCEPT-Operanden zurück. Diese Werte werden nicht in der rechten Abfrage gefunden.

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

Die folgende Abfrage gibt alle unterschiedlichen Werte aus der linken Abfrage des EXCEPT-Operanden zurück. Diese Werte werden nicht in der rechten Abfrage gefunden. Die Tabellen sind die Umkehrung des vorherigen Beispiels.

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

Siehe auch

Konzepte

Ausführen von Semi-Join-Vorgängen mit EXCEPT und INTERSECT

Verwenden von UNION, EXCEPT und INTERSECT mit anderen Transact-SQL-Anweisungen