Ein Join ist nicht genug

Für viele Aufgabenstellungen reichen einfache Joins aus, um die Verknüpfung von Tabellen zu definieren. Manche lassen sich jedoch so nicht formulieren.

In Pocket speichern vorlesen Druckansicht 62 Kommentare lesen
Lesezeit: 7 Min.
Von
  • Peter Gulutzan
Inhaltsverzeichnis

Joins sind das Mittel der Wahl, um in SQL Werte aus mehreren Tabellen abzufragen:

SELECT vorname, name, summe FROM 
kunden k, auftraege a
WHERE a.kunden_id=k.kunden_id AND
k.kunden_id=5;

beispielsweise findet alle Aufträge für die Kundennummer 5. Nicht jede Art von Information lässt sich jedoch so beschaffen. Geht es etwa darum, den Kunden mit der größten Auftragssumme zu finden, scheitert der naive Ansatz

SELECT vorname, name, summe FROM
kunden k, auftraege a
WHERE a.kunden_id=k.kunden_id AND
a.summe=MAX(a.summe);

Statt des gewünschten Ergebnisses bekommt man eine Fehlermeldung, denn die Gruppierungsfunktion MAX ist an dieser Stelle nicht erlaubt.

Abhilfe schaffen so genannte Unterabfragen (subquery). Ihre Verwendung illustrieren im Folgenden einige Beispiele, für die zunächst zwei Tabellen anzulegen und mit Daten zu füllen sind:

CREATE TABLE Kunden (
name CHAR(5), waggon_id INT);
CREATE TABLE Waggons (
waggon_id INT PRIMARY KEY,
Klasse CHAR(5));
INSERT INTO Kunden VALUES ('Klaus',15);
INSERT INTO Kunden VALUES ('Rudi',23);
INSERT INTO Waggons VALUES (15,'erste');

Zum Ausprobieren eignen sich gängige freie (etwa MySQL ab 4.1.0) und kommerzielle Datenbanken (Oracle et cetera).

Alle Unterabfragen haben zwei Merkmale: sie stehen in Klammern und sind Teil eines übergeordneten SELECT. Zum Beispiel findet

SELECT name FROM Kunden 
WHERE waggon_id = /* Haupt */
(SELECT waggon_id FROM Waggons
WHERE klasse = 'erste') /* Unter */;

alle Kunden in einem Waggon erster Klasse - also lediglich Klaus. Die Datenbank führt zunächst die Unterabfrage durch, die als Ergebnismenge genau ein Element mit dem Wert 15 liefert. Die Hauptabfrage ist damit äquivalent zu:

SELECT name FROM Kunden 
WHERE waggon_id = 15;

Diese spezielle Unterabfrage ließe sich mit einem herkömmlichen Join übrigens kürzer schreiben:

SELECT name FROM Kunden k, Waggons w
WHERE k.waggon_id=w.waggon_id AND
w.klasse='erste';

Zumindest in diesem Fall läuft die Variante mit Unterabfrage in MySQL schneller als der klassische Join, wenn viele Duplikate vorhanden sind.

Als Join lässt sich das nächste Beispiel nicht formulieren:

SELECT name FROM Kunden WHERE waggon_id =
(SELECT MAX(waggon_id) FROM Kunden);

Auf deutsch: „Welcher Kunde hat die höchste Waggonnummer?“ Verwirrung entsteht, wenn die Unterabfrage keine Datensätze liefert. Zum Beispiel:

SELECT * FROM Kunden 
WHERE waggon_id =
(SELECT waggon_id FROM Waggons
WHERE Klasse = 'zweite');

Wäre der zweite Teil eine gewöhnliche Abfrage, lieferte die Datenbank eine leere Menge. Die jedoch lässt sich nicht mit dem Inhalt einer Spalte vergleichen. Deshalb schreibt der SQL-Standard vor, dass in diesem Fall NULL zu liefern ist, und der Vergleich damit scheitert in jedem Fall. Deshalb gibt die äußere WHERE-Klausel die Antwort „0 Datensätze gefunden“.

Eine andere Schwierigkeit entsteht scheinbar, wenn die Ergebnismenge der Unterabfrage zu viele Datensätze beinhaltet. Zum Beispiel:

SELECT * FROM Waggons 
WHERE waggon_id <
(SELECT waggon_id FROM Kunden);

Es gibt zwei Kunden mit waggon_id >= 15, und der Vergleich eines Wertes mit zwei Werten ist nicht möglich. In dieser Situation liefert die Datenbank einen Fehler zurück, da das Ergebnis der Subquery aus mehr als einem Wert besteht. Zwei Lösungen stehen bereit. Die erste ist der Vergleich mit „irgendeinem“ Wert:

SELECT * FROM Waggons                
WHERE waggon_id <ANY
(SELECT waggon_id FROM Kunden);

