Anfragen über mehrere Tabellen - Verbund

Eine Anfrage über mehrere SQL-Tabellen nennt man Verbund oder Join. Ausführlich wird das Thema in der Sekundarstufe II behandelt. Hier soll nur ein kleiner Einblick gegeben. Zur Einführung nehme ich diesmal nicht Instahub, weil das zu unübersichtlich werden würde.

Wir betrachten eine kleine Datenbank Geo, die aus den folgenden beiden Tabellen bestehen soll:

Tabelle Stadt

Name	Einwohnerzahl	Fläche	Staat
Berlin	3,769 Mio	891,8 km²	Deutschland
Hamburg	1,845 Mio	755,2 km²	Deutschland
London	8,982 Mio	1572 km²	Großbritannien
Paris	2,176 Mio	105,34 km²	Frankreich

Tabelle Staaten

Staat	Sprache
Deutschland	Deutsch
Großbritanien	Englisch
Frankreich	Französisch

Das Attribut Staat, welches in der Tabelle Staaten Primärschlüssel ist, ist in der Tabelle Stadt Fremdschlüssel, es verbindet also die Tabellen. (Die zweite Tabelle ist bewusst klein gehalten, dass der Umfang der Seite gesprengt wird.)

Wenn man die folgende Abfrage durchführt

SELECT * FROM Stadt, Staaten;

erhält man die folgende Tabelle

Name	Einwohnerzahl	Fläche	Staat	Staat	Sprache
Berlin	3,769 Mio	891,8 km²	Deutschland	Deutschland	Deutsch
Hamburg	1,845 Mio	755,2 km²	Deutschland	Deutschland	Deutsch
London	8,982 Mio	1572 km²	Großbritannien	Deutschland	Deutsch
Paris	2,176 Mio	105,34 km²	Frankreich	Großbritanien	Englisch
Berlin	3,769 Mio	891,8 km²	Deutschland	Großbritanien	Englisch
Hamburg	1,845 Mio	755,2 km²	Deutschland	Großbritanien	Englisch
London	8,982 Mio	1572 km²	Großbritannien	Großbritanien	Englisch
Paris	2,176 Mio	105,34 km²	Frankreich	Frankreich	Französisch
Berlin	3,769 Mio	891,8 km²	Deutschland	Frankreich	Französisch
Hamburg	1,845 Mio	755,2 km²	Deutschland	Frankreich	Französisch
London	8,982 Mio	1572 km²	Großbritannien	Frankreich	Französisch
Paris	2,176 Mio	105,34 km²	Frankreich	Deutschland	Deutsch

Es wird also jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle kombiniert. Uns interessieren aber nur die Zeilen, in der die Eigenschaft Staat der einen Tabelle mit der Eigenschaft Staat der zweiten Tabelle übereinstimmt. Da die Spalte Staat in beiden Tabellen auftritt, muss im SQL-Befehl der Spaltenname mit dem Tabellenname verknüpft werden:

SELECT * FROM Stadt, Staaten WHERE Stadt.Staat = Staaten.Staat;

Die Abfrage ergibt:

Name	Einwohnerzahl	Fläche	Staat	Staat	Sprache
Berlin	3,769 Mio	891,8 km²	Deutschland	Deutschland	Deutsch
Hamburg	1,845 Mio	755,2 km²	Deutschland	Deutschland	Deutsch
London	8,982 Mio	1572 km²	Großbritannien	Großbritanien	Englisch
Paris	2,176 Mio	105,34 km²	Frankreich	Frankreich	Französisch

Eine solche Abfrage nennt man Verbund oder Join. Man kann sie auch wie folgt formulieren:

SELECT * FROM Stadt JOIN Staaten ON Stadt.Staat = Staaten.Staat;

Auf das Ergebnis des Verbunds lassen sich dann wiederum Projektion und Selektion anwenden.

Hier ist Geo als LibreOffice-Datenbank, mit der du das Ergebnis testen kannst:

Datenbank Geo

Übertragen wir nun das Ganze auf die Instahub-Datenbank. Dazu betrachten wir die Tabellen users und photos:

users (id, username, email, email_verified_at, password, name, bio, gender, birthday, city, country, centimeters, avatar, role, is_active, remember_token, created_at, updated_at)

photos( id, user_id, description, url, created_at, updated_at)

In der Tabelle photos ist user_id Fremdschlüssel und bezieht sich auf den Primärschlüssel (id) in der Tablle users.

Nun sollen einige Abfragen über die beiden Tabellen durchgeführt werden:

SELECT * FROM users, photos;

Bei dieser Abfrage würde jede Zeile der Tabelle users mit jeder Zeile der Tabelle photos kombiniert werden. Das wären in der Ausgabe, bei Füllen der Tabellen mit Standarddaten, 207×1524=315468 Zeilen. In Instahub kommt es dabei zu einer Fehlermeldung, aber mit einer entsprechenden Einstellung wäre die Ausgabe trotzdem möglich.

Uns interessieren aber die Fotos, die jeder Nutzer veröffentlicht hat. Dazu kann man den folgenden Befehl verwenden:

SELECT * FROM users, photos WHERE users.id = photos.user_id

mit der Zeile WHERE users.id = photos.user_id werden hier jeden Nutzer seine Fotos zugeordnet.

Auch möglich wäre:

SELECT * FROM users JOIN photos ON users.id = photos.user_id

Wenn man die Abfrage noch mehr verfeinern will, z.B. alle Fotos des Nutzers mit der id 1, kann man weitere Bedingungen hinzufügen:

SELECT * FROM users, photos WHERE users.id = photos.user_id AND users.id = 1

Aufgabe

Lass von deinem Lehrer alle Tabellen in Instahub eintragen und teste die Abfragen.