Äußerer Join: OUTER JOIN

Ein innerer Join behält nur die Zeilen, für die es in der anderen Tabelle ein Gegenstück gibt. Ein äußerer Join arbeitet anders: Er liefert immer alle Zeilen einer Tabelle oder beider Tabellen und füllt die fehlende Hälfte mit NULL auf.

Es gibt drei Arten des äußeren Joins: links (LEFT), rechts (RIGHT) und vollständig (FULL). Die Art musst du angeben — ein bloßes OUTER JOIN ist ein Syntaxfehler. Das Wort OUTER selbst ist dagegen optional: LEFT JOIN und LEFT OUTER JOIN bedeuten dasselbe, und im Folgenden wird die kurze Schreibweise verwendet.

Linker äußerer Join (LEFT OUTER JOIN)

Liefert alle Zeilen der linken Tabelle. Findet eine Zeile ein Gegenstück in der rechten Tabelle, werden beide Zeilen zusammengefügt; findet sie keines, werden die Spalten der rechten Tabelle mit NULL gefüllt.

Als Beispiel holen wir aus der Datenbank den Klingelplan, verknüpft mit den passenden Einträgen aus dem Stundenplan.

Daten in der Tabelle Timepair (Klingelplan):

Daten in der Tabelle Schedule (Stundenplan):

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

Alle acht Klingelzeiten sind im Ergebnis gelandet — genau das verspricht der linke Join. Zeilen sind es aber 43 und nicht 8.

Ein Join ergänzt die linke Tabelle nicht, sondern geht alle passenden Zeilenpaare durch. Dieselbe Stundennummer kommt im Stundenplan viele Male vor — an verschiedenen Tagen und in verschiedenen Klassen —, und jede Übereinstimmung ergibt eine eigene Zeile. Ist der Schlüssel in der rechten Tabelle nicht eindeutig, hat das Ergebnis mehr Zeilen als die linke Tabelle.

Am Ende der Ergebnismenge stehen Zeilen, in denen alle Spalten des Stundenplans NULL sind. Das sind die Klingelzeiten ohne jeden Unterricht: Ein Gegenstück fehlt, aber eine Zeile der linken Tabelle muss im Ergebnis auftauchen.

Zeilen ohne Gegenstück

Auf diesen NULL-Werten beruht der häufigste Praxis-Kniff — Datensätze finden, die kein Gegenstück haben. Dazu genügt es, nur die Zeilen zu behalten, in denen der Schlüssel der rechten Tabelle leer ist:

MySQL 8.1
SELECT Timepair.id, start_pair, end_pair
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id
WHERE Schedule.number_pair IS NULL;

Übrig bleiben drei Klingelzeiten, für die kein Unterricht eingeplant ist.

Eine Verknüpfung, aus der nur die Zeilen ohne Gegenstück übrig bleiben, nennt man Anti-Join (ANTI JOIN). Einen eigenen Operator hat er nicht: Sowohl in MySQL als auch in PostgreSQL schreibt man ihn genau so — als Verknüpfung mit einer IS NULL-Bedingung.

Rechter äußerer Join (RIGHT OUTER JOIN)

Das Spiegelbild des linken Joins: Alle Zeilen der rechten Tabelle landen garantiert im Ergebnis, und die fehlenden Spalten der linken Tabelle werden mit NULL gefüllt.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    RIGHT JOIN Schedule ON Schedule.number_pair = Timepair.id;

Das Ergebnis hat 40 Zeilen — genau so viele, wie Schedule Datensätze enthält — und keine einzige Zeile mit NULL. Es stimmt also vollständig mit dem inneren Join überein.

Der Grund: Jeder Unterrichtseintrag verweist auf eine vorhandene Klingelzeit, die rechte Tabelle hat schlicht keine Zeilen ohne Gegenstück. Die Art des Joins gibt die Regel vor, was am Ende im Ergebnis steht, entscheiden die Daten.

Vollständiger äußerer Join (FULL OUTER JOIN)

Liefert alle Zeilen beider Tabellen. Zeilen mit Gegenstück werden zusammengefügt, Zeilen ohne Gegenstück aus der linken und der rechten Tabelle landen mit NULL anstelle der fehlenden Hälfte im Ergebnis.

Das Ergebnis eines vollständigen Joins besteht aus drei Teilen:

• den Zeilen des inneren Joins (INNER JOIN);
• den Zeilen der linken Tabelle ohne Gegenstück;
• den Zeilen der rechten Tabelle ohne Gegenstück.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    FULL OUTER JOIN Schedule ON Schedule.number_pair = Timepair.id;

MySQL 8.1
SELECT tabellen_felder
FROM linke_tabelle
    LEFT JOIN rechte_tabelle ON rechte_tabelle.key = linke_tabelle.key

UNION ALL

SELECT tabellen_felder
FROM linke_tabelle
    RIGHT JOIN rechte_tabelle ON rechte_tabelle.key = linke_tabelle.key
WHERE linke_tabelle.key IS NULL;

Alle Arten von Tabellen-Joins

SELECT tabellen_felder
FROM linke_tabelle
    LEFT JOIN rechte_tabelle ON rechte_tabelle.key = linke_tabelle.key

Prüfen wir dich einmal. Die linke Tabelle hat 8 Zeilen. Wie viele Zeilen liefert ein LEFT JOIN mit der rechten Tabelle?