LU10a - SELECT über mehrere Tabellen

• Ich kann mit INNER JOIN … ON Daten aus zwei und drei Tabellen gemeinsam abfragen.
• Ich kann die ältere Schreibweise mit FROM und WHERE lesen und erklären, was ohne Verknüpfungsbedingung passiert.
• Ich kann Aliase für Tabellen und Spalten verwenden.
• Ich weiss, welche Bedingung in ON gehört und welche in WHERE.

Für diese Seite brauchen Sie die Datenbank travel_blog aus LU08 mit den Tabellen users, posts, categories und post_category. Das Skript legt sie mit allen Beispieldaten an.

1. Skript herunterladen: Travel-Blog-Datenbank (SQL-Skript, gezippt)
2. Die ZIP-Datei entpacken.
3. In WebStorm im Datenbank-Fenster mit der rechten Maustaste auf die Verbindung klicken (z.B. mysql@localhost).
4. SQL Scripts > Run SQL Script… wählen und die entpackte .sql-Datei auswählen.
5. Danach nochmals mit der rechten Maustaste auf die Verbindung klicken, Tools > Manage Shown Schemas… wählen und travel_blog ankreuzen.

Screencast: SQL-Skript in WebStorm laden.

In unserem Reiseblog sind die Daten auf vier Tabellen verteilt:

• users: die Autorinnen und Autoren
• posts: die Beiträge, mit author_id als Verweis auf users
• categories: die Kategorien
• post_category: welcher Post zu welcher Kategorie gehört

Bisher haben wir immer nur eine Tabelle abgefragt:

SELECT post_id, title, author_id
FROM posts
WHERE post_id = 1;

Für die Website reicht das nicht. Dort soll beim Post der Name der Autorin oder des Autors stehen, nicht die Nummer 2. Der Name steht aber in einer anderen Tabelle, in users in der Spalte display_name. Wir müssen also posts und users gemeinsam abfragen.

SELECT p.post_id, p.title, u.display_name AS author
FROM posts AS p
INNER JOIN users AS u ON p.author_id = u.user_id;

So ist die Abfrage aufgebaut:

Die Bedingung nach ON verbindet den Fremdschlüssel der Kindtabelle 🧒 posts mit dem Primärschlüssel der Elterntabelle 👩‍🦳👨🏼‍🦰 users. Das ist genau die Verknüpfung, die Sie in LU08 mit FOREIGN KEY angelegt haben.

Resultat:

Ein Alias ist ein Kurzname. Es gibt zwei Arten:

• Tabellenalias: FROM posts AS p bedeutet, dass posts im Statement p heisst. Das spart Schreibarbeit.
• Spaltenalias: u.display_name AS author gibt der Spalte im Resultat den Namen author.

Das Wort AS ist optional. FROM posts p funktioniert genauso.

Sie werden in älteren Beispielen und im Internet oft diese Variante sehen. Sie liefert dasselbe Resultat:

SELECT posts.post_id, posts.title, users.display_name
FROM posts, users
WHERE posts.author_id = users.user_id;

Hier stehen beide Tabellen nach FROM, und die Verknüpfung steht in WHERE. Wir verwenden in diesem Modul INNER JOIN, weil Verknüpfung und Filter so klar getrennt sind.

Video: Abfragen über eine n:m-Beziehung.

Posts und Kategorien hängen über die Zwischentabelle post_category zusammen. Die Abfrage geht deshalb in zwei Schritten: von posts zu post_category und von dort weiter zu categories.

SELECT p.title, c.name AS category
FROM posts p
INNER JOIN post_category pc ON p.post_id = pc.post_id
INNER JOIN categories c ON pc.category_id = c.category_id
ORDER BY p.post_id, c.name;

Resultat (Ausschnitt):

Wie im Video: Welche Kategorien hat der Post Utrecht (post_id 2)?

SELECT c.name AS category
FROM posts p
INNER JOIN post_category pc ON p.post_id = pc.post_id
INNER JOIN categories c ON pc.category_id = c.category_id
WHERE p.post_id = 2;

Bei INNER JOIN ist das für das Resultat egal. Ob Sie bei posts oder bei categories beginnen, es kommen dieselben Zeilen zurück. Für die Lesbarkeit hilft eine einfache Regel: Nach FROM steht die Tabelle, deren Zeilen Sie auflisten wollen. Von dort aus folgen Sie mit INNER JOIN den Fremdschlüsseln:

• 1:n: posts → users
• n:m: posts → post_category → categories

posts und post_category haben beide eine Spalte post_id. Schreiben Sie in SELECT nur post_id, weiss MySQL nicht, welche gemeint ist:

Die Lösung: Stellen Sie den Tabellennamen oder den Alias davor, also p.post_id. Am einfachsten ist es, das bei Abfragen über mehrere Tabellen immer zu tun.

INNER JOIN zeigt nur Zeilen, die in beiden Tabellen einen passenden Eintrag haben. Die Kategorie Deutschland ist keinem Post zugeordnet. Sie fehlt deshalb im Resultat der n:m-Abfrage, obwohl sie in categories steht.

Guido Koch