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 komplexe Abfragen über Verknüpfungstabellen aufbauen, um Daten aus n:m-Beziehungen auszulesen.

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 zur Spotify-Datenbank zurückkehren, 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. Zwei Beispiele:

-- 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';

-- 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 Engineers

In welchem Gebäude arbeiten am meisten Engineers? 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

Zurück zu Spotify

Ab jetzt arbeiten wir wieder mit der normalisierten Datenbank spotify.db statt der flachen spotify_flat.db. Zur Erinnerung das Schema:

Grundsyntax, Spaltennamen und Aliase

Bei buildings und employees konnten wir einfach building_name und building schreiben, weil jeder Spaltenname nur in einer der beiden Tabellen vorkommt. In der Spotify-Datenbank ist das anders: track_id gibt es in tracks und in track_artists, eine Spalte name sogar in artists, albums und genres. Damit klar ist, welche Spalte gemeint ist, schreibt man tabelle.spalte:

SELECT tabelle1.spalte, tabelle2.spalte
FROM tabelle1
JOIN tabelle2 ON tabelle1.gemeinsame_spalte = tabelle2.gemeinsame_spalte;

Weil ausgeschriebene Tabellennamen die Abfragen schnell lang machen, gibt man den Tabellen meist einen kurzen Alias: Direkt hinter dem Tabellennamen steht die Abkürzung — FROM tracks t bedeutet «die Tabelle tracks, im Folgenden kurz t»:

SELECT t.track_name, a.name
FROM tracks t
JOIN track_artists ta ON t.track_id = ta.track_id
JOIN artists a ON ta.artist_id = a.artist_id;

Hier steht t für tracks, ta für track_artists und a für artists. Alle folgenden Beispiele verwenden diese Schreibweise.

Beispiel: Songs mit Künstlernamen

Um Songs mit ihren Künstlern zu sehen, müssen wir über die Verknüpfungstabelle track_artists gehen:

-- Songs mit ihren Künstlern
SELECT t.track_name, a.name AS artist
FROM tracks t
JOIN track_artists ta ON t.track_id = ta.track_id
JOIN artists a ON ta.artist_id = a.artist_id
LIMIT 10;

Zwei JOINs nötig

Weil Songs und Künstler über die Verknüpfungstabelle track_artists verbunden sind, brauchen wir zwei JOINs: erst von tracks zu track_artists, dann von track_artists zu artists.

Beispiel: Songs mit Albumnamen

Auch die Album-Zuordnung läuft über eine Verknüpfungstabelle – ein Song kann auf mehreren Alben erscheinen:

-- Songs mit ihrem Albumnamen anzeigen
SELECT t.track_name, al.name AS album
FROM tracks t
JOIN track_albums tab ON t.track_id = tab.track_id
JOIN albums al ON tab.album_id = al.album_id
LIMIT 10;

Beispiel: Songs mit Genres

Dasselbe Prinzip gilt für Genres – auch hier brauchen wir eine Verknüpfungstabelle:

-- Songs mit ihren Genres
SELECT t.track_name, g.name AS genre
FROM tracks t
JOIN track_genres tg ON t.track_id = tg.track_id
JOIN genres g ON tg.genre_id = g.genre_id
LIMIT 10;

Beispiel: Kollaborationen sichtbar machen

Erinnern Sie sich an das Problem der flachen Tabelle, wo Ingrid Michaelson;ZAYN in einem einzigen Feld stand? In der normalisierten Datenbank sind beide Künstler einzeln gespeichert:

-- Alle Künstler des Songs «To Begin Again»
SELECT t.track_name, a.name AS artist, ta.position
FROM tracks t
JOIN track_artists ta ON t.track_id = ta.track_id
JOIN artists a ON ta.artist_id = a.artist_id
WHERE t.track_name = 'To Begin Again'
ORDER BY ta.position;

Beispiel: Alle Alben eines Songs

Da ein Song jetzt auf mehreren Alben erscheinen kann, können wir das mit einem JOIN sichtbar machen:

-- Auf welchen Alben erscheint «Blinding Lights» von The Weeknd?
SELECT t.track_name, al.name AS album
FROM tracks t
JOIN track_albums tab ON t.track_id = tab.track_id
JOIN albums al ON tab.album_id = al.album_id
JOIN track_artists ta ON t.track_id = ta.track_id AND ta.position = 1
JOIN artists a ON ta.artist_id = a.artist_id
WHERE t.track_name = 'Blinding Lights' AND a.name = 'The Weeknd';

Beispiel: Songs, Künstler und Album zusammen

Sie können auch mehrere Tabellen in einer Abfrage verknüpfen:

-- Vollständige Ansicht: Song, Hauptkünstler, Album
SELECT
    t.track_name,
    a.name AS artist,
    al.name AS album,
    t.popularity
FROM tracks t
JOIN track_artists ta ON t.track_id = ta.track_id AND ta.position = 1
JOIN artists a ON ta.artist_id = a.artist_id
JOIN track_albums tab ON t.track_id = tab.track_id
JOIN albums al ON tab.album_id = al.album_id
WHERE t.popularity > 90
ORDER BY t.popularity DESC
LIMIT 15;

JOIN mit Aggregatsfunktionen

JOINs lassen sich hervorragend mit den Aggregatsfunktionen aus dem letzten Kapitel kombinieren.

Welche Künstler haben die meisten Songs?

