Anfragen über mehrere Tabellen, Joins

Verknüpfung von Tabellen - Join

Relationale Datenbanken bestehen in der Regel aus sehr vielen Tabellen. Oft werden bei einer SQL-Anfrage Daten gewünscht, die aus verschiedenen Tabellen stammen. Dazu müssen die Tabellen verknüpft werden. Solche Verknüpfungen nennt man Joins.

Gewünscht ist eine Liste mit den Klassenleitern und der jeweiligen Klasse.
Die Tabelle Lehrkraft liefert zwar den Namen der in Frage kommenden Personen, aber keine Information über die Klassenleitung. In der Tabelle Klasse findet man zwar die Personalnummer, nicht aber den Namen des jeweiligen Klassenleiters.

Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten Tabellen in der FROM - Klausel. Häufig haben Attribute, die aus verschiedenen Tabellen stammen, identische Namen. Wird ein solches Attribut in der SQL-Anfrage verwendet, muss zusätzlich noch der Tabellenname angegeben werden. Man schreibt: Tabellenname.Attributname.

Der Attributname Name ist zweideutig. Er beschreibt einerseits die Lehrernamen, andererseits die Klasse. In unserem Bespiel muss der Name der Klasse mit Klasse.Name und der Name der Lehrkraft mit Lehrkraft.Name angesprochen werden.

Bemerkung Ist der Spaltenname eindeutig, kann auf das Voranstellen des Tabellennamen verzichtet werden.

Folgende Query zeigt das Prinzip. Die Bedeutung der WHERE - Klausel wird nachfolgend genauer erklärt.

Gewünscht ist eine Liste der Klassenleiter mit den jeweiligen Klassen.

SQL - Anfrage Ergebnis - Tabelle

SELECT Klasse.Name, Lehrkraft.Name
FROM Lehrkraft, Klasse
WHERE PersNr = Klassenleitung ;

Übungsdatenbank

Innerhalb der FROM - Klausel ist eine Umbenennung der Tabellennamen möglich. Dazu stellt SQL das Schlüsselwort AS zur Verfügung, das aber nicht unbedingt verwendet werden muss. Damit ist beispielsweise folgende Formulierung der obigen Anfrage möglich:

SELECT l.Name, k.Name
FROM Lehrkraft AS l, Klasse AS k
WHERE l.PersNr = k.Klassenleitung ;

bzw.

SELECT l.Name, k.Name
FROM Lehrkraft l, Klasse k
WHERE l.PersNr = k.Klassenleitung ;

Da PersNr und Klassenleitung eindeutige Attributnamen sind, kann die WHERE -Klausel auch lauten:

WHERE PersNr = Klassenleitung

SELECT p.Name, d.RaumNr
FROM Professor AS p, Dienstzimmer AS d
WHERE p.PersNr = d.PersNr ;

bzw.

SELECT p.Name, d.Raumnr
FROM professor p, Dienstzimmer d
WHERE p.PersNr = d.PersNr ;

Da name und RaumNr eindeutige Attributnamen sind, kann die SELECT -Klausel auch lauten:

SELECT Name, RaumNr

Bemerkung

ORACLE lässt die Verwendung von AS in der FROM - Klausel nicht zu! Der Aliasname wird einfach hinter den Tabellennamen geschrieben.

MS ACCESS bzw. die Übungsdatenbank akzeptiert beide Möglichkeiten.

Bedeutung der WHERE-Klausel

Das Weglassen der WHERE - Klausel in obigem Beispiel führt zu nachfolgendem Ergebnis:

Die Entstehung dieses Ergebnisses kann man sich folgendermaßen vorstellen. Bei einem Join wird jeder Datensatz der einen Tabelle mit jedem Datensatz der anderen Tabelle verknüpft. Das Ergebnis wird in einer virtuellen Tabelle "abgelegt", die sowohl die Spalten der einen wie auch der anderen Tabellen enthält.

Aus dieser virtuellen Tabelle werden dann die Spalten l.Name und k.Name ausgewählt und angezeigt. Bis auf die erste und zwölfte Datenzeile der obigen Tabellen sind aber alle Ausgabezeilen unsinnig, da bei diesen die Werte der Personalnummer und der Klassenleitung nicht übereinstimmen. Im obigen Beispiel muss deshalb gelten:
l.PersNr = k.Klassenleitung. Diese Bedingung wird in der WHERE - Klausel angegeben.

