SQL JOIN: Tabellen sicher verknüpfen

Eine Tabelle enthält Zeilen für einzelne Datensätze und Spalten für deren Eigenschaften. SQL ist eine Sprache, mit der du solche Daten abfragen kannst. Betrachte diese zwei fiktiven Beispieltabellen:

Mitarbeiter

Kunde

Ein Primärschlüssel kennzeichnet jede Zeile eindeutig und enthält keine NULL-Werte, also keine fehlenden Werte. Hier übernehmen MitarbeiterID und KundeID diese Rolle. Ein Fremdschlüssel verweist auf einen Schlüssel der zugehörigen Tabelle; in diesem Beispiel ist das Bedient.

Die Spalte Mitarbeiter.Bedient verweist auf Kunde.KundeID. Deshalb lautet die passende Bedingung:

Mitarbeiter.Bedient = Kunde.KundeID

Der Tabellenname vor dem Punkt macht eine Spalte eindeutig. Kürzere Abfragen verwenden häufig Aliasse:

FROM Mitarbeiter AS M JOIN Kunde AS K ON M.Bedient = K.KundeID

M und K sind nur Ersatznamen innerhalb dieser Abfrage. Sie verändern die Tabellen nicht. Ein Ausschnitt, der mit FROM beginnt, ist noch keine vollständige Abfrage: Davor gehört eine SELECT-Liste, die die auszugebenden Spalten nennt.

Die folgenden Ergebnistabellen zeigen die enthaltenen Zeilen. Ihre Reihenfolge ist ohne eine ausdrückliche Sortierung mit ORDER BY nicht garantiert.

Ein INNER JOIN übernimmt nur Zeilenpaare, für welche die Join-Bedingung erfüllt ist. JOIN und INNER JOIN bedeuten hier dasselbe.

SQL kennzeichnet fehlende oder unbekannte Werte mit NULL. Enthält einer der beiden verglichenen Werte NULL, ergibt ein normaler Vergleich mit = den Wahrheitswert „unbekannt“. Auch NULL = NULL ist nicht wahr. Für einen Treffer muss die Join-Bedingung aber wahr sein; in unserem Gleichheitsjoin entsteht deshalb kein passendes Paar.

Eine Zeile kann mehrere passende Partner haben. Deshalb erscheint Lena hier zweimal: einmal mit Peter und einmal mit Emira. Ein Join liefert eine Ergebniszeile pro passendem Paar.

Outer Joins ergänzen das Ergebnis eines Inner Joins um nicht passende Zeilen. Die fehlenden Werte der jeweils anderen Tabelle werden dabei NULL. Diese Erhaltung gilt für das Join-Ergebnis; eine zusätzliche WHERE-Bedingung kann danach wieder Zeilen herausfiltern.

OUTER kann bei LEFT, RIGHT und FULL weggelassen werden. So bedeuten LEFT JOIN und LEFT OUTER JOIN dasselbe.

LEFT JOIN

SELECT M.Mitarbeitername, K.Kundenname FROM Mitarbeiter AS M LEFT JOIN Kunde AS K ON M.Bedient = K.KundeID;

Alle Mitarbeiter bleiben erhalten. In den gegebenen Daten haben beide einen passenden Kunden. Deshalb sieht das Ergebnis genauso aus wie beim INNER JOIN:

Das gleiche Ergebnis bedeutet hier nicht die gleiche Operation. Eine Mitarbeiterzeile ohne Partner würde nur beim LEFT JOIN zusätzlich erscheinen.

RIGHT JOIN

SELECT M.Mitarbeitername, K.Kundenname FROM Mitarbeiter AS M RIGHT JOIN Kunde AS K ON M.Bedient = K.KundeID;

Nun bleiben alle Kunden erhalten. Amir besitzt keinen passenden Mitarbeiter und erscheint deshalb mit NULL:

FULL OUTER JOIN

SELECT M.Mitarbeitername, K.Kundenname FROM Mitarbeiter AS M FULL OUTER JOIN Kunde AS K ON M.Bedient = K.KundeID;

Ein FULL OUTER JOIN erhält nicht passende Zeilen beider Tabellen. Mit den vorliegenden Daten entspricht sein Ergebnis dem RIGHT JOIN, weil es zwar einen Kunden ohne Mitarbeiter, aber keinen Mitarbeiter ohne Kunden gibt.

Übung: Eine Mitarbeiterin ohne Kunden

Ergänze nur für diese Übung die Mitarbeiterzeile (3, Lina, NULL). Die Kundentabelle bleibt unverändert. Welche Zeilen liefern die obigen LEFT- und FULL-JOIN-Abfragen jetzt? Überlege zuerst selbst.

Lösung: Peter und Emira passen weiterhin zu Lena. Für Lina liefert NULL = KundeID keinen Treffer. Der LEFT JOIN erhält Lina dennoch und ergänzt ihre Kundenspalte mit NULL:

Der FULL OUTER JOIN enthält zusätzlich die Zeile NULL | Amir, weil er auch den Kunden ohne Mitarbeiter erhält. In den folgenden Abschnitten gelten wieder die ursprünglichen zwei Mitarbeiterzeilen.

