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

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;

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;

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;

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.

Syntax ist korrekt!

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