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 Joins^[1]^[2]:

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.

Mitarbeiter
MId	Name	AbtId
M1	Müller	31
M2	Schmidt	32
M3	Müller	32
M4	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 Anweisung^{[Anm 1]}:

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	AbtName
M1	Müller	31	31	Verkauf
M1	Müller	31	32	Technik
M1	Müller	31	33	Marketing
M2	Schmidt	32	31	Verkauf
M2	Schmidt	32	32	Technik
M2	Schmidt	32	33	Marketing
M3	Müller	32	31	Verkauf
M3	Müller	32	32	Technik
M3	Müller	32	33	Marketing
M4	Meyer	NULL	31	Verkauf
M4	Meyer	NULL	32	Technik
M4	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.^{[Anm 2]}

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:

MId	Name	AbtID	AbtName
M1	Müller	31	Verkauf
M2	Schmidt	32	Technik
M3	Müller	32	Technik

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:

MId	Name	Mitarbeiter.AbtId	Abteilung.AbtId	AbtName
M1	Müller	31	31	Verkauf
M2	Schmidt	32	32	Technik
M3	Müller	32	32	Technik

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.^[3]

Ä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 T₁ LEFT OUTER JOIN T₂ der Tabellen T₁ und T₂ enthält alle Datensätze der Tabelle T₁ links des Schlüsselworts JOIN, selbst wenn es keinen korrespondierenden Datensatz der rechten Tabelle T₂ gibt. Die fehlenden Werte aus T₂ 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, AbtName

Ergebnis:

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	Technik
M4	Meyer	NULL	NULL
NULL	NULL	33	Marketing

Weitere Varianten der Syntax des äußeren Verbunds sind im Syntaxdiagramm weiter unten enthalten.

Self Join

Ein Self Join ist ein Join einer Tabelle mit sich selbst. Das bedeutet, dass Datensätze der Tabelle mit anderen Datensätzen derselben Tabelle verglichen werden müssen. Damit man in SQL Werte der Datensätze derselben Tabelle vergleichen kann, muss man in der Anweisung explizite Bezeichnungen für zwei Tupelvariablen vergeben, die beide die Datensätze der Tabelle durchlaufen können.

Als Beispiel soll überprüft werden, ob in der Tabelle Mitarbeiter zwei Mitarbeiter mit gleichem Namen aber verschiedener MId vorkommen. Im folgenden Self Join werden die Tupelvariablen MA und MB für die Tabelle „Mitarbeiter“ definiert, um den Vergleich durchführen zu können.

SELECT MA.MId, MA.Name
FROM Mitarbeiter MA CROSS JOIN Mitarbeiter MB
WHERE MA.MId <> MB.MId AND MA.Name = MB.Name

Als Ergebnis wird ausgegeben:

MId	Name
M1	Müller
M3	Müller

Tatsächlich erzeugt SQL zu jeder Tabelle bei der Verarbeitung einer SELECT-Anweisung eine Tupelvariable, die normalerweise gleich heißt, wie die Tabelle selbst. Wenn die Anweisung zum Beispiel lautet select * from Mitarbeiter where Mitarbeiter.MId = 'M1' wird eine Tupelvariable verwendet, die wie die Tabelle Mitarbeiter heißt und als Inhalt jeden Datensatz der Tabelle annehmen kann. Die Filterbedingung MId = 'M1' wird dann dadurch überprüft, dass die Tupelvariable die Tabelle durchläuft. Es ist möglich, die Tupelvariable explizit zu benennen, etwa select * from Mitarbeiter M where M.MId = 'M1'.^[4] Dies wird beim Self Join eingesetzt, um zwei Tupelvariablen für ein und dieselbe Tabelle zu haben, wodurch deren Datensätze miteinander verglichen werden können.

Syntaxdiagramm

Folgendes Syntaxdiagramm zeigt alle Formen des Joins in SQL.^[2]

Im Syntaxdiagramm wird der Verbund von zwei „Tabellenreferenzen“ dargestellt. Eine „Tabellenreferenz“ kann selbst wieder eine Anweisung für einen Verbund sein, d. h., es können nicht nur Joins mit zwei, sondern mit mehreren Tabellen gebildet werden.

Stellen wir uns in unserem Beispiel vor, dass es eine dritte Tabelle Adresse gibt, die über die Attribut AdrId dem Mitarbeiter seine Adresse zuordnet. Dann ergibt folgende Anweisung die Zuordnung der Adresse und der Abteilung zum jeweiligen Mitarbeiter:

SELECT *
FROM Mitarbeiter JOIN Adresse using (AdrId)
                 JOIN Abteilung using (AbtId)

Der innere Verbund ist (bis auf die Reihenfolge der Attribute im Ergebnis) sowohl kommutativ als auch assoziativ. Der äußere Verbund ist nicht kommutativ, und im Allgemeinen auch nicht assoziativ. Werden in einer Anweisung mit mehreren Tabellen verschiedene Formen des Verbunds verwendet, ist es ratsam zur Klarheit Klammern zu verwenden.

