Deutsch
Menü

Inner Join vs. Outer Join: verbleibende Zeilen, NULL, Left und Right Outer Join und SQL-Syntax

Ein Join kombiniert die Zeilen zweier Tabellen nach der in ON angegebenen Bedingung (meist, dass ein Fremdschlüssel gleich einem Primärschlüssel ist) zu einer Ergebnistabelle. Ein Inner Join behält nur die Kombinationen von Zeilen, die einen passenden Partner haben; Zeilen ohne Partner verschwinden aus beiden Tabellen. Ein Outer Join behält die passenden Kombinationen und zusätzlich die Zeilen der angegebenen Tabelle, auch wenn sie keinen Partner haben, und füllt die Spalten der anderen Tabelle mit NULL. Ein Left Outer Join (LEFT OUTER JOIN) behält jede Zeile der linken Tabelle, ein Right Outer Join (RIGHT OUTER JOIN) die der rechten, und ein Full Outer Join (FULL OUTER JOIN) die beider. Entscheidend ist, ob Zeilen ohne Partner im Ergebnis erscheinen sollen.

MerkmalInner Join (INNER JOIN)Outer Join (OUTER JOIN)
Zeilen im ErgebnisNur Kombinationen von Zeilen, die die Join-Bedingung erfüllenPassende Kombinationen plus die Zeilen der zu behaltenden Tabelle, die keinen passenden Partner haben
Zeilen ohne PartnerWerden aus dem Ergebnis entfernt, aus beiden TabellenZeilen der behaltenen Tabelle bleiben als je eine Zeile erhalten, die Spalten der anderen Tabelle werden NULL; Zeilen ohne Partner aus der anderen Tabelle werden entfernt
ArtenEine Art; INNER kann entfallen, dann schreibt man nur JOINDrei Arten: links (LEFT), rechts (RIGHT) und vollständig (FULL); OUTER kann entfallen
SQL-BeispielSELECT … FROM Mitarbeiter INNER JOIN Abteilung ON Mitarbeiter.AbteilungsID = Abteilung.AbteilungsIDSELECT … FROM Mitarbeiter LEFT OUTER JOIN Abteilung ON Mitarbeiter.AbteilungsID = Abteilung.AbteilungsID
Beispiel für die Zeilenzahl (4 Mitarbeiter, einer davon mit NULL als AbteilungsID; 3 Abteilungen, eine davon ohne Mitarbeiter)3 Zeilen: Der Mitarbeiter mit NULL als AbteilungsID und die Abteilung ohne Mitarbeiter erscheinen nicht4 Zeilen bei einem Left Outer Join, der die Mitarbeitertabelle behält (der Mitarbeiter mit NULL als AbteilungsID erhält NULL als Abteilungsnamen); 5 Zeilen bei einem Full Outer Join, der beide behält
Reihenfolge der TabellenVertauschen von links und rechts ergibt dieselben Zeilen (nur die Spaltenreihenfolge ändert sich)Bei Left und Right Outer Join hängt das Ergebnis davon ab, welche Tabelle links steht; A LEFT OUTER JOIN B liefert dieselben Zeilen wie B RIGHT OUTER JOIN A
Bedingungen auf Spalten der anderen TabelleGleiches Ergebnis, ob in ON oder in der WHERE-Klausel geschriebenIn der WHERE-Klausel erfüllen die mit NULL gefüllten Zeilen die Bedingung nicht und verschwinden, was dasselbe Ergebnis wie ein Inner Join ergeben kann; um Zeilen ohne Partner zu behalten, schreibt man sie in ON
Typischer EinsatzWenn nur Daten gewünscht sind, die in beiden Tabellen vorhanden sind (z. B. Bestellungen um Kundennamen ergänzen)Wenn auch Zeilen ohne Partner aufgelistet werden sollen (z. B. alle Mitarbeiter einschließlich derer ohne Abteilung) oder wenn Zeilen ohne Partner gesucht werden

Die Zahlenwerte sind typische Größenordnungen und variieren je nach Prozessgeneration und Produkt.

Wann welche Variante?

Inner Join
Wenn nur Zeilen mit passenden Daten in beiden Tabellen gebraucht werden. Werden zum Beispiel einer Bestellliste die Kundennamen hinzugefügt und Bestellungen mit unbekanntem Kunden nicht benötigt, nimmt man einen Inner Join.
Outer Join
Wenn Zeilen ohne Partner nicht wegfallen dürfen. Um zum Beispiel einer Liste aller Mitarbeiter die Abteilungsnamen hinzuzufügen und dabei Mitarbeiter ohne Abteilung zu behalten, nimmt man einen Left Outer Join, der die Mitarbeitertabelle behält. Mit einem Outer Join lassen sich auch Zeilen ohne Partner finden, etwa Kunden, die noch nie bestellt haben, indem man die Zeilen auswählt, in denen die Primärschlüsselspalte der anderen Tabelle NULL ist.

Häufige Fragen

Wie unterscheiden sich Left und Right Outer Join?

