SQL-JOIN – Abfragen über mehrere Datentabellen
IONOS Redaktion
Als SQL-JOIN (auf Deutsch: Verbund) bezeichnet man eine Operation in relationalen Datenbanken, die Abfragen über mehrere Datenbanktabellen ermöglicht. JOINs führen Daten zusammen, die in unterschiedlichen Tabellen gespeichert sind, und geben diese in gefilterter Form in einer Ergebnistabelle aus.
Das Prinzip des SQL-JOINs basiert auf der gleichnamigen Operation der relationalen Algebra – einer Kombination aus kartesischem Produkt und Selektion. Welche Daten der Ausgangstabellen in die Ergebnis-Tabelle übernommen werden, bestimmt der Anwender durch die Wahl eines JOIN-Typs und durch die Definition einer Selektionsbedingung.
Wir führen Sie in die mathematischen Grundlagen von SQL-JOINs ein, stellen verschiedene JOIN-Typen gegenüber und zeigen Ihnen anhand von Praxisbeispielen, wie Sie JOINs im Rahmen von Datenbankabfragen via SQL umsetzen.
Der Artikel zu SQL-JOIN setzt die Kenntnis bestimmter Konzepte des relationalen Datenbankmodells voraus, insbesondere was Relationen, Tupel, Attribute oder Schlüssel betrifft. Eine entsprechende Einführung bietet unser Grundlagenartikel zu relationalen Datenbanken.
Wie funktionieren SQL-JOINs?
Anschaulich darstellen lässt sich das Grundprinzip des SQL-JOINs, wenn man sich die Datenbankoperation über deren Teiloperationen herleitet. Grundlage eines jeden JOINs sind folgende Operationen der relationalen Algebra:
- Kartesisches Produkt
- Selektion
Das kartesische Produkt
Das kartesische Produkt (auch Kreuzprodukt) ist eine Operation der Mengenlehre, bei der zwei oder mehr Mengen miteinander verknüpft werden. Im relationalen Datenbankmodell kommt das kartesische Produkt zum Einsatz, um Tupel-Mengen in Form von Tabellen miteinander zu verbinden. Das Ergebnis dieser Operation ist wiederum eine Menge geordneter Tupel, bei der jedes Tupel aus einem Element jeder Ausgangsmenge besteht.
Als Operator für das kartesische Produkt kommt in der relationalen Algebra das Multiplikationszeichen (×) zum Einsatz.
Dazu ein Beispiel:
Das kartesische Produkt A × B der beiden Mengen A = {x, y, z} und B = {1, 2, 3} ist:
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
Veranschaulichen lässt sich die Rechnung anhand folgender Grafik:
Zu beachten ist dabei die Reihenfolge der Paarbildung. Das kartesische Produkt A × B entspricht beispielsweise nicht derselben Menge wie das kartesische Produkt von B × A.
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
B × A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}
In der SQL-Terminologie wird eine Operation, bei der das kartesische Produkt aus zwei Datenbanktabellen gebildet wird, als CROSS JOIN bezeichnet. In der Praxis kommen CROSS JOINs aufgrund der ungefilterten Ergebnismenge nur selten zum Einsatz.
Die Selektion
Bei der Selektion handelt es sich um eine Operation der relationalen Algebra, die es ermöglicht, bestimmte Tupel einer Ausgangsmenge auszuwählen und als Ergebnismenge auszugeben. Welche Tupel in die Ergebnismenge aufgenommen werden, lässt sich über einen Vergleichsausdruck festlegen. Das Ergebnis der Selektion ist somit eine Menge von Tupeln, die die im Vergleichsausdruck definierte Selektionsbedingung erfüllen. Als Operator kommt der griechische Buchstabe Sigma (σ) zum Einsatz. Die Operation wird folgendermaßen notiert:
σF (R)
Der Platzhalter „F“ entspricht dem Vergleichsausdruck, einer Formel aus logischen Prädikaten, die die Selektionsbedingung definieren. R steht für den zu selektierenden Datenbestand. Alternativ bietet sich die lineare Schreibweise R[F] an.
Zum Formulieren von Selektionsbedingungen stehen die üblichen Vergleichsoperatoren zur Verfügung: beispielsweise gleich (=), größer (>) oder kleiner (<).
Wir erläutern die Selektion anhand eines Beispiels, das wir bereits im Grundlagentext zum relationalen Datenbankmodell eingeführt haben. Folgende Tabelle zeigt fiktive Personaldaten, die ein Unternehmen zu seinen Mitarbeitern erfasst haben könnte. Für jeden Mitarbeiter sind die Personalnummer (m_id), Angaben zum Namen (nachname, vorname), zur Sozialversicherungsnummer (svn), zur Adresse (str, plz, ort) sowie zum zugewiesenen Firmenwagen (kfz_id) angegeben.
| Tabelle: mitarbeiter | ||||||||
|---|---|---|---|---|---|---|---|---|
| m_id | nachname | vorname | svn | str | nr | plz | ort | kfz_id |
| 1 | Schmidt | Udo | 25 120512 S 477 | Hauptstraße | 1 | 11111 | Musterhausen | 3 |
| 2 | Müller | Wolfgang | 25 100615 M 694 | Bahnhofstraße | 2 | 22222 | Musterheim | 1 |
| 3 | Meyer | Günther | 25 091225 M 463 | Am Marktplatz | 3 | 33333 | Musterfelde | 1 |
| 4 | Krause | Helmut | 25 170839 K 783 | Waldweg | 4 | 44444 | Musterwalde | 2 |
Möchten wir die Tabelle „mitarbeiter“ nun so selektieren, dass lediglich die Mitarbeiter angezeigt werden, die den Firmenwagen mit der kfz_id 1 fahren, könnten wir folgendermaßen vorgehen:
σkfz_id=1(mitarbeiter)
Wir rufen lediglich die Tupel ab, bei denen der Wert in der Spalte kfz_id gleich 1 ist.
Das Ergebnis entspricht folgender Tabelle.
| Tabelle: mitarbeiter (selektiert) | ||||||||
|---|---|---|---|---|---|---|---|---|
| m_id | nachname | vorname | svn | str | nr | plz | ort | kfz_id |
| 2 | Müller | Wolfgang | 25 100615 M 694 | Bahnhofstraße | 2 | 22222 | Musterheim | 1 |
| 3 | Meyer | Günther | 25 091225 M 463 | Am Marktplatz | 3 | 33333 | Musterfelde | 1 |
In der Datenbanksprache SQL werden Selektionsbedingungen mithilfe des Befehls WHERE definiert.
SELECT * FROM mitarbeiter WHERE mitarbeiter.kfz_id = 1;Erfüllt ein Tupel die Bedingung kfz_id = 1, sollen für diesen die Werte aller Spalten ausgegeben werden.
Der Asterisk (*) steht in der SQL-Sytax stellvertretend für alle Spalten einer Tabelle.
Kombination aus kartesischem Produkt und Selektion
Alle gängigen JOIN-Typen kombinieren das kartesische Produkt mit einer Selektionsbedingung. Um eine solche Datenbankoperation zu erklären, reduzieren wir die Tabelle „mitarbeiter“ der Anschaulichkeit halber auf vier Spalten. Zudem führen wir die Tabelle kfz ein, in der Detail-Informationen zum Fuhrpark des Unternehmens gespeichert werden.
Beide Tabellen sind über eine Fremdschlüsselbeziehung mit einander verknüpft. Der Primärschlüssel der Tabelle „kfz“ (die kfz_id) fungiert in der Tabelle „mitarbeiter“ als Fremdschlüssel.
| 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 |
| 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 |
Die Auslagerung von Informationen in verschiedene Datenbanktabellen ist ein grundlegendes Konzept des relationalen Datenbankmodells. Die Vorteile eines solchen Datenbankdesigns sowie dessen Umsetzung thematisieren wir in einem weiterführenden Artikel zur Normalisierung von relationalen Datenbanken.
Möchte man die beiden Tabellen zusammenführen und gleichzeitig relevante Tupel selektieren, kombiniert man die zuvor eingeführten Datenbankoperationen:
σkfz_id=kfz_id(mitarbeiter × kfz)
Dabei wird zunächst das kartesische Produkt mitarbeiter × kfz gebildet. Das (Zwischen-)Ergebnis ist ein CROSS JOIN – eine Ergebnistabelle, in der jeder Tupel der Tabelle „mitarbeiter“ mit jedem Tupel der Tabelle „kfz“ kombiniert wird.
| Tabelle: Kartesisches Produkt „mitarbeiter“ × „kfz“ | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| m_id | nachname | vorname | mitarbeiter.kfz_id | kfz.kfz_id | marke | modell | kennzeichen | baujahr | hu |
| 1 | Schmidt | Udo | 3 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 1 | Schmidt | Udo | 3 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 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 |
| 2 | Müller | Wolfgang | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 2 | Müller | Wolfgang | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 3 | Meyer | Günther | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 3 | Meyer | Günther | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 3 | Meyer | Günther | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
| 4 | Krause | Helmut | 2 | 1 | VW | Caddy | B KH 778 | 2016 | 18.12.2018 |
| 4 | Krause | Helmut | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 12.08.2019 |
| 4 | Krause | Helmut | 2 | 3 | BMW | X6 | B MW 780 | 2017 | 01.09.2018 |
Anschließend werden lediglich die Tupel selektiert, bei denen die kfz_id der Tabelle „kfz“ mit der kfz_id der Tabelle „mitarbeiter“ übereinstimmt. Selektionsbedingung ist somit, dass der Fremdschlüssel der Tabelle „mitarbeiter“ dem Primärschlüssel der Tabelle „kfz“ entspricht.
Als (End-)Ergebnis erhalten wir eine Tabelle, die beide Ausgangstabellen ohne Redundanzen zusammenführt.
| Tabelle: JOIN über „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 |
Als Kombination aus kartesischem Produkt und anschließender Selektion fassen JOINs beide Operationen in einer gemeinsamen zusammen. Als Operator wird das Bowtie-Symbol (⋈) genutzt.
Es gilt somit:
σkfz_id=kfz_id(mitarbeiter × kfz) := mitarbeiter⋈kfz_id=kfz_idkfz
Die Operation σkfz_id=kfz_id(mitarbeiter × kfz) entspricht einem JOIN über die Tabellen „mitarbeiter“ und „kfz“ mit der Voraussetzung kfz_id=kfz_id.
Übertragen auf die SQL-Syntax entspräche die oben dargestellte Operation folgendem Statement:
SELECT * FROM mitarbeiter INNER JOIN kfz ON mitarbeiter.kfz_id = kfz.kfz_id;Der INNER JOIN ist einer der wichtigsten JOINs, die im Rahmen von Datenbankabfragen zum Einsatz kommen. Mitunter werden jedoch spezielle JOIN-Typen benötigt, um das gewünschte Ergebnis zu erreichen.
SQL-JOIN-Typen
Im relationalen Datenbankmodell werden unterschiedliche SQL-JOIN-Typen genutzt, die es ermöglichen, Abfragen über einen Verbund von Datenbanktabellen auszuführen. Voraussetzung dafür ist, dass die ausgewählten Tabellen über Fremdschlüsselbeziehungen miteinander verknüpft sind.
Zu den wichtigsten JOIN-Typen gehören folgende:
- INNER JOINs: Bei einem INNER JOIN handelt es sich um eine gefilterte Form des CROSS JOINs, bei der in der Ergebnismenge nur die Tupel beider Ausgangstabellen zusammengeführt werden, die die vom Anwender definierte Selektionsbedingung erfüllen.
- OUTER JOINs: Der OUTER JOIN stellt eine Erweiterung des INNER JOINS dar. Ergebnismenge eines OUTER JOINS enthält die Tupel beider Ausgangstabellen, die die vom Anwender definierte Selektionsbedingung erfüllen, wie auch alle restlichen Tupel der ersten Tabelle, der zweiten Tabelle oder beider Tabellen. OUTER JOINS werden entsprechend als LEFT OUTER JOIN, RIGHT OUTER JOIN oder FULL OUTER JOIN realisiert.
Eine detaillierte Beschreibung von INNER JOINs und OUTER JOINs finden Sie in den weiterführenden Artikeln zu diesen JOIN-Typen.
Verdeutlichen lassen sich die Unterschiede zwischen INNER JOINs und den verschiedenen Varianten des OUTER JOINS durch Mengendiagramme. Folgende Grafik ist eine bildliche Darstellung der vorgestellten JOIN-Typen:
Unabhängig von der Unterscheidung zwischen INNER JOIN und OUTER JOIN lassen sich SQL-JOINs zudem als folgende JOIN-Typen klassifizieren:
- EQUI JOIN
- NON EQUI JOIN
- SELF JOIN
INNER JOINs und OUTER JOINs lassen sich als EQUI JOINs und NON EQUI JOINs umsetzen. Alle bisher vorgestellten JOIN-Beispiele stellen EQUI JOINs dar. EQUI JOINs sind dadurch gekennzeichnet, dass sie ausschließlich das Gleichheitszeichen als Vergleichsoperator (=) zulassen.
Die Selektionsbedingung eines EQUI JOINs ist somit immer die Gleichheit von Spalten-Werten.
Prinzipiell sind JOINS jedoch (ebenso wie die Selektion in der relationalen Algebra) nicht auf die Gleichheit von Spalten beschränkt. Mögliche Vergleichsoperatoren sind:
| Vergleichsoperator | Bedeutung |
|---|---|
| = | gleich |
| < | kleiner als |
| > | größer als |
| ≤ | kleiner oder gleich |
| ≥ | größer oder gleich |
| <> | ungleich |
| != | ungleich |
Seit SQL-92 bietet die Datenbanksprache mit dem Schlüsselwort USING eine Kurzschreibweise für EQUI JOINS. Diese setzt jedoch voraus, dass die betreffenden Spalten den gleichen Namen haben, was nicht zwangsläufig der Fall sein muss.
Das folgende Beispiel zeigt zwei unterschiedliche SQL-Statements, die zum selben Ergebnis führen. Im ersten Statement definieren wird die JOIN-Bedienung mithilfe des Schlüsselworts ON explizit. Beim zweiten Statement verwenden wir die Kurzschreibweise mit dem Schlüssel USING.
SELECT * FROM mitarbeiter INNER JOIN kfz ON mitarbeiter.kfz_id = kfz.kfz_id;SELECT * FROM mitarbeiter INNER JOIN kfz USING kfz_id;NON EQUI JOINs hingegen schließen Operationen auf Basis der Gleichheit von Spalten aus. Es sind somit alle Vergleichsoperationen mit Ausnahme des Gleichheitszeichens (=) erlaubt.
Da Beziehungen in relationalen Datenbanken in der Regel über die Gleichheit von Primär- und Fremdschlüssel definiert werden, sind NON EQUI JOIN im relationalen Datenbankmodell von nachrangiger Bedeutung. Nicht zuletzt deshalb, weil diese ebenso wie bei CROSS JOINs oft zu sehr vielen Ergebnisdatensätzen führen.
Ein SELF JOIN schließlich ist eine Sonderform des SQL-JOINs, bei der eine Datenbanktabelle mit sich selbst verknüpft wird. Prinzipiell lässt sich jeder JOIN-Typ als SELF JOIN ausführen.
Werden zwei Tabellen über gleichnamige Spalten verknüpft, spricht man von einem NATURAL JOIN. Ein NATURAL JOIN wird über das gleichnamige Schlüsselwort standardmäßig als INNER JOIN realisiert. Auf diesen JOIN-Typ festgelegt sind NATURAL JOINs hingegen nicht. Möglich ist ebenso ein NATURAL LEFT OUTER JOIN oder ein NATURAL RIGHT OUTER JOIN.
Da die Verknüpfung bei NATURAL JOINs über gleichnamige Spalten erfolgt, werden die jeweiligen Werte in der Ergebnismenge nicht doppelt ausgegeben, sondern zu einer gemeinsamen Spalte zusammengeführt. Beispiele zu NATURAL JOINs finden Sie in den weiterführenden Artikeln zu INNER JOINs und OUTER JOINs.
