JOINs mit Spotify

Lernziele

• Sie erkennen in einem Datenbankschema, über welche Tabellen Sie joinen müssen, um zwei Tabellen zu verbinden.
• Sie können gleichnamige Spalten mit tabelle.spalte und Aliasen eindeutig ansprechen.
• Sie können Daten aus n:m-Beziehungen über Verknüpfungstabellen auslesen und mit WHERE, GROUP BY, HAVING und LEFT JOIN kombinieren.

Vom Bürogebäude zu Spotify

Auf der letzten Seite haben Sie drei Tabellen hintereinander verknüpft:

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

Die Tabelle in der Mitte, employees, war dabei der Schlüssel: Nur sie kennt sowohl das Gebäude als auch die Rolle — und über die Rolle den Lohn. In einem Gebäude arbeiten verschiedene Rollen, und jede Rolle kommt in mehreren Gebäuden vor — buildings und salaries stehen also in einer n:m-Beziehung, und employees verbindet die beiden.

Genau dieses Muster kennen Sie aus dem Kapitel Datenbank-Design: die Verknüpfungstabelle. Ab jetzt arbeiten wir wieder mit der normalisierten Datenbank spotify.db statt der flachen spotify_flat.db — und dort gibt es gleich drei solche Verknüpfungstabellen:

Einen Unterschied gibt es: employees hat eigenen Inhalt (Namen, Dienstjahre). track_artists dagegen ist eine reine Verknüpfungstabelle — sie enthält praktisch nur IDs, die auf einen Song und einen Künstler zeigen. Wenn Sie also einen Song mit seinem Künstlernamen sehen möchten, brauchen Sie immer zwei JOINs — genau wie von buildings zu salaries.

Spaltennamen und Aliase

Bei buildings, employees und salaries konnten wir einfach building_name oder role schreiben, weil jeder Spaltenname nur in einer Tabelle 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.

Beispiele

Songs mit Künstlernamen

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

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

Erst von tracks zu track_artists, dann von track_artists zu artists — genau wie von buildings über employees zu salaries.

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;

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;

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;

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

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 kombinieren — so wie bei der Lohnsumme pro Gebäude.

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

• Verknüpfungstabellen lösen Viele-zu-Viele-Beziehungen (Songs ↔ Künstler, Songs ↔ Alben, Songs ↔ Genres) — wer von einer Seite zur anderen will, braucht zwei JOINs
• Gleichnamige Spalten sprechen Sie mit tabelle.spalte an, kurze Aliase (FROM tracks t) halten die Abfragen lesbar
• JOINs lassen sich mit WHERE, GROUP BY, HAVING und ORDER BY kombinieren
• Mit LEFT JOIN und WHERE ... IS NULL finden Sie verwaiste Einträge
• Eine normalisierte Datenbank vermeidet Duplikate und Inkonsistenzen der flachen Tabelle