Ein CROSS JOIN kombiniert jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle. Er benötigt keine ON-Bedingung.

SELECT M.Mitarbeitername, K.Kundenname FROM Mitarbeiter AS M CROSS JOIN Kunde AS K;

Bei zwei Mitarbeiter- und zwei Kundenzeilen entstehen $2 \cdot 2 = 4$ Kombinationen:

Allgemein erzeugen Tabellen mit $m$ und $n$ Zeilen genau $m \cdot n$ Kombinationen.

Für einen inneren Join kannst du die verglichenen Spalten unterschiedlich festlegen.

ON ist besonders eindeutig

ON erlaubt gleich oder unterschiedlich benannte Spalten sowie allgemeine Vergleichsbedingungen:

FROM Mitarbeiter AS M JOIN Kunde AS K ON M.Bedient = K.KundeID

Ein Join, dessen Bedingung nur Gleichheitsvergleiche verwendet, heißt Equijoin. Der allgemeinere Theta-Join erlaubt auch andere Vergleichsbedingungen, etwa <=. Ein Equijoin ist damit ein Spezialfall eines Theta-Joins.

USING nennt eine gemeinsame Spalte

Haben beide Tabellen eine gleichnamige Join-Spalte, kannst du sie ausdrücklich angeben:

FROM Mitarbeiter JOIN Kunde USING (KundeID)

Dafür müsste die Verweisspalte in Mitarbeiter ebenfalls KundeID heißen. Bei SELECT *, also der Ausgabe aller Spalten, erscheint die gemeinsame Join-Spalte im Ergebnis nur einmal.

NATURAL JOIN wählt automatisch

Ein NATURAL JOIN vergleicht alle gleichnamigen Spalten beider Tabellen. Im bisherigen Beispiel heißen die zusammengehörigen Spalten jedoch Bedient und KundeID. Mit diesen Spaltennamen drückt ein NATURAL JOIN die gewünschte Zuordnung deshalb nicht aus. Weil diese beiden Tabellen gar keine gleichnamigen Spalten haben, verhält er sich hier sogar wie ein CROSS JOIN.

Würde Bedient in der Tabelle Mitarbeiter ebenfalls KundeID heißen, wäre folgende Abfrage möglich:

FROM Mitarbeiter NATURAL JOIN Kunde

Sie würde KundeID automatisch als Join-Spalte verwenden. Das kann bequem, aber riskant sein. Kommt später in beiden Tabellen eine weitere gleichnamige Spalte hinzu, wird auch sie automatisch Teil der Join-Bedingung. Die Bedeutung der Abfrage kann sich dadurch unbeabsichtigt ändern.

Die gezeigten Join-Formen sind beispielsweise in PostgreSQL möglich. Andere Datenbanksysteme unterstützen nicht jede Form: SQL Server bietet weder NATURAL JOIN noch JOIN ... USING; MySQL 8.4 bietet keinen FULL OUTER JOIN. Eine ausdrückliche ON-Bedingung ist oft gut übertragbar, ersetzt aber keinen fehlenden Join-Typ.

Ein Self Join verknüpft eine Tabelle mit sich selbst. Zwei Aliasse unterscheiden die beiden Rollen derselben Tabelle.

Gegeben ist die fiktive Tabelle Personen:

Die erste Tabellenrolle steht für die Person, die zweite für ihren Partner:

SELECT P.Name AS Person, T.Name AS Partner FROM Personen AS P INNER JOIN Personen AS T ON P.TeampartnerID = T.PersonenID;

Peter fehlt, weil seine TeampartnerID NULL ist. Die Paare erscheinen in beiden Richtungen, weil die Ausgangstabelle beide gegenseitigen Verweise enthält.

Mehr als zwei Tabellen verknüpfen

Das Ergebnis eines Joins kann mit einer weiteren Tabelle verknüpft werden. Für ein weiteres fiktives Beispiel hat Mitarbeiter die Spalten MitarbeiterID, Mitarbeitername, AdrId und AbtId. Dazu kommen Adresse(AdrId, Strasse) und Abteilung(AbtId, Abteilungsname). AdrId verweist auf die Adresse, AbtId auf die Abteilung. Dann ist folgende Kette möglich:

FROM Mitarbeiter JOIN Adresse USING (AdrId) JOIN Abteilung USING (AbtId)

Der erste Join ordnet über AdrId die Adresse zu. Der zweite ergänzt über AbtId den Abteilungsnamen. Weil beide Joins innere Joins sind, bleiben nur Mitarbeiter mit passender Adresse und passender Abteilung übrig.

Prüfe bei jedem Schritt getrennt, welche Spalten verbunden werden und welche Zeilen erhalten bleiben. Das ist besonders wichtig bei Outer Joins: Ihre Reihenfolge und Richtung können die Ergebnismenge verändern.

Du kannst eine Join-Abfrage prüfen, indem du vier Fragen beantwortest: Welche Tabellen brauche ich? Welche Spalten bilden die Beziehung? Welche Zeilen müssen erhalten bleiben? Welche Zeilen und NULL-Ergänzungen erwarte ich im Ergebnis?