Join (SQL)

Ein SQL-Join (deutsch: Verbund) bildet aus den Datensätzen zweier Tabellen einer relationalen Datenbank eine Ergebnistabelle, deren Datensätze Attribute beider Tabellen entsprechend einer angegebenen Verbundbedingung enthält. Er ist die Umsetzung des Konzepts des Verbunds der relationalen Algebra in der Abfragesprache SQL. Der ISO-Standard für SQL beschreibt folgende Arten von JoinsC.J.Date with Hugh Darwen: A Guide to the SQL Standard, Fourth Edition, Addison-Wesley, 1997, S. 135ff.Die verschiedenen Arten des Verbunds werden im SQL-Standard SQL:2011 in Part 2 Foundation Abschnitt 7.7 beschrieben.: Das kartesische Produkt CROSS JOIN, den inneren Verbund in der Form des natürlichen Verbunds NATURAL JOIN und anderer Varianten, sowie den äußeren Verbund in den Formen LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN. Als Spezialfall kann man auch einen Verbund einer Tabelle mit sich selbst bilden, dies wird als Self Join bezeichnet.

Beispiel-Tabellen Zur Demonstration der verschiedenen Formen des Verbunds werden als Beispiel die Tabellen Mitarbeiter und Abteilung verwendet. Über einen Mitarbeiter wird die identifizierende MId, sein Name und die AbtId der Abteilung, bei der er beschäftigt ist, verzeichnet. Die Abteilung hat die Attribute AbtId, die die Abteilung identifiziert sowie ihren Namen. Ein Verbund verknüpft Informationen aus den beiden Tabellen.

+ MitarbeiterMId Name AbtIdM1 Müller 31M2 Schmidt 32M3 Müller 32M4 Meyer NULL

+ Abteilung AbtId AbtName 31 Verkauf 32 Technik 33 Marketing

Die Beispiel-Tabellen haben folgende Besonderheiten: Die Mitarbeiterin oder der Mitarbeiter namens „Meyer“ ist keiner Abteilung zugeordnet. Der Wert NULL als AbtId bedeutet in SQL, dass dieser Wert unbekannt ist. Die Abteilung „Marketing“ hat keine zugeordneten Mitarbeiter.

Am Beispiel dieser beiden Tabellen kann man die verschiedenen Formen des Verbunds veranschaulichen.

Kartesisches Produkt (CROSS JOIN) Der CROSS JOIN zweier Tabellen bildet das kartesische Produkt der Datensätze der beiden Tabellen. Dabei wird jeder Datensatz der ersten Tabelle mit jedem anderen der zweiten Tabelle verknüpft. Wenn die beiden Tabellen gleichnamige Attribute haben, werden sie durch das Voranstellen des Tabellennamens ergänzt.

Beispiel des CROSS JOINs: SELECT * FROM Mitarbeiter CROSS JOIN Abteilung

Die explizite Benennung des kartesischen Produkts wurde mit SQL-Standard SQL-92 eingeführt. Im SQL-Standard von 1989 erhält man dasselbe Ergebnis mit folgender AnweisungDie Syntax von SQL92 hebt durch die Schlüsselworte CROSS JOIN die Absicht hervor, dass das kartesische Produkt gebildet werden soll.:

SELECT * FROM Mitarbeiter, Abteilung

Das Ergebnis hat 4 × 3 Datensätze, weil es 4 Mitarbeiter und 3 Abteilungen gibt; es besteht aus allen möglichen Kombinationen der Datensätze der beiden Tabellen. Da die AbtId in beiden Tabellen vorkommt, werden die beiden Attribute in der Ergebnistabelle durch das Voranstellen des Tabellennamens eindeutig gemacht.

MId Name Mitarbeiter.AbtId Abteilung.AbtId AbtNameM1 Müller 31 31 VerkaufM1 Müller 31 32 TechnikM1 Müller 31 33 MarketingM2 Schmidt 32 31 VerkaufM2 Schmidt 32 32 TechnikM2 Schmidt 32 33 MarketingM3 Müller 32 31 VerkaufM3 Müller 32 32 TechnikM3 Müller 32 33 MarketingM4 Meyer NULL 31 VerkaufM4 Meyer NULL 32 TechnikM4 Meyer NULL 33 Marketing

Innerer Verbund

Der innere Verbund zweier Tabellen hat zum Ergebnis die Kombination der Datensätze der beteiligten Tabellen, die die Verbundbedingung erfüllen. In der Regel verlangt die Verbundbedingung die Gleichheit von Werten bestimmter Attribute in den beiden Tabellen. Sie kann aber auch andere Vergleichsoperatoren als die Gleichheit enthalten.Im Internet findet man sehr viele Artikel über den Verbund, bei dem er durch Venn-Diagramme für Mengenoperationen veranschaulicht wird. In diesen Artikeln wird gern der innere Verbund von zwei Tabellen gebildet, die dasselbe Schema haben und nur in diesem Spezialfall ist der natürliche Verbund in der Tat identisch mit der Schnittmenge der beiden Tabellen. Im Allgemeinen ist dies aber nicht der Fall. In unserem Beispiel kann man die Schnittmenge der beiden Tabellen Mitarbeiter und Abteilung gar nicht bilden, weil die Datensätze der beiden Tabellen unterschiedlichen Aufbau haben und nicht vergleichbar sind. Die Visualisierung der Verbund-Operatoren durch Venn-Diagramme ist im Allgemeinen unzutreffend und deshalb irreführend.

