Tabellen verknüpfen mit JOIN

Lernziele

• Nach diesem Kapitel können Sie Tabellen über gemeinsame Spalten mit dem JOIN-Befehl verknüpfen.
• Sie können den Unterschied zwischen einem INNER JOIN und einem LEFT JOIN verstehen und anwenden.
• Sie können mehrere JOINs hintereinander schreiben, um drei Tabellen in einer Abfrage zu verknüpfen.

Im letzten Kapitel haben Sie gelernt, warum man Daten auf mehrere Tabellen aufteilt und wie Beziehungen zwischen Tabellen funktionieren. Jetzt wird es praktisch: Mit JOIN können Sie die normalisierten Tabellen wieder zusammenführen und Daten aus verschiedenen Tabellen in einer einzigen Abfrage kombinieren.

Erklärvideo

Ein überschaubares Beispiel: buildings und employees

Bevor wir uns an die grosse Spotify-Datenbank wagen, schauen wir uns die JOIN-Befehle an einem kleinen Beispiel an. Stellen Sie sich vor, wir hätten zwei Tabellen mit folgenden Daten, die wir anhand der Gebäudenamen zusammenführen möchten:

Die JOIN-Arten

Mit SQL können Sie dem Datenbanksystem die mühselige Arbeit überlassen, diese Daten zusammenzuführen. Dazu gibt es mehrere JOIN-Varianten. Ein Tipp: Wenn Sie sich die Gebäudenamen in einem Venn-Diagramm (Mengendiagramm) vorstellen, sind die Befehle einfacher verständlich:

Schauen Sie sich die vier Varianten an — die Farben entsprechen dem Venn-Diagramm (rot = aus buildings, grün = aus employees). In allen vier Befehlen sagen Sie mit ON, welche Spalten zusammengehören: links vom = die Spalte der einen Tabelle (building_name aus buildings), rechts die der anderen (building aus employees). Achten Sie darauf, welche Zeilen jeweils erscheinen und wo NULL steht:

SELECT *
FROM buildings
JOIN employees ON building_name = building;

JOIN und WHERE kombinieren

Das Ergebnis eines JOIN ist eine ganz normale Tabelle — Sie können sie also wie gewohnt mit WHERE filtern.

Im ersten Beispiel filtern wir nach einer Spalte aus employees: Wir holen nur die Engineers und zeigen zu jedem, wie viele Plätze sein Gebäude hat.

-- Alle Engineers mit der Kapazität ihres Gebäudes
SELECT name, building_name, capacity
FROM buildings
JOIN employees ON building_name = building
WHERE role = 'Engineer';

Im zweiten Beispiel filtern wir umgekehrt nach einer Spalte aus buildings: Gesucht sind alle Mitarbeiter in Gebäuden mit weniger als 24 Plätzen.

-- Wer arbeitet in einem Gebäude mit weniger als 24 Plätzen?
SELECT name, role, building_name, capacity
FROM buildings
JOIN employees ON building_name = building
WHERE capacity < 24;

Aufgabe: Manager finden

Zeigen Sie name, building_name und capacity aller Mitarbeiter mit der Rolle Manager.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe: Langjährige Mitarbeiter

Zeigen Sie name, years_employed, building_name und capacity aller Mitarbeiter, die seit mindestens 5 Jahren angestellt sind.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe: Gebäude mit den meisten Mitarbeitern

In welchem Gebäude arbeiten am meisten Mitarbeiter? Zeigen Sie building_name und die Anzahl — nur das Gebäude mit den meisten. Kombinieren Sie dazu den JOIN mit den Aggregatsfunktionen aus dem letzten Kapitel.

-- Schreiben Sie Ihre Abfrage hier

Drei Tabellen verknüpfen

Jetzt wird es spannend: Die Firma hat uns auch ihre Lohntabelle gegeben. Sie steckt in der erweiterten Datenbank buildings_employees_salaries.db — darin finden Sie buildings und employees wie bisher, plus eine dritte Tabelle salaries mit dem Monatslohn in Franken für jede Rolle:

salaries

Die Spalte role in employees verweist auf role_name in salaries — genau so, wie building auf building_name verweist. employees ist also mit beiden anderen Tabellen verbunden:

Angenommen, Sie möchten wissen, wer in welchem Gebäude arbeitet und wie viel diese Person verdient. Die Gebäude stehen in buildings, die Löhne in salaries — und nur employees kennt beides. Sie brauchen also zwei JOINs, und die schreiben Sie einfach untereinander. Schauen Sie sich an, was dabei Schritt für Schritt passiert (rot = buildings, grün = employees, blau = salaries):

SELECT name, role, building_name, capacity
FROM buildings
JOIN employees ON building_name = building;

Lesen Sie die Abfrage von oben nach unten: Jeder JOIN hängt eine weitere Tabelle an das bisherige Ergebnis an. So können Sie beliebig viele Tabellen verknüpfen — solange es jeweils eine passende Spalte für ON gibt.

Wo sind Jakob J. und Brandon J.?

Die beiden Artists aus Gebäude 3e fehlen im Ergebnis — obwohl es die Rolle Artist in salaries durchaus gibt. Warum?

Lösung

Sie fliegen schon beim ersten JOIN raus, weil es das Gebäude 3e in buildings nicht gibt. Was im Zwischenergebnis fehlt, kann der zweite JOIN nicht mehr zurückholen. Aus demselben Grund taucht auch die Rolle Intern nicht auf: Niemand hat diese Rolle.

Probieren Sie es selbst aus. Löschen Sie danach die letzte Zeile und führen Sie die Abfrage nochmals aus — welche Fehlermeldung erhalten Sie, und warum?

SELECT name, role, building_name, capacity, role_name, salary
FROM buildings
JOIN employees ON building_name = building
JOIN salaries ON role = role_name;

Aufgabe: Gut verdienende Mitarbeiter

Zeigen Sie name, building_name und salary aller Mitarbeiter, die mehr als 8000 Franken verdienen.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe: Lohnsumme pro Gebäude

Wie viel Lohn wird pro Gebäude jeden Monat ausbezahlt? Zeigen Sie building_name und die Lohnsumme, sortiert von der grössten zur kleinsten.

-- Schreiben Sie Ihre Abfrage hier

Zusammenfassung

Was Sie gelernt haben

• Mit JOIN verknüpfen Sie Tabellen über gemeinsame Spalten
• Ein JOIN (= INNER JOIN) zeigt nur Zeilen mit Übereinstimmung in beiden Tabellen
• Ein LEFT JOIN zeigt alle Zeilen der linken Tabelle, auch ohne Treffer rechts
• Ein RIGHT JOIN zeigt alle Zeilen der rechten Tabelle (wird selten verwendet)
• Ein FULL JOIN zeigt alle Zeilen beider Tabellen
• Das Ergebnis eines JOIN ist eine normale Tabelle — Sie können sie mit WHERE, GROUP BY und ORDER BY weiterverarbeiten
• Mehrere JOINs schreiben Sie untereinander: Jeder hängt eine weitere Tabelle an das bisherige Ergebnis an

Weiter geht es mit der Spotify-Datenbank: JOINs mit Spotify. Dort werden Sie das Muster von buildings → employees → salaries wiedererkennen.