Produktspezifisches

Die verschiedenen Datenbankmanagementsysteme weichen teilweise vom SQL-Standard ab oder haben andere Varianten für die Formulierung des äußeren Verbunds. Die folgenden Links verweisen auf die Dokumentation zum Join für einige der gängigen Produkte:

IBM Db2
- IBM Db2 unterstützt die Variante NATURAL JOIN nicht.
Microsoft SQL Server
- Der SQL-Dialekt für Microsoft SQL Server heißt Transact-SQL.
- Transact SQL unterstützt die Formen NATURAL JOIN und JOIN ... USING ... des Joins nicht, es gibt also lediglich die Variante mit ON, mit der sich aber alle Aufgabenstellungen bewältigen lassen.
MySQL
- MySQL unterstützt alle Formen des Joins entsprechend SQL-92.
- MySQL hat mit STRAIGHT JOIN eine spezielle Variante des Joins. Normalerweise entscheidet der Anfrageoptimierer eines Datenbankmanagementsystems, wie der Verbund tatsächlich ausgeführt wird. Mit STRAIGHT JOIN wird dem Optimierer von MySQL die Reihenfolge vorgegeben, wie der Join ausgeführt werden soll.
- MySQL unterstützt den FULL [OUTER] JOIN nicht. Diese Form des äußeren Verbunds kann durch eine Konstruktion aus LEFT/RIGHT OUTER JOIN zusammen mit dem Operator UNION erzeugt werden.
Oracle
- Oracle hat auch eine proprietäre Syntax für den äußeren Verbund, erst 2001 mit Version 9 wurde die Syntax von SQL-92 für den äußeren Verbund eingeführt.
- Oracle empfiehlt heute für den äußeren Verbund die dem SQL-Standard entsprechende Syntax.
PostgreSQL
- PostgreSQL unterstützt alle Formen des Joins entsprechend SQL-92. (Die Dokumentation von PostgreSQL enthält ein Tutorial zum Join.)
SQLite
- SQLite unterstützt nur den LEFT OUTER JOIN. Die beiden anderen Formen des äußeren Verbunds können durch den LEFT OUTER JOIN zusammen mit dem Operator UNION erzeugt werden.

Siehe auch

Literatur

C. J. Date with Hugh Darwen: A Guide to the SQL Standard. 4. Auflage. Addison-Wesley, 1997.
Wolfgang Panny mit Alfred Taudes: Einführung in den Sprachkern von SQL-99. Springer, 2000.
Gunter Saake, Kai-Uwe Sattler und Andreas Heuer: Datenbanken: Konzepte und Sprachen. 4. Auflage. mitp, 2010, S. 221 f. und 339 f.
Alfons Kemper und André Eckler: Datenbanksysteme: Eine Einführung. 6. Auflage. Oldenbourg, 2006, S. 125 f.

Anmerkungen

↑ Die Syntax von SQL92 hebt durch die Schlüsselworte CROSS JOIN die Absicht hervor, dass das kartesische Produkt gebildet werden soll.
↑ 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.

Einzelnachweise

↑ C.J.Date with Hugh Darwen: A Guide to the SQL Standard, Fourth Edition, Addison-Wesley, 1997, S. 135ff.
↑ ^a ^b Die verschiedenen Arten des Verbunds werden im SQL-Standard SQL:2011 in Part 2 Foundation Abschnitt 7.7 beschrieben.
↑ Gunther Saake, Kai-Uwe Sattler und Andreas Heuer: Datenbanken: Konzepte und Sprachen. mitp, S. 339.
↑ Gunther Saake, Kai-Uwe Sattler und Andreas Heuer: Datenbanken: Konzepte und Sprachen. mitp, S. 220 f.

[3] Die Syntax von SQL92 hebt durch die Schlüsselworte CROSS JOIN die Absicht hervor, dass das kartesische Produkt gebildet werden soll.

[4] 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.

[date-1] C.J.Date with Hugh Darwen: A Guide to the SQL Standard, Fourth Edition, Addison-Wesley, 1997, S. 135ff.

[ISO-2] Die verschiedenen Arten des Verbunds werden im SQL-Standard SQL:2011 in Part 2 Foundation Abschnitt 7.7 beschrieben.

[5] Gunther Saake, Kai-Uwe Sattler und Andreas Heuer: Datenbanken: Konzepte und Sprachen. mitp, S. 339.

[6] Gunther Saake, Kai-Uwe Sattler und Andreas Heuer: Datenbanken: Konzepte und Sprachen. mitp, S. 220 f.

[1]

[2]

[Anm 1]

[Anm 2]

[3]

[4]

Anweisungen und Klauseln	As • Case • Create • Delete • From • Having • Insert • Join • Merge • Null • Order by • Prepare • Select • Truncate • Union • Update • Where • With
Varianten	PL/SQL • Transact-SQL • Open SQL • Embedded SQL • PostgreSQL