In diesem Fall ist der Vergleich für den ersten Kunden falsch (15 < 15), aber wahr für den zweiten (15 < 23), sodass die Unterabfrage „wahr“ ist. Statt =ANY lässt sich IN verwenden, und SOME bedeutet dasselbe wie ANY. Die angekündigte zweite Lösung ist der Vergleich mit jedem von der Unterabfrage gelieferten Wert:

SELECT * FROM Waggons               
WHERE waggon_id < ALL
(SELECT waggon_id FROM Kunden);

Diesmal ergibt der Vergleich für den ersten Kunden einen falschen Wert (15 < 15). Damit kann ALL nur noch falsch sein, sodass der gesamte Ausdruck eine leere Ergebnismenge liefert. Das vom SQL-Standard Geforderte bei der Kombination von ALL und ANY mit leeren Unterabfragen ist zumindest gewöhnungsbedürftig: „ANY < (Unterabfrage)“ liefert in dieser Situation falsch, „ALL < (Unterabfrage)“ jedoch wahr.

Geht es lediglich darum, ob eine Unterabfrage überhaupt Werte liefert, bietet sich die Verwendung des [NOT] EXISTS Prädikats an. EXISTS ist wahr, wenn das Ergebnis der Subquery mindestens einen Datensatz enthält. Das triviale Beispiel

SELECT * FROM Kunden WHERE 
EXISTS (SELECT * FROM Waggons);

wählt alle Datensätze aus der Tabelle Kunden aus. Die WHERE-Klausel in dem obigen Beispiel ist nur deshalb wahr, weil die Tabelle Waggons nicht leer ist.

Aber normalerweise ist [NOT] EXISTS komplizierter. Man setzt die Werte in jedem Satz der Unterabfrage mit Werten der Hauptabfrage zueinander in Beziehung. Zum Beispiel:

SELECT * FROM Waggons W1
WHERE NOT EXISTS (
SELECT * FROM Kunden K2
WHERE NOT EXISTS (
SELECT * FROM Waggons W3
WHERE W3.waggon_id = K2.waggon_id
AND W3.waggon_id = W1.waggon_id));

Damit lässt sich nach dem Waggon fragen, in dem alle Kunden fahren. Rudi ist in Wagen 15, Klaus in 23. Es gibt jedoch in der Tabelle Waggons keinen Waggon 23. Das heißt, es gibt einen Kunden (Klaus) ohne Waggon. Das innere NOT EXISTS ist deshalb für Klaus immer wahr. Also gibt es für Waggon 15 einen Kunden (Klaus) der nicht im Waggon sitzt; das äußere NOT EXISTS ist deshalb falsch. Da keine andere Waggons existieren, ist das Ergebnis hier leer.

SQL-99 erlaubt einige weitere Einsatzmöglichkeiten für Unterabfragen. So können sie mehrere Spalten vergleichen wie in

SELECT ROW ('Klaus',15) =
(SELECT name,waggon_id FROM Kunden
WHERE waggon_id <> 23);

Diese Unterabfrage liefert eine Zeile (row) mit den Werten „Klaus“ und „15“, sodass die Abfrage auf Gleichheit im übergeordneten SELECT wahr (beziehungsweise 1) ergibt. Subqueries lassen sich sogar statt einer Tabelle im FROM-Teil einer Abfrage verwenden:

SELECT * FROM 
(SELECT * FROM Kunden
WHERE name like 'K%') AS x;

In diesem Fall erzeugt die Unterabfrage eine eigene Tabelle, aus der das übergeordnete SELECT die gewünschten Daten auswählt. Die Klausel AS x ist in diesem Fall obligatorisch. Schließlich und endlich können Subqueries in INSERT- oder UPDATE-Kommandos zur Bestimmung der Spaltenwerte dienen:

UPDATE Waggons 
SET waggon_id =
(SELECT AVG(waggon_id) FROM Kunden);

Mit diesem Befehl setzt man die Wagennummer für alle Datensätze in Waggons auf 19.

Peter Gulutzan
ist Mitverfasser des Buches „SQL-99 Complete, Really“. Er arbeitet für MySQL AB als Softwarearchitekt und lebt in Edmonton, Kanada.

Mehr Infos

MySQL-Besonderheiten

Zumindest seine Fans wissen MySQLs gelegentlich großzügige Interpretation des SQL99-Standards zu schätzen. Auch bei Unterabfragen unterstützt es ungewöhnliche Konstruktionen:

Eine Subquery kann LIMIT, ORDER BY, und sogar UNION enthalten:

SELECT * FROM Kunden
WHERE name > (
SELECT klasse from Waggons
WHERE waggon_id = 15
UNION ALL
SELECT klasse FROM Waggons
WHERE waggon_id = 4
ORDER BY klasse);

Sie darf auf der linken Seite einer Vergleichsbedingung und in jeder Klausel eines SELECT erscheinen:

SELECT (SELECT 1),Waggons.waggon_id +
(SELECT waggon_id FROM Waggons) AS spalte2
FROM Kunden, Waggons
WHERE (SELECT name FROM Kunden
WHERE waggon_id = 15) > Klasse
ORDER BY spalte2;

(ck)