SQL JOINs verstehen - Vergiss die Kreise, schau auf die Daten

Wenn du anfängst, mit Datenbanken zu arbeiten, wirst du relativ schnell auf SQL JOINs stoßen. Und wahrscheinlich kennst du auch diese typische Darstellung mit zwei Kreisen die sich überschneiden. Mal ist die Schnittmenge eingefärbt, mal der linke Kreis, mal der rechte Kreis oder mal beide.

Das sieht zunächst ziemlich logisch aus. Zumindest so lange, bis du selbst eine SQL-Abfrage schreiben sollst und überlegen musst, welche Daten wirklich zurückkommen.

Dabei ist die grundlegende Idee hinter einem JOIN gar nicht besonders kompliziert: Du möchtest Informationen aus zwei Tabellen miteinander verknüpfen. Entscheidend ist nur, welche Datensätze im Ergebnis auftauchen sollen und was passiert, wenn keine passende Verknüpfung existiert.

Schauen wir uns das einmal ohne Kreise an. Wir nehmen zwei kleine Tabellen, schreiben die passenden SQL-Abfragen und schauen uns jedes Mal das tatsächliche Ergebnis an.

Als Beispiel verwenden wir eine einfache Ticketverwaltung. In der Tabelle ENTWICKLER stehen drei Personen:

In der Tabelle TICKET liegen vier Tickets. Jedes Ticket kann einem Entwickler zugeordnet sein. Die Zuordnung erfolgt über die Spalte ENTWICKLER_ID.

Du erkennst bereits ein paar Besonderheiten. Alice hat zwei Tickets, Carlos hat eines und Bob hat überhaupt kein Ticket. Ticket 104 ist dagegen noch niemandem zugewiesen. Deshalb steht dort NULL.

Die Verbindung zwischen den Tabellen entsteht über ENTWICKLER.ID und TICKET.ENTWICKLER_ID. Beide Spalten enthalten die ID des Entwicklers, wenn eine Zuordnung existiert.

Mit genau diesen Daten schauen wir uns jetzt die vier wichtigsten JOIN-Arten an.

INNER JOIN - Nur was zusammenpasst

Beginnen wir mit dem INNER JOIN. Er ist vermutlich die einfachste Variante, weil er nur Datensätze zurückliefert, für die auf beiden Seiten eine passende Verknüpfung existiert.

Wir möchten wissen, welche Entwickler welche Tickets bearbeiten.

SELECT E.NAME, T.ID AS TICKET_ID
  FROM ENTWICKLER E
 INNER JOIN TICKET T
    ON E.ID = T.ENTWICKLER_ID
 ORDER BY E.ID, T.ID;

Das Ergebnis sieht folgendermaßen aus:

Schauen wir genauer hin: Alice taucht zweimal auf, weil sie zwei Tickets besitzt. Das ist wichtig: Ein JOIN liefert nicht automatisch einen Datensatz pro Entwickler. Wenn mehrere Zeilen die Verknüpfungsbedingung erfüllen, entstehen auch mehrere Ergebniszeilen.

Carlos besitzt ein Ticket und erscheint deshalb einmal.

Aber wo ist Bob geblieben? Und warum fehlt Ticket 104?

Ganz einfach: Bob hat kein Ticket. Es gibt also keinen Datensatz in der Tabelle TICKET, dessen ENTWICKLER_ID den Wert 2 enthält. Für Ticket 104 gibt es umgekehrt keinen passenden Entwickler, weil dort keine Entwickler-ID eingetragen ist.

Beide fallen deshalb aus dem Ergebnis heraus.

Das entscheidende Stück unserer Abfrage ist übrigens die ON-Bedingung:

ON E.ID = T.ENTWICKLER_ID

Sie legt fest, wann zwei Zeilen zusammengehören. Die Kürzel E und T sind lediglich Aliase, damit wir nicht jedes Mal die vollständigen Tabellennamen schreiben müssen.

Merke dir beim INNER JOIN: Nur wenn die Verknüpfungsbedingung erfüllt ist, erscheint das Datenpaar im Ergebnis.

Übrigens kannst du INNER JOIN auch einfach als JOIN schreiben. Ohne weitere Angabe ist damit normalerweise genau dieser JOIN-Typ gemeint.

LEFT JOIN - Alle von links, passende von rechts

