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.

Die normalisierte Spotify-Datenbank

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

Was ist ein JOIN?

Ein JOIN verknüpft zwei Tabellen miteinander, indem er zusammengehörige Zeilen über eine gemeinsame Spalte verbindet. So können Sie Daten aus verschiedenen Tabellen in einer einzigen Abfrage kombinieren.

JOIN-Arten als Mengendiagramm

Die verschiedenen JOIN-Arten lassen sich gut mit Mengendiagrammen (Venn-Diagramme) darstellen:

JOIN = INNER JOIN

Ein einfaches JOIN ist dasselbe wie ein INNER JOIN. Die Kurzform JOIN wird heute bevorzugt. Analog ist LEFT JOIN dasselbe wie LEFT OUTER JOIN, und RIGHT JOIN dasselbe wie RIGHT OUTER JOIN. Sie werden in der Praxis fast immer die Kurzformen antreffen.

JOIN – Tabellen verknüpfen

Grundsyntax

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

Tabellen-Aliase

Um die Abfragen kürzer und lesbarer zu machen, können Sie Tabellen mit kurzen Aliasen versehen:

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.

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 – auch ohne Treffer anzeigen

Ein normaler JOIN (= INNER JOIN) zeigt nur Zeilen, die in beiden Tabellen eine Übereinstimmung haben. Ein LEFT JOIN zeigt dagegen alle Zeilen aus der linken Tabelle – auch wenn es in der rechten Tabelle keinen passenden Eintrag gibt. In diesem Fall stehen NULL-Werte in den Spalten der rechten Tabelle.

Grundsyntax

SELECT t1.spalte, t2.spalte
FROM tabelle1 t1
LEFT JOIN tabelle2 t2 ON t1.spalte = t2.spalte;

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 funktioniert genau umgekehrt: Er zeigt alle Zeilen aus der rechten Tabelle, auch wenn es links keinen Treffer gibt. In der Praxis kommt RIGHT JOIN 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

• Eine gute Datenarchitektur erfordert Entscheidungen: Was ist ein Duplikat? Welche Beziehungen sind n:m? Welche Version behalten wir?
• 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)
• 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