Nur darin, von welcher Tabelle alle Zeilen behalten werden. Ein Left Outer Join behält die Zeilen der linken, in FROM zuerst genannten Tabelle auch ohne Partner, ein Right Outer Join die Zeilen der rechten, danach genannten Tabelle. A LEFT OUTER JOIN B und B RIGHT OUTER JOIN A liefern dieselben Zeilen, daher schreibt man Abfragen der Lesbarkeit halber oft einheitlich mit Left Outer Joins.

Warum kann ein Join mehr Zeilen liefern als die ursprünglichen Tabellen?

Bei einem Join wird jede Kombination von Zeilen, die die Bedingung erfüllt, zu einer Ergebniszeile. Hat die Join-Spalte in beiden Tabellen doppelte Werte, vervielfacht sich die Zahl der Kombinationen. Hat zum Beispiel eine Tabelle 2 Zeilen mit dem Wert 1 und die andere ebenfalls 2, ergibt allein dieser Wert 2 × 2 = 4 Zeilen.

Was passiert ohne Join-Bedingung?

Das Ergebnis ist jede Kombination der Zeilen beider Tabellen (das kartesische Produkt). Das nennt man CROSS JOIN: Eine Tabelle mit 4 Zeilen und eine mit 3 Zeilen ergeben 4 × 3 = 12 Zeilen. Entsteht ein kartesisches Produkt versehentlich, etwa weil ON vergessen wurde, schnellt die Zeilenzahl in die Höhe; Vorsicht ist also geboten.

Stimmen NULL-Werte in einer Join-Bedingung miteinander überein?

Nein. In SQL ist ein Vergleich mit NULL nie wahr, daher werden Zeilen, deren Join-Spalten in beiden Tabellen NULL sind, nicht kombiniert. Folglich fallen bei einem Inner Join Zeilen weg, deren Join-Spalte NULL ist.

Verständnis prüfen

Eine Mitarbeitertabelle (MitarbeiterNr, Name, AbteilungsID) hat 4 Zeilen mit den AbteilungsID-Werten 10, 20, 10 und NULL in dieser Reihenfolge. Eine Abteilungstabelle (AbteilungsID, Abteilungsname) hat 3 Zeilen mit AbteilungsID 10, 20 und 30. Wie viele Zeilen liefert SELECT Mitarbeiter.Name, Abteilung.Abteilungsname FROM Mitarbeiter INNER JOIN Abteilung ON Mitarbeiter.AbteilungsID = Abteilung.AbteilungsID?

  1. A3 Zeilen
  2. B4 Zeilen
  3. C12 Zeilen
Antwort und Erläuterung anzeigen

Antwort: A. 3 Zeilen

Ein INNER JOIN liefert nur die Kombinationen von Zeilen, die die ON-Bedingung erfüllen. Die zwei Mitarbeiter mit AbteilungsID 10 werden mit Abteilung 10 kombiniert und der eine Mitarbeiter mit AbteilungsID 20 mit Abteilung 20, das Ergebnis hat also 3 Zeilen. Der Mitarbeiter mit AbteilungsID NULL wird mit keiner Abteilung verbunden, weil ein Vergleich mit NULL nie wahr ist, und fehlt im Ergebnis. Auch Abteilung 30 ohne Mitarbeiter erscheint nicht. 4 Zeilen ist die Anzahl bei einem Left Outer Join, der jede Zeile der Mitarbeitertabelle behält (Mitarbeiter LEFT OUTER JOIN Abteilung). 12 Zeilen ist der CROSS JOIN (kartesisches Produkt), der ohne Bedingung jede Kombination bildet: 4 × 3 = 12 Zeilen.

Eine Mitarbeitertabelle (MitarbeiterNr, Name, AbteilungsID) hat 5 Zeilen mit den AbteilungsID-Werten 10, 20, 10, NULL und NULL in dieser Reihenfolge. Eine Abteilungstabelle (AbteilungsID, Abteilungsname) hat 3 Zeilen mit AbteilungsID 10, 20 und 30. Wie viele Zeilen liefert SELECT Abteilung.Abteilungsname, Mitarbeiter.Name FROM Abteilung LEFT OUTER JOIN Mitarbeiter ON Abteilung.AbteilungsID = Mitarbeiter.AbteilungsID?

  1. A3 Zeilen
  2. B4 Zeilen
  3. C5 Zeilen
Antwort und Erläuterung anzeigen

Antwort: B. 4 Zeilen

Ein LEFT OUTER JOIN behält jede Zeile der linken Tabelle (hier der Abteilungstabelle); hat die rechte Tabelle keine passende Zeile, entsteht eine Zeile, deren Spalten der rechten Tabelle mit NULL gefüllt sind. Abteilung 10 hat zwei Mitarbeiter und ergibt zwei Zeilen, Abteilung 20 eine Zeile, und Abteilung 30 ohne Mitarbeiter ergibt eine Zeile mit Name NULL, zusammen 4 Zeilen. Die zwei Mitarbeiter mit AbteilungsID NULL passen zu keiner Abteilung und erscheinen als Zeilen der rechten Tabelle, die nicht vollständig behalten wird, nicht im Ergebnis. Ein Inner Join würde auch die Zeile für Abteilung 30 weglassen und 3 Zeilen ergeben. Vertauscht man die Tabellen zu Mitarbeiter LEFT OUTER JOIN Abteilung, bleiben alle fünf Mitarbeiter erhalten, was 5 Zeilen ergibt. Bei Outer Joins ist es wichtig zu prüfen, welche Tabelle alle ihre Zeilen behält.