Jetzt ändern wir unsere Anforderung ein wenig.

Wir möchten nicht mehr nur Entwickler sehen, die bereits Tickets bearbeiten. Wir möchten alle Entwickler sehen, auch diejenigen, denen noch kein Ticket zugeordnet wurde.

Dafür verwenden wir einen LEFT JOIN.

SELECT E.NAME, T.ID AS TICKET_ID
  FROM ENTWICKLER E
  LEFT JOIN TICKET T
    ON E.ID = T.ENTWICKLER_ID
 ORDER BY E.ID, T.ID;

Das Ergebnis:

Der Unterschied zum INNER JOIN besteht aus genau einer zusätzlichen Zeile: Bob ist jetzt ebenfalls dabei.

Warum?

Weil die Tabelle ENTWICKLER auf der linken Seite des JOINs steht. Bei einem LEFT JOIN bleiben alle Zeilen dieser linken Tabelle erhalten, unabhängig davon, ob rechts ein passender Datensatz existiert.

Bei Alice und Carlos funktioniert die Zuordnung wie zuvor. Bei Bob findet die Datenbank kein passendes Ticket.

Statt Bob deshalb wegzulassen, liefert sie seine Daten trotzdem zurück. Für die fehlenden Informationen aus der Tabelle TICKET wird NULL eingesetzt.

Und Ticket 104? Das fehlt weiterhin, denn es besitzt keinen zugeordneten Entwickler und gehört damit zu keinem Datensatz der linken Tabelle.

Genau hier liegt ein häufiger Denkfehler: Ein LEFT JOIN bedeutet nicht, dass sämtliche Daten aus beiden Tabellen zurückkommen. Er garantiert lediglich, dass alle Zeilen der linken Tabelle berücksichtigt werden.

Merke dir beim LEFT JOIN: Die linke Tabelle bestimmt, welche Datensätze auf jeden Fall im Ergebnis vorkommen.

Das ist besonders praktisch, wenn du beispielsweise alle Kunden einschließlich derjenigen ohne Bestellung anzeigen möchtest oder eine Übersicht aller Mitarbeiter benötigst, unabhängig von ihren aktuellen Aufgaben.

RIGHT JOIN - Alle von rechts, passende von links

Beim RIGHT JOIN drehen wir das Prinzip einfach um.

Diesmal möchten wir alle Tickets sehen. Auch solche, die noch keinem Entwickler zugewiesen wurden.

Unsere Abfrage sieht so aus:

SELECT E.NAME, T.ID AS TICKET_ID
  FROM ENTWICKLER E
 RIGHT JOIN TICKET T
    ON E.ID = T.ENTWOCKLER_ID
 ORDER BY T.ID;

Und das bekommen wir zurück:

Jetzt erscheint Ticket 104, obwohl es keinem Entwickler zugeordnet ist. Dafür fehlt Bob wieder, weil er kein Ticket besitzt.

Der Grund ist derselbe wie beim LEFT JOIN, nur mit vertauschten Seiten.

Beim RIGHT JOIN bleiben alle Zeilen der rechten Tabelle erhalten. Da rechts unsere Tabelle TICKET steht, werden sämtliche Tickets berücksichtigt. Gibt es links keinen passenden Entwickler, bleiben dessen Spalten leer beziehungsweise enthalten NULL.

Damit wird auch klar, dass du einen RIGHT JOIN in den meisten Fällen gar nicht unbedingt benötigst.

Du könntest die Tabellen nämlich einfach vertauschen und stattdessen einen LEFT JOIN verwenden:

SELECT E.NAME, T.ID AS TICKET_ID
  FROM TICKET T
  LEFT JOIN ENTWICKLER E
    ON E.ID = T.ENTWICKLER_ID
 ORDER BY T.ID;

Das Ergebnis wäre exakt dasselbe.

In der Praxis begegnet dir der LEFT JOIN häufig öfter als der RIGHT JOIN, weil sich Abfragen damit oft etwas natürlicher lesen lassen. Fachlich sind die beiden Varianten aber gleichwertig, wenn du die Tabellen entsprechend vertauschst.

Merke dir beim RIGHT JOIN: Alle Zeilen der rechten Tabelle bleiben erhalten. Die linke Tabelle liefert nur dann zusätzliche Werte, wenn eine passende Verbindung existiert.

