JOINs: Anwendung
Einführung
Stell dir vor, du erhältst die Aufgabe, eine vollständige Mitarbeiterliste für ein Reporting zu erstellen. Dabei soll nicht nur der Name jedes Mitarbeiters erscheinen, sondern auch die zugehörige Abteilung, das Projekt und der Standort. Wenn du diese Daten aus mehreren Tabellen abrufen musst, reicht ein einfaches SELECT nicht mehr aus.
Genau hier kommen JOINs ins Spiel. Sie ermöglichen es dir, Daten aus unterschiedlichen Tabellen miteinander zu verknüpfen und so vollständige, praxisrelevante Auswertungen zu erstellen. Ohne JOINs wären komplexe Abfragen in relationalen Datenbanken kaum möglich.
In dieser Lerneinheit lernst du die verschiedenen JOIN-Arten kennen und verstehst, in welchen Situationen sie eingesetzt werden. Du wirst sehen, wie du mit INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN und SELF JOIN genau die Datensätze erhältst, die du für deine Aufgaben benötigst.
Lernziele
Nach dieser Lerneinheit kannst du:
- JOIN-Typen unterscheiden: Du erkennst die Unterschiede zwischen INNER, LEFT, RIGHT, FULL, CROSS und SELF JOIN.
- SQL-Abfragen formulieren: Du schreibst JOIN-Abfragen mit Aliassen und ON-Bedingungen korrekt.
- NULL-Verhalten verstehen: Du erklärst, wie OUTER JOINs mit fehlenden Werten umgehen und setzt Filter richtig.
- Ergebnisse interpretieren: Du analysierst JOIN-Ergebnisse und prüfst, ob sie der Anforderung entsprechen.
Wichtige Fachbegriffe
Bevor wir uns die einzelnen JOINs genauer anschauen, klären wir zunächst ein paar wichtige Fachbegriffe:
- JOIN-Klausel: Der Teil einer SQL-Anweisung, der festlegt, wie zwei oder mehr Tabellen miteinander verknüpft werden (z. B.
INNER JOIN,LEFT JOIN). - ON-Bedingung: Regel für die Verknüpfung, definiert, welche Spalten verglichen werden müssen, damit Zeilen aus beiden Tabellen zusammengehören. Meist wird ein Primärschlüssel mit einem Fremdschlüssel verglichen (z. B.
ON mitarbeiter.abteilungs_id = abteilung.id). - Alias (AS): Ein kurzer, temporärer Name für eine Tabelle innerhalb einer Abfrage (z. B.
FROM Mitarbeiter AS m). Macht den Code kürzer und ist bei SELF JOINs zwingend notwendig. - Primärschlüssel: Eine Spalte (oder Kombination), die jede Zeile in einer Tabelle eindeutig identifiziert (z. B.
MA_IDin der Mitarbeiter-Tabelle). - Fremdschlüssel: Eine Spalte in einer Tabelle, die auf den Primärschlüssel einer anderen Tabelle verweist und so eine Beziehung herstellt (z. B.
Abt_IDin der Mitarbeiter-Tabelle).
INNER JOIN (Die Schnittmenge)
Funktionsweise Ein INNER JOIN liefert ausschließlich Datensätze zurück, für die in beiden Tabellen ein passender Wert in der ON-Bedingung gefunden wird.
INNER JOIN (Die Schnittmenge)
Anforderung “Erstellen Sie eine Namensliste aller Mitarbeiter, die einer Abteilung zugeordnet sind.”
Benötigte Tabellen: Mitarbeiter
| MA_ID | Vorname | Nachname | Abt_ID |
|---|---|---|---|
| 1 | Julia | Wagner | 10 |
| 2 | Kevin | Huber | 20 |
| 3 | Sandra | Beck | 10 |
| 4 | Tom | Scholz | NULL |
Abteilung:
| Abt_ID | Abt_Name |
|---|---|
| 10 | Entwicklung |
| 20 | Vertrieb |
| 30 | Personal |
SQL-Abfrage
SELECT m.Vorname, m.Nachname, a.Abt_Name
FROM Mitarbeiter AS m
INNER JOIN Abteilung AS a ON m.Abt_ID = a.Abt_ID;Code-Erklärung im Detail
SELECT m.Vorname, m.Nachname, a.Abt_Name: Wir wählen die Spalten aus, die wir im Ergebnis sehen wollen. Die Präfixe m. und a. (Aliase) machen eindeutig, aus welcher Tabelle die jeweilige Spalte stammt.FROM Mitarbeiter AS m: Dies legt die Mitarbeiter-Tabelle als linke Ausgangstabelle für unsere Abfrage fest. Wir geben ihr den Kurznamen (Alias) m.INNER JOIN Abteilung AS a: Wir weisen die Datenbank an, die Tabelle Abteilung (mit dem Alias a) mit der Mitarbeiter-Tabelle zu verbinden.ON m.Abt_ID = a.Abt_ID: Dies ist die entscheidende Verknüpfungsregel. Die Datenbank soll nur die Zeilen kombinieren, bei denen der Wert in der Spalte Abt_ID der Mitarbeitertabelle (m) exakt mit dem Wert in der Spalte Abt_ID der Abteilungstabelle (a) übereinstimmt.
Ergebnis der Abfrage
| Vorname | Nachname | Abt_Name |
|---|---|---|
| Julia | Wagner | Entwicklung |
| Kevin | Huber | Vertrieb |
| Sandra | Beck | Entwicklung |
LEFT (OUTER) JOIN (Die linksseitige Erweiterung)
Funktionsweise Gibt alle Datensätze der linken Tabelle zurück, ergänzt um passende Daten der rechten Tabelle. Wo keine Übereinstimmung existiert, wird NULL verwendet.
LEFT (OUTER) JOIN (Die linksseitige Erweiterung)
Anforderung „Listen Sie alle Mitarbeiter auf und geben Sie, falls vorhanden, deren Projektnamen an.“
Benötigte Tabellen: Mitarbeiter
| MA_ID | Vorname | Nachname |
|---|---|---|
| 1 | Julia | Wagner |
| 2 | Kevin | Huber |
| 3 | Sandra | Beck |
| 4 | Tom | Scholz |
Projekt:
| Projekt_ID | Projekt_Name | MA_ID |
|---|---|---|
| P100 | ”Neues CRM” | 1 |
| P200 | ”Webshop Relaunch” | 1 |
| P300 | ”Cloud-Migration” | 2 |
| P400 | ”Interne Schulung” | 5 |
SQL-Abfrage
SELECT m.Vorname, m.Nachname, p.Projekt_Name
FROM Mitarbeiter AS m
LEFT JOIN Projekt AS p ON m.MA_ID = p.MA_ID;Code-Erklärung im Detail
FROM Mitarbeiter AS m: Die Mitarbeiter-Tabelle ist unsere linke Tabelle. Das bedeutet, jeder Mitarbeiter aus dieser Tabelle wird im Endergebnis erscheinen.LEFT JOIN Projekt AS p: Wir verknüpfen die Projekt-Tabelle (p) als optionale Informationsquelle.ON m.MA_ID = p.MA_ID: Die Verknüpfung erfolgt über die Mitarbeiter-ID. Für jeden Mitarbeiter aus m sucht die Datenbank nach passenden Einträgen in p. Findet sie keine, bleiben die Mitarbeiterdaten trotzdem erhalten und die Spalte p.Projekt_Name wird mit NULL aufgefüllt.
Ergebnis:
| Vorname | Nachname | Projekt_Name |
|---|---|---|
| Julia | Wagner | ”Neues CRM” |
| Julia | Wagner | ”Webshop Relaunch” |
| Kevin | Huber | ”Cloud-Migration” |
| Sandra | Beck | NULL |
| Tom | Scholz | NULL |
RIGHT (OUTER) JOIN (Die rechtsseitige Erweiterung)
Funktionsweise Gibt alle Datensätze der rechten Tabelle zurück, ergänzt um passende Daten der linken Tabelle.
RIGHT (OUTER) JOIN (Die rechtsseitige Erweiterung)
Anforderung „Erstellen Sie eine Übersicht aller Abteilungen und listen Sie die zugehörigen Mitarbeiter auf.“
Benötigte Tabellen: Mitarbeiter
| MA_ID | Vorname | Nachname | Abt_ID |
|---|---|---|---|
| 1 | Julia | Wagner | 10 |
| 2 | Kevin | Huber | 20 |
| 3 | Sandra | Beck | 10 |
| 4 | Tom | Scholz | NULL |
Abteilung:
| Abt_ID | Abt_Name |
|---|---|
| 10 | Entwicklung |
| 20 | Vertrieb |
| 30 | Personal |
SQL-Abfrage
SELECT m.Vorname, m.Nachname, a.Abt_Name
FROM Mitarbeiter AS m
RIGHT JOIN Abteilung AS a ON m.Abt_ID = a.Abt_ID;Code-Erklärung im Detail
RIGHT JOIN Abteilung AS a: Abteilung ist die rechte Tabelle. Das Schlüsselwort RIGHT sorgt dafür, dass jede Abteilung aus dieser Tabelle im Ergebnis erscheint, egal ob Mitarbeiter zugeordnet sind oder nicht.ON m.Abt_ID = a.Abt_ID: Wenn für eine Abteilung in a keine passende Abt_ID in m gefunden wird, werden die Spalten aus m (Vorname, Nachname) mit NULL aufgefüllt.
Ergebnis:
| Vorname | Nachname | Abt_Name |
|---|---|---|
| Julia | Wagner | Entwicklung |
| Sandra | Beck | Entwicklung |
| Kevin | Huber | Vertrieb |
| NULL | NULL | Personal |
JOINs über mehr als zwei Tabellen
Anforderung „Listen Sie alle Mitarbeiter mit ihrer Abteilung und dem zugehörigen Standort (Stadt) auf.“
Benötigte Tabellen: Mitarbeiter
| MA_ID | Vorname | Nachname | Abt_ID |
|---|---|---|---|
| 1 | Julia | Wagner | 10 |
| 2 | Kevin | Huber | 20 |
| 3 | Sandra | Beck | 10 |
Abteilung:
| Abt_ID | Abt_Name | Standort_ID |
|---|---|---|
| 10 | Entwicklung | S1 |
| 20 | Vertrieb | S2 |
Standort:
| Standort_ID | Stadt |
|---|---|
| S1 | Berlin |
| S2 | Hamburg |
SQL-Abfrage
SELECT
m.Vorname,
m.Nachname,
a.Abt_Name,
s.Stadt
FROM
Mitarbeiter AS m
INNER JOIN
Abteilung AS a ON m.Abt_ID = a.Abt_ID
INNER JOIN
Standort AS s ON a.Standort_ID = s.Standort_ID;Code-Erklärung im Detail
FROM Mitarbeiter AS m INNER JOIN Abteilung AS a ...: Zuerst werden Mitarbeiter und Abteilung verbunden. Das Ergebnis ist eine temporäre, virtuelle Tabelle.INNER JOIN Standort AS s ON a.Standort_ID = s.Standort_ID: Diese temporäre Tabelle wird nun mit der Standort-Tabelle verknüpft. Die Verknüpfungsregel a.Standort_ID = s.Standort_ID nutzt eine Spalte aus der bereits angebundenen Abteilung-Tabelle (a), um die Verbindung zur neuen Tabelle s herzustellen. So entsteht eine Kette: m → a → s.
Ergebnis:
| Nachname | Abt_Name | Stadt |
|---|---|---|
| Wagner | Entwicklung | Berlin |
| Huber | Vertrieb | Hamburg |
| Beck | Entwicklung | Berlin |
SELF JOIN (Hierarchien abfragen)
Funktionsweise:
Verknüpft eine Tabelle mit sich selbst, um hierarchische oder vergleichende Beziehungen zwischen Datensätzen in derselben Tabelle abzufragen. Technisch gesehen ist es ein normaler JOIN, bei dem die Tabelle jedoch unter zwei verschiedenen Aliasnamen auftritt, um sich selbst wie zwei getrennte Tabellen zu behandeln.
SELF JOIN (Hierarchien abfragen)
Anforderung „Erstellen Sie eine Liste aller Mitarbeiter und geben Sie den Nachnamen ihres jeweiligen Vorgesetzten an.“
Benötigte Tabelle: Mitarbeiter
| MA_ID | Nachname | Vorgesetzten_ID |
|---|---|---|
| 1 | Wagner | 3 |
| 2 | Huber | 3 |
| 3 | Beck | NULL |
| 4 | Scholz | 1 |
SQL-Abfrage
SELECT
m.Nachname AS Mitarbeiter,
v.Nachname AS Vorgesetzter
FROM
Mitarbeiter AS m
LEFT JOIN
Mitarbeiter AS v ON m.Vorgesetzten_ID = v.MA_ID;Code-Erklärung im Detail
FROM Mitarbeiter AS m: Wir lesen die Tabelle Mitarbeiter ein und geben ihr den Alias m, um die “Mitarbeiter”-Rolle darzustellen.LEFT JOIN Mitarbeiter AS v: Wir lesen dieselbe Tabelle Mitarbeiter ein zweites Mal, geben ihr aber den Alias v, um die “Vorgesetzten”-Rolle darzustellen. Der LEFT JOIN ist wichtig, damit auch Mitarbeiter ohne Vorgesetzten im Ergebnis erscheinen.ON m.Vorgesetzten_ID = v.MA_ID: Wir verknüpfen die Fremdschlüsselspalte Vorgesetzten_ID aus der Mitarbeiter-Sicht (m) mit der Primärschlüsselspalte MA_ID aus der Vorgesetzten-Sicht (v).
Ergebnis:
| Mitarbeiter | Vorgesetzter |
|---|---|
| Wagner | Beck |
| Huber | Beck |
| Beck | NULL |
| Scholz | Wagner |
FULL OUTER JOIN (Die vollständige Vereinigung)
Funktionsweise Kombiniert die Ergebnisse eines LEFT JOIN und eines RIGHT JOIN. Es werden alle Datensätze aus beiden Tabellen zurückgegeben, unabhängig von Übereinstimmungen.
FULL OUTER JOIN (Die vollständige Vereinigung)
Anforderung “Erstellen Sie einen Report, der sowohl Mitarbeiter ohne Projekte als auch Projekte ohne zugewiesene Mitarbeiter enthält.”
Benötigte Tabellen
Mitarbeiter:
| MA_ID | Vorname | Nachname |
|---|---|---|
| 1 | Julia | Wagner |
| 2 | Kevin | Huber |
| 3 | Sandra | Beck |
| 4 | Tom | Scholz |
Projekt:
| Projekt_ID | Projekt_Name | MA_ID |
|---|---|---|
| P100 | ”Neues CRM” | 1 |
| P200 | ”Webshop Relaunch” | 1 |
| P300 | ”Cloud-Migration” | 2 |
| P400 | ”Interne Schulung” | 5 |
SQL-Abfrage (Simulation für MySQL/MariaDB)
SELECT m.Vorname, m.Nachname, p.Projekt_Name
FROM Mitarbeiter AS m
LEFT JOIN Projekt AS p ON m.MA_ID = p.MA_ID
UNION
SELECT m.Vorname, m.Nachname, p.Projekt_Name
FROM Mitarbeiter AS m
RIGHT JOIN Projekt AS p ON m.MA_ID = p.MA_ID;Code-Erklärung im Detail
SELECT ... LEFT JOIN ...: liefert alle Mitarbeiter, auch ohne Projekte.UNION: verbindet die Ergebnismengen und entfernt dabei Duplikate.SELECT ... RIGHT JOIN ...: liefert alle Projekte, auch ohne Mitarbeiter.- Kombination: Das Ergebnis ist eine lückenlose Liste aller Mitarbeiter und aller Projekte.
Ergebnis:
| Vorname | Nachname | Projekt_Name |
|---|---|---|
| Julia | Wagner | ”Neues CRM” |
| Julia | Wagner | ”Webshop Relaunch” |
| Kevin | Huber | ”Cloud-Migration” |
| Sandra | Beck | NULL |
| Tom | Scholz | NULL |
| NULL | NULL | ”Interne Schulung” |
CROSS JOIN (Das kartesische Produkt)
Funktionsweise
Kombiniert jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle. Er benötigt keine ON-Bedingung und erzeugt alle denkbaren Kombinationen.
CROSS JOIN (Das kartesische Produkt)
Anforderung „Erstellen Sie eine Liste aller möglichen Kombinationen aus Mitarbeitern und Standorten, um potenzielle Einsatzorte zu planen.“
Benötigte Tabellen
Mitarbeiter:
| Nachname |
|---|
| Wagner |
| Huber |
| Beck |
| Scholz |
Standort:
| Stadt |
|---|
| Berlin |
| Hamburg |
SQL-Abfrage
SELECT m.Nachname, s.Stadt
FROM Mitarbeiter AS m
CROSS JOIN Standort AS s;Code-Erklärung im Detail
FROM Mitarbeiter AS m CROSS JOIN Standort AS s: kombiniert jeden der 4 Mitarbeiter mit jedem der 2 Standorte.- Ergebnis:
4 * 2 = 8Zeilen. - Achtung: Bei großen Tabellen wächst die Ergebnismenge extrem an. Ein versehentlicher CROSS JOIN (z. B. durch eine fehlende
ON-Klausel) ist eine häufige Fehlerquelle.
Ergebnis:
| Nachname | Stadt |
|---|---|
| Wagner | Berlin |
| Wagner | Hamburg |
| Huber | Berlin |
| Huber | Hamburg |
| Beck | Berlin |
| Beck | Hamburg |
| Scholz | Berlin |
| Scholz | Hamburg |
Zusammenfassung und Prüfungsfallen
Zusammenfassung der Anwendungsfälle
| JOIN-Typ | Anwendungsfall | Schlüsselwörter in der Anforderung |
|---|---|---|
| INNER JOIN | Nur vollständig zuordenbare Datensätze | „zeige X mit Y“, „nur Datensätze, die…“ |
| LEFT JOIN | Alle Datensätze einer primären Tabelle (links) | „alle X und deren Y“, „vollständige Liste von X“ |
| RIGHT JOIN | Alle Datensätze einer primären Tabelle (rechts) | „alle Y und deren X“ |
| FULL JOIN | Alle Datensätze aus beiden Tabellen | „lückenlose Zuordnung“, „zeige alle X und alle Y“ |
| CROSS JOIN | Erzeugen aller möglichen Kombinationen | „alle Kombinationen“, „jede mögliche Variante“ |
| SELF JOIN | Abfragen von Hierarchien in derselben Tabelle | „Vorgesetzter“, „untergeordnet“, „berichtet an“ |
Wichtige Prüfungsfallen
- WHERE-Bedingung bei OUTER JOINs: Eine WHERE-Klausel auf die rechte Tabelle eines LEFT JOIN (z. B.
WHERE p.Projekt_Name IS NOT NULL) wirkt wie ein Filter und macht den LEFT JOIN funktional zu einem INNER JOIN. - Verwendung von Aliasen: Bei SELF JOINs zwingend erforderlich. Bei allen anderen JOINs erhöhen sie die Lesbarkeit und sind Best Practice.
- NULL-Verhalten:
NULList niemals gleichNULL. Eine VerknüpfungON a.ID = b.IDfunktioniert nicht, wenn beide IDsNULLsind.
Zusammenfassung
Zusammenfassung:
SQL-JOIN-Konzepte
In diesem Abschnitt hast du die verschiedenen Arten von JOINs in SQL kennengelernt. Dabei wurden sowohl die Funktionsweise als auch typische Anwendungsfälle im Detail betrachtet.
- JOIN-Klausel und ON-Bedingung: Eine JOIN-Klausel verknüpft Tabellen miteinander. Die ON-Bedingung bestimmt, anhand welcher Spalten die Verbindung erfolgt. In der Regel wird ein Fremdschlüssel mit einem Primärschlüssel verglichen.
- Alias (AS): Ermöglicht es, Tabellen in einer Abfrage abzukürzen. Das ist nicht nur bei komplexeren Abfragen hilfreich, sondern bei SELF JOINs zwingend erforderlich, da dieselbe Tabelle mehrfach genutzt wird.
- Primärschlüssel und Fremdschlüssel: Ein Primärschlüssel identifiziert jeden Datensatz eindeutig. Ein Fremdschlüssel verweist auf den Primärschlüssel einer anderen Tabelle und stellt so die Beziehung her.
JOIN-Arten im Überblick
Jeder JOIN-Typ liefert unterschiedliche Ergebnismengen und wird je nach Fragestellung eingesetzt.
- INNER JOIN: Gibt nur Datensätze zurück, die in beiden Tabellen übereinstimmen. Beispiel: Liste von Mitarbeitern mit ihrer Abteilung, sofern eine Abteilung existiert.
- LEFT JOIN: Enthält alle Datensätze der linken Tabelle, ergänzt um Daten der rechten Tabelle. Falls keine Übereinstimmung vorliegt, wird NULL angezeigt. Beispiel: Liste aller Mitarbeiter, auch wenn sie keinem Projekt zugeordnet sind.
- RIGHT JOIN: Umgekehrt zum LEFT JOIN. Alle Datensätze der rechten Tabelle erscheinen, ergänzt um passende Daten der linken Tabelle. Beispiel: Liste aller Abteilungen, auch wenn keine Mitarbeiter zugeordnet sind.
- FULL OUTER JOIN: Vereinigt die Ergebnisse von LEFT und RIGHT JOIN. Alle Datensätze beider Tabellen erscheinen, fehlende Werte werden mit NULL ergänzt. Beispiel: Übersicht über Mitarbeiter und Projekte, inklusive Mitarbeiter ohne Projekt und Projekte ohne Mitarbeiter.
- CROSS JOIN: Bildet das vollständige kartesische Produkt zweier Tabellen. Jede Zeile der einen Tabelle wird mit jeder Zeile der anderen kombiniert. Beispiel: Kombination aller Mitarbeiter mit allen Standorten.
- SELF JOIN: Eine Tabelle wird mit sich selbst verknüpft, um hierarchische Strukturen abzubilden. Beispiel: Zuordnung von Mitarbeitern zu ihren Vorgesetzten.
Typische Anwendungsfälle und Prüfungsfallen
- Anwendungsfälle: Ein INNER JOIN wird bei „nur passende Datensätze“ eingesetzt, ein LEFT JOIN bei „alle Datensätze einer Haupttabelle“, ein RIGHT JOIN bei „alle Datensätze einer Referenztabelle“, ein FULL OUTER JOIN bei „vollständiger Abgleich beider Tabellen“, ein CROSS JOIN bei „alle Kombinationen“ und ein SELF JOIN bei „hierarchischen Abfragen“.
- Prüfungsfallen: Eine WHERE-Bedingung kann einen OUTER JOIN ungewollt zu einem INNER JOIN machen, wenn NULL-Werte herausgefiltert werden. Zudem darfst du nicht vergessen, Aliase konsequent einzusetzen, um die Lesbarkeit und Eindeutigkeit zu sichern.
Ausblick:
In der nächsten Lerneinheit beschäftigst du dich mit Triggern, Indizes und Stored Procedures – drei wichtigen Konzepten zur Optimierung und Automatisierung von Datenbanken. Du lernst, wie Trigger Aktionen bei bestimmten Ereignissen automatisch ausführen, wie Indizes Abfragen deutlich beschleunigen und wie Stored Procedures komplexe Abläufe effizient in der Datenbank selbst abbilden.