Zum Zusammenführen von Daten aus mehreren über Fremdschlüssel verbundene Tabellen verwenden wir bevorzugt den JOIN-Befehl.

Tabelle der Relation «movies»

id	title	year
1	Argo	2012
2	12 Years a Slave	2013
3	Birdman	2014
4	Spotlight	2015
5	Moonlight	2016
6	The Shape of Water	2017

Tabelle der Relation «quotes»

id	movie_id	quotetext
1	5	Little: My name is Chiron, people call me little.
2	5	Black: It is what it is.
3	3	Mike Shiner: Popularity is the slutty little cousin of prestige.

Möchte man nun die beiden Tabellen mit SQL verknüpfen, kann man dies mit einem JOIN der beiden Tabellen erreichen:

SELECT *
    FROM movies JOIN quotes
    ON movies.id = quotes.movie_id;

Ergebnis der SQL-Abfrage

movies.id	movies.title	movies.year	quotes.id	quotes.movie_id	quotes.quotetext
5	Moonlight	2016	1	5	Little: My name is Chiron, people call me little.
5	Moonlight	2016	2	5	Black: It is what it is.
3	Birdman	2014	3	3	Mike Shiner: Popularity is the slutty little cousin of prestige.

JOIN erstellt eigentlich eine temporäre Tabelle (eine sogenannte Kreuztabelle) aus der wir dann mit SELECT Datensätze und Attribute auswählen können:

Z.B. Film-Id, -Titel und Zitat aller Filme aus den Jahren 2014 und 2015:

SELECT movies.id, title, quotetext
    FROM movies JOIN quotes
    ON movies.id = quotes.movie_id
    WHERE year = 2014 OR year = 2015;

Ergebnis der SQL-Abfrage

movies.id	movies.title	quotes.quotetext
3	Birdman	Mike Shiner: Popularity is the slutty little cousin of prestige.

Wie man sieht fehlt der Film «Spotlight» im Ergebnis. Dies weil es sich beim «normalen» JOIN um einen sogenannten «Inneren Join» handelt.

Inner Join: A JOIN B

Dieser verknüpft die Datensätze der beiden Tabellen. Wenn aber ein Datensatz aus einer Tabelle in der anderen keine Verknüpfung hat, dann wird dieser Datensatz nicht angezeigt. Wenn wir also alle Filme der Jahre 2014 und 2015 haben möchten – inkl. Zitat, falls sie eines haben, sonst halt ohne – dann können wir dies mit einem sogenannten «Linken Join» erreichen:

Left Join: A LEFT JOIN B

SELECT movies.id, title, quotetext
    FROM movies LEFT JOIN quotes
    ON movies.id = quotes.movie_id
    WHERE year = 2014 OR year = 2015;

Ergebnis der SQL-Abfrage

movies.id	movies.title	quotes.quotetext
3	Birdman	Mike Shiner: Popularity is the slutty little cousin of prestige.
4	Spotlight

Neben LEFT JOIN existiert natürlich auch ein RIGHT JOIN, welcher alle Datensätze der rechten Tabelle anzeigt, egal ob sie eine Verknüpfung zu einem Datensatz der linken Tabelle haben oder nicht.

Right Join: A RIGHT JOIN B

Aufgabe: SQL-Joins

Führe die folgenden SQL-Select-Abfragen auf unsere bereits bekannte Film-DB aus und überlege dir wie die Resultate zustande kommen. Übersetze die Anfrage dem Sinn nach in Umgangssprache und notiere dein Ergebnis.

Die SQL-Abfragen kannst du hier ausführen:

SQL-Run

SELECT movies.id, title, rank
    FROM movies JOIN ratings
    ON movies.id = ratings.movie_id;

SELECT *
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
        JOIN actors ON actors.id = movies2actors.actor_id;

SELECT title, COUNT(movies2actors.actor_id) as count_actors
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
    GROUP BY movies.id;

SELECT name, COUNT(movies.id) as count_movies
    FROM actors
        JOIN movies2actors ON actors.id = movies2actors.actor_id
        JOIN movies ON movies.id = movies2actors.movie_id
    GROUP BY actors.id
    HAVING COUNT(movies.id) > 1;

SELECT actors.name, AVG(rank)
    FROM movies
        JOIN movies2actors ON movies.id = movies2actors.movie_id
        JOIN actors ON actors.id = movies2actors.actor_id
        JOIN ratings ON movies.id = ratings.movie_id
    GROUP BY actors.id
    ORDER BY AVG(rank);