FULL OUTER JOIN - Alles von beiden Seiten

Jetzt fehlt noch eine Variante.

Was passiert, wenn wir wirklich alle Entwickler und alle Tickets sehen möchten? Also auch Entwickler ohne Ticket und Tickets ohne Entwickler?

Genau dafür gibt es den FULL OUTER JOIN.

SELECT E.NAME, T.ID AS TICKET_ID
  FROM ENTWICKLER E
  FULL OUTER JOIN TICKET T
    ON E.ID = T.ENTWICKLER_ID
 ORDER BY COALESCE(E.ID, 999), T.ID;

Das Ergebnis:

Hier sind tatsächlich alle Informationen enthalten.

Alice erscheint mit ihren beiden Tickets. Carlos erscheint mit seinem Ticket. Bob erscheint ohne Ticket und Ticket 104 erscheint ohne Entwickler.

Der FULL OUTER JOIN kombiniert also das Verhalten eines LEFT JOIN und eines RIGHT JOIN.

Wenn es passende Daten auf beiden Seiten gibt, werden sie verbunden. Gibt es auf einer Seite keine passende Zeile, bleiben die Daten der anderen Seite trotzdem erhalten.

Bei unserem Beispiel ergeben sich dadurch fünf Ergebniszeilen.

Vielleicht ist dir noch das COALESCE in der ORDER BY-Klausel aufgefallen. Damit sorgen wir lediglich für eine nachvollziehbare Sortierung. COALESCE(E.ID, 999) verwendet die Entwickler-ID und setzt ersatzweise den Wert 999 ein, wenn diese NULL ist. Dadurch erscheint das nicht zugewiesene Ticket am Ende. Auf die eigentliche Verknüpfung hat das keinen Einfluss.

Eine Besonderheit solltest du allerdings kennen: Nicht jedes Datenbanksystem unterstützt FULL OUTER JOIN direkt. PostgreSQL und aktuelle SQLite-Versionen können damit umgehen. MySQL und MariaDB unterstützen diese Syntax dagegen nicht unmittelbar. Dort musst du das gewünschte Ergebnis beispielsweise mit einer Kombination aus zwei Abfragen und UNION ALL nachbilden. Mehr zu UNIONs aber in eine anderen Beitrag.

Merke dir beim FULL OUTER JOIN: Alle Zeilen beider Tabellen werden berücksichtigt. Wo eine passende Verbindung fehlt, stehen für die jeweils andere Seite NULL-Werte.

Wenn du unsere vier Ergebnisse jetzt miteinander vergleichst, wird der Unterschied eigentlich ziemlich deutlich. Beim INNER JOIN fehlen sowohl Bob als auch das nicht zugewiesene Ticket. Der LEFT JOIN ergänzt Bob, der RIGHT JOIN ergänzt stattdessen Ticket 104 und der FULL OUTER JOIN berücksichtigt beide.

Und damit brauchst du eigentlich auch keine zwei sich überschneidenden Kreise mehr.

Es gibt darüber hinaus noch weitere JOIN-Konstruktionen. Ein CROSS JOIN kombiniert beispielsweise jede Zeile der einen Tabelle mit jeder Zeile der anderen. Bei unseren drei Entwicklern und vier Tickets wären das zwölf Kombinationen, unabhängig davon, welchem Entwickler die Tickets tatsächlich gehören. Ein SELF JOIN wiederum verbindet eine Tabelle mit sich selbst, beispielsweise um Mitarbeiter und ihre ebenfalls in derselben Tabelle gespeicherten Vorgesetzten zuzuordnen. Das sind andere Anwendungsfälle als die vier gerade betrachteten Verknüpfungsarten.

Für die tägliche Arbeit mit relationalen Datenbanken ist vor allem eine Frage entscheidend: Welche Datensätze sollen auch dann im Ergebnis erscheinen, wenn es in der anderen Tabelle keinen passenden Eintrag gibt?

Wenn du diese Frage beantworten kannst, weißt du meistens bereits, welchen JOIN du benötigst.

Und falls du einmal unsicher bist, baue dir zwei kleine Tabellen wie in unserem Beispiel und schau dir das tatsächliche Ergebnis an. Gerade bei JOINs helfen fünf konkrete Ergebniszeilen oft mehr als jede noch so schöne Grafik.