Spalte x der Tabelle R hat 4 Zeilen mit den Werten 1, 1, 2, 3, und Spalte x der Tabelle S hat 4 Zeilen mit den Werten 1, 1, 2, 4. Wie viele Zeilen liefert SELECT * FROM R INNER JOIN S ON R.x = S.x?

  1. A2 Zeilen
  2. B3 Zeilen
  3. C5 Zeilen
Antwort und Erläuterung anzeigen

Antwort: C. 5 Zeilen

Bei einem Join wird jede Kombination von Zeilen, die die Bedingung erfüllt, zu einer Ergebniszeile. Für x = 1 erfüllt jede Kombination der zwei Zeilen von R mit den zwei Zeilen von S die Bedingung, das ergibt 2 × 2 = 4 Zeilen; für x = 2 ist es 1 × 1 = 1 Zeile. Die 3 von R und die 4 von S haben keinen Partner und erscheinen im Inner Join nicht. Insgesamt sind es 4 + 1 = 5 Zeilen. 2 Zeilen wäre die Anzahl, wenn pro übereinstimmendem Wert (1 und 2) nur eine Zeile entstünde. 3 Zeilen wäre die Anzahl, wenn man für jede Zeile von R nur prüft, ob S einen passenden Wert enthält (eine Abfrage wie SELECT * FROM R WHERE x IN (SELECT x FROM S)). Das kartesische Produkt ohne Bedingung hätte 4 × 4 = 16 Zeilen. Zu beachten ist, dass sich bei doppelten Werten in der Join-Spalte die Zahl der Ergebniszeilen multipliziert.

  1. Aus einer Kundentabelle (KundenID, Name) und einer Bestelltabelle (BestellID, KundenID, Betrag) sollen die Namen der Kunden ermittelt werden, die noch nie bestellt haben. Welche SQL-Anweisung ist richtig?

Übungsfragen zu Grundlagen der Datenbanken

Wichtige Begriffe erklärt

SQL
Standardsprache relationaler Datenbanken zum Definieren von Tabellen, zum Suchen, Einfügen, Ändern und Löschen von Daten und zum Steuern von Berechtigungen. Sie ist durch ISO/IEC genormt (in Japan zudem als JIS). Zu ihren Anweisungen gehören SELECT, INSERT, UPDATE, DELETE, CREATE TABLE und GRANT.
Relationale Datenbank
Datenbank, die Daten als Tabellen (Relationen) aus Zeilen und Spalten darstellt und Tabellen über gemeinsame Werte miteinander verknüpft; auch RDB genannt. Abfragen und Änderungen erfolgen in SQL, und Integritätsbedingungen wie Primär- und Fremdschlüssel halten die Daten konsistent.
Primärschlüssel
Spalte oder Spaltenkombination, mit der eine Zeile einer Tabelle eindeutig identifiziert wird. Sie unterliegt den Bedingungen, dass sich ihre Werte nicht wiederholen und nicht NULL sein dürfen. Der Primärschlüssel wird aus den minimalen Spaltenkombinationen ausgewählt, die eine Zeile eindeutig identifizieren können (Schlüsselkandidaten).
Fremdschlüssel
Spalte, die auf den Primärschlüssel (oder eine eindeutige Spalte) einer anderen Tabelle verweist. Über die Bedingung der referenziellen Integrität, die nur in der referenzierten Tabelle vorhandene Werte oder NULL zulässt, verhindert sie Daten, die auf nicht existierende Zeilen zeigen. Auch das Verhalten beim Löschen oder Ändern einer referenzierten Zeile (Zurückweisen, kaskadierendes Löschen usw.) lässt sich festlegen.
Normalisierung
Entwurfsverfahren, das Tabellen einer relationalen Datenbank anhand funktionaler Abhängigkeiten zwischen Spalten aufteilt, damit dieselbe Tatsache nicht an mehreren Stellen gespeichert wird. Es beseitigt doppelt gespeicherte Daten und verhindert Änderungs-, Einfüge- und Löschanomalien. Zu den Stufen gehören die erste bis dritte Normalform und die Boyce-Codd-Normalform.
View (Datenbank-View)
Virtuelle Tabelle in einer relationalen Datenbank, die definiert wird, indem man einer SELECT-Anweisung einen Namen gibt. Sie hält normalerweise keine eigenen Daten und bildet ihr Ergebnis bei jedem Zugriff aus den zugrunde liegenden Tabellen. Sie dient dazu, komplexe Abfragen zu vereinfachen und den Zugriff durch Begrenzung der angezeigten Spalten und Zeilen einzuschränken.

Verwandte Vergleiche