Was sind eigentlich… SET-Operatoren? UNION, INTERSECT und EXCEPT mit Beispielen

Als SQL-Einsteiger kommt man recht schnell an den Punkt, wo man Informationen aus mehr als einer Tabelle abfragen möchte, und dafür lernen wir zunächst einmal JOINS. Dabei geht es aber darum, Verknüpfungen über übereinstimmende Felder zu erstellen und die Ergebnismenge über die Art der Verknüpfung einzuschränken.

Manchmal wollen wir jedoch nicht notwendigerweise zusammenhängende Daten miteinander vergleichen oder listenartig ausgeben – und dafür verwenden wir die Set-Operatoren.

SET-Operatoren in T-SQL sind UNION, UNION ALL, INTERSECT und EXCEPT.

Wenn jemand schon einmal von einem MINUS-Operator gehört hat: stimmt, den gibt es auch, aber nicht bei uns in SQL Server. MINUS erfüllt beispielsweise in Oracle die gleiche Funktion wie EXCEPT in den meisten anderen großen Datenbanksystemen.

Die Grundfunktionen sind schnell erklärt:

Mit UNION können wir Ergebnisse aus zwei (theoretisch) voneinander unabhängigen SELECT-Statements untereinander ausgeben (dazu gleich mehr), INTERSECT zeigt uns, welche Übereinstimmungen es in den beiden Abfragen gibt und EXCEPT sagt uns, wo die Unterschiede liegen. Das müssen wir uns selbstverständlich noch etwas näher ansehen.

 

UNION und UNION ALL

 

Wir wollen beispielsweise eine Liste aller Kontaktpersonen und deren Telefonnummern ausgeben. Für dieses erste Beispiel verwenden wir die Northwind Beispieldatenbank.

Kontaktpersonen gibt es in der Customers- und in der Suppliers-Tabelle.

 

Mit einem JOIN würden wir keine Liste der Kontaktpersonen bekommen, sondern vier Spalten, jeweils eine für ContactName und Phone aus der Customers-Tabelle und jeweils eine für ContactName und Phone aus der Suppliers Tabelle. Weiters hätten wir mit einem INNER JOIN das Problem, dass nur die Kunden, die schon etwas bestellt, und nur die Supplier, die schon etwas geliefert haben, in unserer Übersicht aufscheinen würden. Und wir müssten fünf Tabellen miteinander verjoinen, von denen wir die meisten gar nicht brauchen. Also keine gute Idee.

UNION kann das für uns erledigen:

Wir machen ein SELECT-Statement für die Informationen, die uns an der Customers-Tabelle interessieren, und das Gleiche für die Suppliers-Tabelle.

Beides wird mit dem UNION-Operator verknüpft:


SELECT       ContactName

           , Phone

FROM Customers

UNION

SELECT       ContactName

           , Phone

FROM Suppliers

 

Wir erhalten eine Spalte mit allen ContactNames und eine mit allen Telefonnummern, unabhängig von der Tabelle, aus der sie stammen.

Dabei wird die Spaltenüberschrift aus der ersten Abfrage beibehalten. Wir dürfen hier auch ein ALIAS vergeben. Wären die Spaltenüberschriften in der zweiten Abfrage anders, würden sie die ersten nicht überschreiben.

 

Ein paar Dinge gibt es zu beachten:

  1. Wir müssen in allen beteiligten SELECT-Statements die gleiche Anzahl an Spalten verwenden, wobei wir allerdings fehlende Spalten mit NULL oder mit eigenem Text auffüllen dürften.
  2. In eine Spalte dürfen nur gleiche oder implizit konvertierbare Datentypen. Selbstverständlich können wir auch in die entsprechenden Datentypen konvertieren, wenn wir das aus irgendeinem Grund unbedingt so wollen.
  3. UNION macht auch ein DISTINCT; Mehrfacheinträge werden also nicht ausgegeben.
  4. ORDER BY kann nicht vor dem UNION-Befehl kommen. Wir können aber das Gesamtergebnis nach einer bestimmten Spalte ordnen.

 

Zu Punkt 1: Gleiche Anzahl an Spalten

Das funktioniert nicht:


SELECT       CustomerID

           , ContactName

           , Phone

FROM Customers

UNION

SELECT       ContactName

           , Phone

FROM Suppliers


Im oberen SELECT-Statement werden drei, im unteren nur zwei Spalten abgefragt. Wir bekommen eine Fehlermeldung.

Folgendes wäre möglich:

 

SELECT       CustomerID

           , ContactName

           , Phone

FROM Customers

UNION

SELECT       NULL

           , ContactName

           , Phone

FROM Suppliers


Dabei müssten wir aber von Fall zu Fall die Sinnhaftigkeit, eine Spalte mit NULL aufzufüllen, abwägen.

 

Zu Punkt 2: gleiche Datentypen

Das funktioniert nicht:

SELECT       CustomerID

           , ContactName

           , Phone

FROM Customers

UNION