NATURAL JOIN

Die grundlegende Form des inneren Verbunds ist der natürliche Verbund NATURAL JOIN.

Beim natürlichen Verbund werden die Datensätze der beiden beteiligten Tabellen miteinander verknüpft, deren Werte an den gleichnamigen Attributen übereinstimmen. Es werden also nur die „passenden“ Kombinationen gebildet und nicht wie beim kartesischen Produkt alle möglichen Kombinationen.

Beispiel für den NATURAL JOIN: SELECT * FROM Mitarbeiter NATURAL JOIN Abteilung

In unserem Beispiel haben die beiden Tabellen ein gemeinsames Attribut, die AbtID. Im natürlichen Verbund wird dementsprechend jedem Mitarbeiter die Abteilung zugeordnet, deren AbtId bei ihm verzeichnet ist. Da nun in jeder Zeile der Wert von AbtId in beiden Tabellen identisch ist, erscheint das Attribut nur einmal in der Ergebnistabelle:

MIdNameAbtIDAbtNameM1Müller31VerkaufM2Schmidt32TechnikM3Müller32Technik

Da die Mitarbeiterin oder der Mitarbeiter namens „Meyer“ keiner Abteilung zugeordnet ist, erscheint „M4“ nicht im Ergebnis. Auch die Abteilung „Marketing“ kommt nicht vor, weil ihr ja gar kein Mitarbeiter zugeordnet ist.

JOIN ... USING ... Beim natürlichen Verbund werden die Kombinationen durch den Vergleich der Werte aller gleichnamigen Attribute der beiden Tabellen gebildet. Oft will man jedoch explizit angeben, welche der Attribute für das Verknüpfen der Datensätze verwendet werden sollen. Dafür gibt es die Form JOIN ... USING ...

In unserem Beispiel: SELECT * FROM Mitarbeiter JOIN Abteilung USING (AbtID)

Das Ergebnis ist in diesem Beispiel identisch mit dem des natürlichen Verbunds.

Diese Form des Verbunds ist der Variante mit NATURAL JOIN vorzuziehen, weil die explizite Angabe der Attribute sicherstellt, dass keine unerwünschten Kombinationen gebildet werden, wenn die beteiligten Tabellen erweitert werden. Wird in unserem Beispiel etwa in die Tabelle Mitarbeiter das neue Attribut Ort für den Wohnort des Mitarbeiters eingeführt und in der Tabelle Abteilung auch ein Attribut namens Ort für den Standort der Abteilung, dann werden beim NATURAL JOIN die Attribute AbtId und Ort für das Bilden der verknüpften Datensätze verglichen, was nicht der Intention entspricht, den Mitarbeitern ihre Abteilungen zuzuordnen.

JOIN ... ON ... Es ist auch möglich, einen Verbund zwischen Tabellen zu bilden, bei denen die Bezeichnungen der zu vergleichenden Attribute nicht übereinstimmen oder bei dem ein anderer Operator als = verwendet werden soll.

Für die Beispiel-Tabellen lautet eine Anweisung mit dieser Form des Verbunds SELECT * FROM Mitarbeiter JOIN Abteilung ON Mitarbeiter.AbtId = Abteilung.AbtId; Ergebnis:

MIdNameMitarbeiter.AbtIdAbteilung.AbtIdAbtNameM1Müller3131VerkaufM2Schmidt3232TechnikM3Müller3232Technik

In diesem Beispiel entspricht das Ergebnis der Anweisung mit JOIN ... ON ... dem mit JOIN ... USING .... Der Unterschied besteht nur darin, dass die verglichenen Attribute Mitarbeiter.AbtId und Abteilung.AbtId mit identischen Werten beide im Ergebnis ausgegeben werden.

Wenn man einen Verbund über Attribute mit unterschiedlichen Bezeichnungen bildet, dann kann JOIN ... USING ... nicht verwendet werden, sondern man muss die Anweisung mit JOIN ... ON ... bilden. (Auch dann erhält man bei SELECT * FROM ... identische Werte in den beiden Join-Attributen, von denen man jedoch durch explizites Angeben der gewünschten Attribute hinter SELECT nur eines ausgeben kann.)

Man kann vor die beiden Formen JOIN ... USING ... und JOIN ... ON ... auch das optionale Schlüsselwort INNER setzen, um zu unterstreichen, dass es sich um einen inneren Verbund handelt.