Mit Hilfe der WHERE - Klausel können Gleichheitsbedingungen angegeben werden, die unsinnige bzw. nicht aussagekräfte Kombinationen ausschließen.

Self-Join

Manchmal wird eine Tabelle mit sich selbst verknüpft. Dieser spezielle Join heißt Self-Join.

Welche Lehrkräfte (Angabe der PersNr) haben die Lehrbefähigung für mehr als ein Fach?

SQL - Anfrage Ergebnis - Tabelle

SELECT DISTINCT l1.Lehrkraft
FROM hat_Lehrbefaehigung_in l1, hat_Lehrbefaehigung_in l2
WHERE l1.Lehrkraft = l2.Lehrkraft AND NOT (l1.Fach = l2.Fach) ;

Übungsdatenbank

In diesem Beispiel wird ein Selfjoin verwendet. Zur Auswertung der Anfrage wird eine (virtuelle) Tabelle mit den vier Spalten l1.Lehrkraft, l1.Fach, l2.Lehrkraft und l2.Fach berechnet, aus der die Datensätze ausgewählt werden, bei denen die Lehrkraft- Attribute übereinstimmen und die Fach- Attribute verschieden sind. Anschließend wird von diesen Datensätzen die erste Spalte unter Entfernung von Duplikaten ausgegeben.

Welche Fächer dürfen die einzelnen Lehrkräfte unterrichten? Gewünscht ist die Ausgabe der Namen von Lehrkraft und Fach.

Lösungsvorschlag

SELECT Name, Fach
FROM Lehrkraft, hat_Lehrbefaehigung_in
WHERE PersNr = Lehrkraft ;

Übungsdatenbank

Ausblenden

Welche Lehrerin bzw. welcher Lehrer (Angabe des Namens) ist Fachbetreuer in Deutsch?

Lösungsvorschlag

SELECT Name
FROM Lehrkraft, hat_Fachbetreuung_in
WHERE PersNr = Lehrkraft AND Fach= 'Deutsch' ;

Übungsdatenbank

Ausblenden

Gesucht sind die Namen der Lehrkräfte, die die Schülerin Falbala unterrichten?

Lösungsvorschlag

SELECT l.Name
FROM Lehrkraft l, Schueler s, ist_Fachlehrkraft_von i
WHERE i.Klasse = s.gehoert_zu AND i.Lehrkraft = l.PersNr AND s.Name = 'Falbala' ;

Übungsdatenbank

Ausblenden

Gibt es Lehrkräfte (Angabe der PersNr), die mehr als ein Fach in derselben Klasse unterrichten?

Lösungsvorschlag

SELECT a.Lehrkraft
FROM ist_Fachlehrkraft_von a, ist_Fachlehrkraft_von b
WHERE a.Lehrkraft = b.Lehrkraft AND a.Klasse = b.Klasse AND NOT (a.Fach = b.Fach) ;

Übungsdatenbank

Hier handelt es sich um einen Self-Join, da die Tabelle ist_Fachlehrkraft_von mit sich selbst gejoint wird.

Ausblenden

zu Abschnitt 13.

Aufgabe 21

Probier die SQL-Anfrage SELECT "artikelname","kategoriename" FROM "artikel","kategorien"; aus. Hast du so eine Ausgabe erwartet? Welches System steckt hinter dem Ergebnis? Schau dazu z.B. nach, welche Kategorien es gibt.

Aufgabe 22

Gib von allen Artikeln Artikelname und Kategoriename aus.
Achtung: Wegen des Bindestrichs sind Anführungszeichen um kategorie-nr nötig: "kategorie-nr" !

Aufgabe 23

Welche Milchprodukte werden angeboten? (Ausgabe der Artikelnamen als 'Milchprodukte')

Aufgabe 24

Gibt es Länder, in denen mehrere Kunden in derselben Stadt wohnen?

Aufgabe 25

Wer vom Personal (Angabe von Vor- und Nachname) betreut Bestellungen der Kundenfirma 'Alfreds Futterkiste'?

Aufgabe 26

Welche Artikel (Angabe des Artikelnamens) hat die Firma 'Alfreds Futterkiste' bestellt? (Nimm den Firmennamen als Spaltentitel der Ausgabe.)