SELECT       SupplierID

           , ContactName

           , Phone

FROM Suppliers


Wir haben zwar nun die gleiche Anzahl der Spalten und auf den ersten Blick sieht alles gut aus, allerdings ist im Falle der Northwind Datenbank die CustomerID ein nchar(5), die SupplierID hingegen ein int. Somit dürfen die beiden nicht in derselben Spalte stehen, es sei denn, wir würden die SupplierID explizit in einen String-Datentyp konvertieren.

 

Zu Punkt 3: UNION macht ein DISTINCT

Wenn wir eine Liste von irgendetwas ausgeben wollen, sind Mehrfacheinträge oft unerwünscht. UNION verhindert diese; wenn wir Mehrfacheinträge aber angezeigt bekommen möchten, verwenden wir statt UNION ein UNION ALL.

Wenn wir in einem konkreten Fall 100%ig sicher sein können, dass es dort keine Mehrfacheinträge gibt, können wir auch mit UNION ALL arbeiten, wenn wir keine Mehrfacheinträge wollen, denn UNION ALL ist etwas schneller. UNION muss ja im Hintergrund für uns noch eine Überprüfung anstellen, ob Mehrfacheinträge vorhanden sind und diese dann ausschließen. Dieser Arbeitsschritt fällt bei UNION ALL weg.

 

Zu Punkt 4: UNION und ORDER BY

Wollen wir unser Ergebnis nach einer bestimmten Spalte ordnen, können wir das tun, aber das ORDER BY gilt für alles, was mit dem UNION- oder UNION ALL-Operator verknüpft wurde. Nur im oberen Teil ein ORDER BY oder im ersten und zweiten SELECT-Statement nach einer anderen Reihenfolge sortiert funktioniert nur unter Zuhilfenahme von temporären Tabellen oder Subselect (Subquery, Unterabfrage).

So dürfte ein ORDER BY mit UNION aussehen:


SELECT       ContactName

           , Phone

FROM Customers

UNION

SELECT       ContactName

           , Phone

FROM Suppliers

ORDER BY ContactName


Damit ordnen wir alphabetisch nach Kontaktnamen.

 

An einem stark vereinfachten Beispiel können wir am besten sehen, wie UNION, UNION ALL, INTERSECT und EXCEPT funktionieren:

 

Wir erstellen eine Mini-Test-Datenbank mit zwei „Tabellen“ mit jeweils einer Spalte:


CREATE DATABASE Demo

 

USE Demo

 

 

CREATE TABLE A (Spalte1 int)

 

CREATE TABLE B (Spalte1 int)

 

Diese beiden Tabellen befüllen wir mit ein paar Werten:


INSERT INTO A (Spalte1)

VALUES (NULL), (1), (2), (1)

 

INSERT INTO B (Spalte1)

VALUES (NULL), (1), (3), (1)

 

In unserer Tabelle A befinden sich jetzt die Werte 1, 2, nochmals 1 und NULL.

In unserer Tabelle B befinden sich jetzt die Werte 1, 3, nochmals 1 und NULL.

Wir können also das Verhalten der Se-Operatoren bei Mehrfacheinträgen, unterschiedlichen Werten, gleichen Werten und leeren Werten testen.

 

Wie verhält sich UNION?


SELECT *

FROM A

UNION

SELECT *

FROM B


Unser Ergebnis zeigt:

 


Wir erhalten alle Einträge aus der (einzigen) Spalte unserer Tabellen, NULL wird ausgegeben, und da UNION auch ein DISTINCT macht, wird NULL und 1 nur einmal angezeigt.

 

Wie verhält sich UNION ALL?


SELECT *

FROM A

UNION ALL

SELECT *

FROM B

 

Unser Ergebnis zeigt:

 


Alles wird ausgegeben, auch wenn ein Wert schon irgendwo vorkommt. Der Wert 1 steht sogar viermal da, denn er steht zweimal in Tabelle A und zweimal in Tabelle B.

 

Wie verhält sich INTERSECT?


SELECT *

FROM A

INTERSECT

SELECT *

FROM B

 

Unser Ergebnis zeigt:

 


INTERSECT zeigt uns nur die Werte (oder auch NULL), die in beiden Tabellen vorkommen. Auch hier wird ein DISTINCT gemacht. Und die Werte 2 und 3, die jeweils nur in einer Tabelle vorkommen, werden nicht ausgegeben.

 

Wie verhält sich EXCEPT?


SELECT *

FROM A

EXCEPT

SELECT *

FROM B

 

Unser Ergebnis zeigt:

 


EXCEPT zeigt uns das, was in Tabelle A, aber nicht in Tabelle B vorkommt. Wollen wir wissen, was in Tabelle B, aber nicht in Tabelle A vorkommt, drehen wir einfach die Reihenfolge der Abfrage um.

 

Das und noch viel mehr gibt es auch in unseren Kursen zumThema SQL – inzwischen wünsche ich viel Spaß beim Ausprobieren!

 


Kommentare sind geschlossen