Im SQL-Standard von 1989 würde man die obige Anweisung so formulieren:SELECT * FROM Mitarbeiter, Abteilung WHERE Mitarbeiter.AbtId = Abteilung.AbtId In der ersten Form der Anweisung wird der Verbund explizit angegeben, während in der zweiten Variante zunächst ein kartesisches Produkt gebildet wird und der Verbund durch die anschließende Restriktion mit der Bedingung Mitarbeiter.AbtId = Abteilung.AbtId erreicht wird.

Die Formen des inneren Verbunds in den bisherigen Beispielen werden in der Literatur auch als Equijoin (deutsch: Gleichverbund) bezeichnet, weil in der Bedingung für den Verbund der Gleichheitsoperator verwendet wird. Es ist in der Variante JOIN ... ON ... auch möglich eine beliebige Bedingung für das Verknüpfen der Datensätze der beteiligten Tabellen anzugeben, z. B. mit dem Vergleichsoperator ≤. In diesem Fall bezeichnet man den Verbund als Theta-Join.

Äußerer Verbund (OUTER JOIN) In allen Formen des inneren Verbundes der Beispiel-Tabellen kommt der Mitarbeiter mit der MId „M4“ nicht vor, weil ihm ja keine Abteilung zugeordnet ist. Und auch die Abteilung „Marketing“ kommt nicht vor, weil sie keine Mitarbeiter hat.

Die Formen des Outer Joins (deutsch: äußerer Verbund) beziehen Datensätze in den Verbund ein, zu denen es keine Entsprechungen der Werte in den beiden Tabellen gibt. Der äußere Verbund muss also immer eingesetzt werden, wenn unbekannte oder fehlende Information im Spiel ist.

Sollen im Beispiel der Mitarbeiter und Abteilungen alle Mitarbeiter mit ihren Abteilungen ausgegeben werden, auch diejenigen, die keiner Abteilung zugeordnet sind, dann ist ein äußerer Verbund erforderlich.

LEFT OUTER JOIN Das Ergebnis von T1 LEFT OUTER JOIN T2 der Tabellen T1 und T2 enthält alle Datensätze der Tabelle T1 links des Schlüsselworts JOIN, selbst wenn es keinen korrespondierenden Datensatz der rechten Tabelle T2 gibt. Die fehlenden Werte aus T2 werden durch NULL aufgefüllt. Das Schlüsselwort OUTER ist in allen Beispielen nicht erforderlich, kann aber geschrieben werden, um einen Leser auf den äußeren Verbund aufmerksam zu machen.

Im Beispiel der Mitarbeiter und Abteilungen ergibt

SELECT * FROM Mitarbeiter LEFT OUTER JOIN Abteilung USING (AbtId)

MId Name AbtId AbtName M1 Müller 31 Verkauf M2 Schmidt 32 Technik M3 Müller 32 Technik M4 Meyer NULL NULL

Das Ergebnis enthält nun auch den Mitarbeiter mit der MId „M4“ und die Attribute aus der verknüpften Tabelle Abteilung sind NULL.

RIGHT OUTER JOIN Ein RIGHT OUTER JOIN bildet den inneren Verbund der beiden Tabellen und ergänzt ihn um je einen Datensatz für Datensätze in der rechten Tabelle, zu denen es keine Korrespondenz in der linken Tabelle gibt.

Im Beispiel der Mitarbeiter und Abteilungen ergibt

SELECT * FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId)

MId Name AbtId AbtName M1 Müller 31 Verkauf M2 Schmidt 32 Technik M3 Müller 32 Technik NULL NULL 33 Marketing

Das Ergebnis enthält nun einen Datensatz für die Abteilung „Marketing“, der kein Angestellter zugeordnet ist, weshalb die Attribute MId und Name NULL sind.

Ein weiteres Beispiel, bei dem der äußere Verbund benötigt wird: Es sollen alle Abteilungen mit der Anzahl ihrer Mitarbeiter ausgegeben werden. Da beim inneren Verbund zur Abteilung mit der AbtId 33 gar kein Datensatz ausgegeben werden würde, muss die Anweisung mit dem äußeren Verbund formuliert werden: SELECT AbtName, count(MId) as Mitarbeiterzahl FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId) GROUP BY AbtId, AbtNameErgebnis: AbtName Mitarbeiterzahl Verkauf 1 Technik 2 Marketing 0

FULL OUTER JOIN Ein FULL OUTER JOIN ist die Vereinigungsmenge der Ergebnisse des LEFT und des RIGHT OUTER JOINs.

Das Beispiel für Mitarbeiter und Abteilungen:

SELECT * FROM Mitarbeiter FULL OUTER JOIN Abteilung USING (AbtId)

MId Name AbtId AbtName M1 Müller 31 Verkauf M2 Schmidt 32 Technik M3 Müller 32 TechnikM4MeyerNULLNULL NULL NULL 33 Marketing