-- Top 10 Künstler mit den meisten Songs
SELECT a.name AS artist, COUNT(DISTINCT t.track_id) AS anzahl_songs
FROM artists a
JOIN track_artists ta ON a.artist_id = ta.artist_id
JOIN tracks t ON ta.track_id = t.track_id
GROUP BY a.artist_id
ORDER BY anzahl_songs DESC
LIMIT 10;

Songs mit den meisten Künstlern

-- Songs mit den meisten beteiligten Künstlern
SELECT t.track_name, COUNT(ta.artist_id) AS anzahl_kuenstler
FROM tracks t
JOIN track_artists ta ON t.track_id = ta.track_id
GROUP BY t.track_id
ORDER BY anzahl_kuenstler DESC
LIMIT 10;

In wie vielen Genres ist ein Künstler vertreten?

-- Künstler, die in den meisten Genres vertreten sind
SELECT a.name AS artist, COUNT(DISTINCT g.genre_id) AS anzahl_genres
FROM artists a
JOIN track_artists ta ON a.artist_id = ta.artist_id
JOIN track_genres tg ON ta.track_id = tg.track_id
JOIN genres g ON tg.genre_id = g.genre_id
GROUP BY a.artist_id
ORDER BY anzahl_genres DESC
LIMIT 10;

LEFT JOIN in der Praxis

Sie kennen den LEFT JOIN bereits vom buildings-Beispiel: alle Zeilen der linken Tabelle, auch ohne Treffer rechts. Genau das macht ihn zum Werkzeug, um «verwaiste» Einträge zu finden.

Beispiel: Gibt es Alben ohne Songs?

Mit einem LEFT JOIN können wir prüfen, ob es Alben in der Datenbank gibt, zu denen kein Song gespeichert ist:

-- Alben ohne Songs finden
SELECT al.name AS album, tab.track_id
FROM albums al
LEFT JOIN track_albums tab ON al.album_id = tab.album_id
WHERE tab.track_id IS NULL
LIMIT 10;

NULL-Werte als Indikator

Wenn tab.track_id IS NULL ist, bedeutet das: Dieses Album existiert in der albums-Tabelle, aber es gibt keinen Song in track_albums, der darauf verweist. Mit WHERE ... IS NULL nach einem LEFT JOIN findet man also «verwaiste» Einträge.

Unterschied zwischen JOIN und LEFT JOIN

-- Mit JOIN: Nur Alben, die mindestens einen Song haben
SELECT COUNT(DISTINCT al.album_id) AS alben_mit_songs
FROM albums al
JOIN track_albums tab ON al.album_id = tab.album_id;

-- Mit LEFT JOIN: Alle Alben, auch ohne Songs
SELECT COUNT(DISTINCT al.album_id) AS alle_alben
FROM albums al
LEFT JOIN track_albums tab ON al.album_id = tab.album_id;

RIGHT JOIN

Ein RIGHT JOIN kommt in der Praxis selten vor, weil man die Abfrage meist einfach umdrehen und einen LEFT JOIN verwenden kann.

Aufgaben

Aufgabe 1: Songs und Alben

Zeigen Sie die Spalten track_name und den Albumnamen für die 10 populärsten Songs. Sortieren Sie nach Popularität absteigend.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 2: Alle Künstler eines Songs

Zeigen Sie alle Künstler des Songs «Blinding Lights», inklusive ihrer Position. Sortieren Sie nach Position.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 3: Genres eines Songs

Finden Sie alle Genres, die dem Song «Tippa My Tongue» von den Red Hot Chili Peppers zugeordnet sind.

Hinweis

Um den Hauptkünstler einzuschränken, joinen Sie auch track_artists und artists und filtern mit ta.position = 1.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 4: Alben der Beatles

Finden Sie alle Alben von «The Beatles» und zählen Sie, wie viele Songs pro Album in der Datenbank sind. Sortieren Sie nach Anzahl Songs absteigend.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 5: Durchschnittliche Tanzbarkeit pro Künstler

Finden Sie die 10 Künstler mit der höchsten durchschnittlichen Tanzbarkeit, aber nur solche, die mindestens 20 Songs in der Datenbank haben. Zeigen Sie Künstlername, durchschnittliche Tanzbarkeit (gerundet auf 3 Stellen) und Anzahl Songs.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 6: Kollaborationen finden

Finden Sie alle Songs, an denen mehr als 5 Künstler beteiligt sind. Zeigen Sie den Songtitel und die Anzahl der Künstler, sortiert nach Anzahl absteigend. Begrenzen Sie auf 15 Ergebnisse.

-- Schreiben Sie Ihre Abfrage hier

Aufgabe 7: Komplett-Abfrage

Erstellen Sie eine vollständige Übersicht: Zeigen Sie für die 10 populärsten Songs den Titel, den Hauptkünstler (Position 1), ein Album, das Genre, die Popularität und die Tanzbarkeit. Sortieren Sie nach Popularität absteigend.

Hinweis

Da ein Song mehrere Alben und Genres haben kann, erscheint er möglicherweise mehrfach im Ergebnis. Um nur eine Zeile pro Song zu bekommen, können Sie mit GROUP BY t.track_id gruppieren und MIN(al.name) bzw. MIN(g.name) verwenden.

-- 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
• Verknüpfungstabellen lösen Viele-zu-Viele-Beziehungen (Songs ↔ Künstler, Songs ↔ Alben, Songs ↔ Genres)
• Sie können mehrere JOINs in einer Abfrage kombinieren
• JOINs lassen sich mit WHERE, GROUP BY, HAVING und ORDER BY kombinieren
• Eine normalisierte Datenbank vermeidet Duplikate und Inkonsistenzen der flachen Tabelle