SQL OUTER JOIN
IONOS Redaktion9 mins
Bei einem SQL JOIN handelt es sich um eine Abfrageoperation, die mehrere Tabellen einer relationalen Datenbank verknüpft und deren Datensätze (Tupel) gefiltert nach einer vom Benutzer definierten Selektionsbedingung ausgibt.
- Inklusive Wildcard-SSL-Zertifikat
- Inklusive Domain Lock
- Inklusive 2 GB E-Mail-Postfach
Der geläufigste JOIN-Typ des relationalen Datenbankmodells ist der SQL INNER JOIN. In der Praxis nutzen Anwender beispielsweise dann INNER JOINs, wenn zwei Datenbanktabellen anhand gleicher Spalten verbunden werden sollen. Dabei wird jeder Datensatz der einen Tabelle mit einem entsprechenden Datensatz der anderen Tabelle zusammengeführt. Datensätze, zu denen das Datenbank-Managementsystem (DBMS) keine Entsprechung in der jeweils anderen Tabelle findet, werden ausgeblendet. Ein SQL OUTER JOIN hingegen gibt nicht nur die Datensätze beider Tabellen aus, die die Selektionsbedingung (beispielsweise die Gleichheit der Werte zweier Spalten) erfüllen, sondern zusätzlich auch alle übrigen Tupel der einen bzw. der anderen Tabelle. Bezogen auf die Leserichtung der SQL-Syntax spricht man von einer linken und einer rechten Tabelle. Die jeweiligen Operationen heißen dementsprechend LEFT OUTER JOIN und RIGHT OUTER JOIN. Möchten Sie im Rahmen von Datenbankabfragen zusätzlich zu den Datensätzen, die die Selektionsbedingung erfüllen, auch sämtliche Datensätze der linken und rechten Tabelle ausgeben, handelt es sich um einen FULL OUTER JOIN. Das Prinzip der unterschiedlichen JOIN-Typen lässt sich sehr gut durch Mengendiagramme veranschaulichen:
Unterarten des OUTER JOINs
Jeder OUTER JOIN wird als LEFT OUTER JOIN, RIGHT OUTER JOIN oder FULL OUTER JOIN realisiert.
Das Keyword OUTER ist in der SQL-Syntax optional. In der Regel nutzen Anwender die verkürzte Schreibweise LEFT JOIN, RIGHT JOIN und FULL JOIN.
Wir veranschaulichen die Funktionsweise von OUTER JOINs anhand der Datenbanktabellen „mitarbeiter“ und „kfz“.
Tabelle: mitarbeiter
| m_id | nachname | vorname | kfz_id |
|---|---|---|---|
| 1 | Schmidt | Udo | 3 |
| 2 | Müller | Wolfgang | 1 |
| 3 | Meyer | Günther | 1 |
| 4 | Krause | Helmut | 2 |
| 5 | Fischer | Hugo | NULL |
Die Tabelle „mitarbeiter“ umfasst die Vor- und Nachnamen der Mitarbeiter eines fiktiven Unternehmens sowie die Kennung des zugewiesenen Firmenwagens (kfz_id). Primärschlüssel der Tabelle ist eine widerspruchsfreie Mitarbeiter-ID (m_id). Dem Mitarbeiter mit der ID 5 (Hugo Fischer) wurde bisher kein Firmenwagen zugewiesen. Die Zelle der entsprechenden Spalte enthält daher einen Nullwert.
Der Nullwert NULL ist ein Wert, der für die Abwesenheit eines Wertes steht. Er entspricht nicht dem Zahlenwert 0.
Tabelle: kfz
| kfz_id | marke | modell | kennzeichen | baujahr | hu |
|---|---|---|---|---|---|
| 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 4 | Porsche | Boxster | B AA 123 | 2018 | 23.12.2020 |
In der Tabelle „kfz“ wurden Informationen zum Fuhrpark des Unternehmens erfasst: Marke des Firmenwagens, Modell, das Kennzeichen, das Baujahr sowie das Datum der nächsten Hauptuntersuchung (hu). Jedem Firmenwagen ist eine widerspruchsfreie ID zugeordnet (kfz_id), die als Primärschlüssel der Tabelle fungiert.
Beide Tabellen sind durch eine Fremdschlüsselbeziehung miteinander verknüpft. Der Primärschlüssel der Tabelle „kfz“ (die kfz_id) wurde als Fremdschlüssel in die Tabelle „mitarbeiter“ integriert. Dies ermöglicht es uns, beide Tabellen über eine gemeinsame Spalte zu verbinden.
Während gültige Primärschlüssel keine Nullwerte enthalten dürfen, verletzen Nullwerte in Fremdschlüsseln nicht die Integrität eines Datensatzes.
SQL LEFT OUTER JOIN
Bei einem LEFT OUTER JOIN gilt die Tabelle auf der linken Seite des JOIN-Operators als dominante Tabelle. In der relationalen Algebra werden LEFT OUTER JOINs mit folgendem Operator notiert: ⟕.
Um die Tabellen „mitarbeiter“ und „kfz“ im Rahmen eines LEFT OUTER JOINs zu verbinden, bietet sich folgende Operation an:
mitarbeiter ⟕ kfz_id=kfz_idkfz
Die Interaktion mit dem DBMS erfolgt in der Datenbanksprache SQL. Die oben dargestellte Formel entspricht folgendem SQL-Statement:
SELECT * FROM mitarbeiter LEFT JOIN kfz ON mitarbeiter.kfz_id = kfz.kfz_id;Die Tabelle „mitarbeiter“ steht auf der linken Seite des JOIN-Operators, die Tabelle „kfz“ auf der rechten. Als Selektionsbedingung definieren wir mitarbeiter.kfz_id = kfz.kfz_id. Die Ergebnismenge eines LEFT OUTER JOIN umfasst alle Datensätze der linken Tabelle sowie jene Datensätze der rechten Tabelle, die die JOIN-Bedingung erfüllen. Es werden somit nur Datensätze der Tabelle „kfz“ in die Ergebnismenge des JOINs aufgenommen, die in der Spalte kfz_id einen Wert enthalten, für den das DBMS auch einen entsprechenden Wert in der Tabelle „mitarbeiter“ findet.
Fehlende Werte in der Ergebnistabelle werden als Nullwerte ausgegeben.
Anders als bei INNER JOINs ist bei OUTER JOINs die Reihenfolge der Tabellen im SQL-Statement zu beachten. Bei einem LEFT JOIN werden alle Datensätze der Tabelle links vom JOIN-Operator komplett ausgegeben, bei einem RIGHT JOIN alle Datensätze der Tabelle rechts vom JOIN-Operator.
Als Ergebnis des LEFT OUTER JOINs erhalten wir folgende Tabelle.
Tabelle: LEFT OUTER JOIN über die Tabellen „mitarbeiter“ und „kfz“
| m_id | nachname | vorname | mitarbeiter.kfz_id | kfz.kfz_id | marke | modell | kennzeichen | baujahr | hu |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Schmidt | Udo | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Müller | Wolfgang | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Meyer | Günther | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Krause | Helmut | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Fischer | Hugo | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Die Ergebnistabelle weist zwei Besonderheiten auf:
Der Datensatz der Tabelle „kfz“ mit der kfz_id 4 (Porsche Boxster) taucht in der Ergebnistabelle nicht auf. Der Grund dafür: Für einen Primärschlüssel mit dem Wert 4 existiert kein passender Fremdschlüssel in der Tabelle „mitarbeiter“. Die Selektionsbedingung ist nicht erfüllt. Der Datensatz entstammt der rechten Ausgangstabelle und wird daher ausgeblendet.
Der Fremdschlüssel kfz_id der Ausgangstabelle „mitarbeiter“ enthält für den Datensatz zum Mitarbeiter Hugo Fischer einen Nullwert. Es lässt sich somit kein entsprechender Primärschlüssel in der Tabelle „kfz“ finden. Auch hier ist die Selektionsbedingung also nicht erfüllt. Da der Datensatz jedoch der linken Ausgangstabelle entstammt, wird dieser bei einem LEFT JOIN trotzdem in die Ergebnistabelle aufgenommen. Fehlende Werte im Tupel der Ergebnistabelle werden auf NULL gesetzt.
SQL RIGHT OUTER JOIN
Der RIGHT OUTER JOIN folgt demselben Prinzip wie der LEFT OUTER JOIN, dominierend ist hier jedoch nicht die linke, sondern die rechte Tabelle.
Die Ergebnismenge eines RIGHT OUTER JOIN umfasst alle Tupel der Tabelle auf der rechten Seite des JOIN-Operators sowie die Tupel der linken Tabelle, die die JOIN-Bedingung erfüllen. Als Operator kommt folgendes Symbol zum Einsatz: ⟖.
Wir gehen erneut von den Ausgangstabellen „mitarbeiter“ und „kfz“ aus und definieren für den RIGHT JOIN dieselbe Selektionsbedingung wie im Beispiel zum LEFT JOIN.
Relationale Algebra:
mitarbeiter ⟖ kfz_id=kfz_idkfz
SQL-Statement:
SELECT * FROM mitarbeiter RIGHT JOIN kfz ON mitarbeiter.kfz_id = kfz.kfz_id;Die Ergebnistabelle des RIGHT JOINs unterschiedet sich deutlich von der des LEFT JOINs.
Tabelle: RIGHT OUTER JOIN über die Tabellen „mitarbeiter“ und „kfz“
| m_id | nachname | vorname | mitarbeiter.kfz_id | kfz.kfz_id | marke | modell | kennzeichen | baujahr | hu |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Schmidt | Udo | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Müller | Wolfgang | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Meyer | Günther | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Krause | Helmut | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| NULL | NULL | NULL | 4 | 4 | Porsche | Boxster | B AA 123 | 2018 | 23.12.2020 |
Der Datensatz zum Mitarbeiter Hugo Fischer ist in der Ergebnistabelle nicht enthalten. Der Grund dafür: Die kfz_id des Datensatzes entspricht dem Wert NULL und lässt sich daher keinem Datensatz der rechten Tabelle zuordnen.
Als Ergebnis des RIGHT JOINs erhalten wir alle Datensätze der Tabelle „kfz“ – auch den Datensatz mit der kfz_id 4, dem kein Tupel der Tabelle „mitarbeiter“ zugewiesen wurde. Fehlende Werte werden auch hier als Nullwerte ausgegeben.
SQL FULL OUTER JOIN
Bei einem FULL OUTER JOIN handelt es sich um eine Kombination aus LEFT OUTER JOIN und RIGHT OUTER JOIN. Für die Operation wurde in der relationalen Algebra folgender Operator definiert: ⟗.
Auch den FULL JOIN veranschaulichen wir an den Ausgangstabellen „mitarbeiter“ und „kfz“ und gehen dabei von derselben Selektionsbedingung aus wie zuvor.
Relationale Algebra:
mitarbeiter ⟗ kfz_id=kfz_idkfz
SQL-Statement:
SELECT * FROM mitarbeiter FULL JOIN kfz ON mitarbeiter.kfz_id = kfz.kfz_id;Das Ergebnis entspricht folgender Tabelle.
Tabelle: FULL OUTER JOIN für die Tabellen „mitarbeiter“ und „kfz“
| m_id | nachname | vorname | mitarbeiter.kfz_id | kfz.kfz_id | marke | modell | kennzeichen | baujahr | hu |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Schmidt | Udo | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Müller | Wolfgang | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Meyer | Günther | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Krause | Helmut | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Fischer | Hugo | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | 4 | Porsche | Boxster | B AA 123 | 2018 | 23.12.2020 |
Der FULL JOIN verbindet die Datensätze der Ausgangstabellen gemäß der Selektionsbedingung, führt jedoch nicht nur die verbundenen Datensätze in der Ergebnistabelle auf, sondern auch die Datensätze beider Tabellen, die die Selektionsbedingung nicht erfüllen.
Auch beim FULL JOIN werden fehlende Werte auf NULL gesetzt.
FULL OUTER JOINs haben in der Praxis eine geringe Bedeutung und werden von marktführenden Datenbank-Managementsystemen wie MySQL und MariaDB nicht unterstützt.
NATURAL OUTER JOIN
Wie INNER JOINs lassen sich auch OUTER JOINs als NATURAL JOINs umsetzen. Die entsprechenden Operatoren lauten:
LEFT/RIGHT JOIN ... USINGOder:
NATURAL LEFT/RIGHT JOINNATURAL OUTER JOINs verbinden Tabellen über gleichnamige Spalten. Welche Spalten ausgewählt werden, lässt sich mithilfe des USING-Schlüsselworts explizit definieren:
SELECT * FROM mitarbeiter LEFT JOIN kfz USING(kfz_id);Alternativ können Sie auf eine Kurzschreibweise zurückgreifen, bei der das DMBS automatisch nach gleichnamigen Spalten sucht und die aufgeführten Tabellen über diese verbindet:
SELECT * FROM mitarbeiter NATURAL LEFT JOIN abteilungen;Bezogen auf die oben aufgeführten Beispieltabellen führen beide SQL-Statements zum selben Ergebnis.
| m_id | nachname | vorname | kfz_id | marke | modell | kennzeichen | baujahr | hu |
|---|---|---|---|---|---|---|---|---|
| 1 | Schmidt | Udo | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 2 | Müller | Wolfgang | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Meyer | Günther | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Krause | Helmut | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 5 | Fischer | Hugo | NULL | NULL | NULL | NULL | NULL | NULL |
Beim NATURL LFET JOIN werden die Spalten mitarbeiter.kfz_id und kfz.kfz_id zur gemeinsamen Spalte kfz_id zusammengeführt.
OUTER JOINs in der Praxis
OUTER JOINS führen in der Regel zu Tabellenverbünden mit Nullwerten. Nützlich ist dies beispielsweise dann, wenn man genau diese herausstellen möchte. In unserem Beispiel wurde der Porsche Boxster bisher noch keinem Mitarbeiter zugewiesen. Der Tabelle „kfz“ ist dies nicht zu entnehmen. Und auch die Tabelle „mitarbeiter“ zeigt lediglich, dass Hugo Fischer bisher keinen Firmenwagen nutzt – nicht jedoch, welcher noch frei ist. Nach einem FULL JOIN über beide Tabellen ist auf einen Blick ersichtlich, dass Hugo zukünftig den Porsche fahren könnte.
