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:

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:

Beispiel: Songs mit Genres

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

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:

Beispiel: Alle Alben eines Songs

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

Beispiel: Songs, Künstler und Album zusammen

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

JOIN mit Aggregatsfunktionen

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

Welche Künstler haben die meisten Songs?

Songs mit den meisten Künstlern

In wie vielen Genres ist ein Künstler vertreten?

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:

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

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.

Aufgabe 2: Alle Künstler eines Songs

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

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.

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.

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.

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.

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.

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