Lektion 3: Mit dem Join Tabellen verknüpfen |
Der Join - was ist das? |
Das ER-Diagramm einer Datenbank enthält normalerweise mehrere Entitätstypen und Beziehungen
zwischen diesen. Bei der Umsetzung in das Relationenmodell entstehen daraus mehrere Relationen (Tabellen).
Betrachten wir die 1:n-Beziehung zwischen Fahrer und Fahrt im ER-Diagramm eines Busunternehmens. Sie wird durch Aufnahme des Primärschlüssels PersonalNr der Relation Fahrer in die Relation Fahrt als Fremdschlüssel ↑PersonalNr in das Relationenmodell abgebildet: • Fahrer(PersonalNr, Name, Vorname, StraßeNr, PLZ , Ort, Telefon) • Fahrt(FahrtNr, ↑PersonalNr, Kennzeichen, Datum, Preis, Dauer, Reisestart, Reiseziel) Mit dem Join kann man nun Daten, die während des Entwurfsprozesses auf mehrere Relationen verteilt wurden, wieder zusammenführen. Man nutzt dazu aus, dass PersonalNr ein Schlüssel ist und dieser sowohl in der Tabelle Fahrer als auch in der Tabelle Fahrt vorkommt. |
Aha! Und wie geht das jetzt? |
Mit einem Join kann man zwei Tabellen zu einer Tabelle zusammenführen. Dabei wird immer
ein Datensatz aus der ersten Tabelle mit einem Datensatz aus der zweiten Tabelle zu einem neuen Datensatz
zusammengesetzt. Das macht man natürlich nur für die Datensätze, die auch zusammen gehören,
bei denen also der Wert des Primärschlüssels mit dem Wert des Fremdschlüssels übereinstimmt.
Die Gleichheit der beiden Werte wird durch eine entsprechende Bedingung in der where-Klausel ausgedrückt. In der from-Klausel muss man beide Tabellen angeben. Alternativ verwendet man die inner join-Syntax. Dabei gibt man in der from-Klausel die eine Tabelle und in inner join <Tabelle> on die zweite Tabelle an. Danach folgt ohne where die Gleichheitsbedingung. |
Ein Beispiel: | |
Es soll ein Join zwischen der Fahrer-Tabelle und der Fahrt-Tabelle durchgeführt werden. | |
select * from Fahrer, Fahrt where Fahrer.PersonalNr = Fahrt.PersonalNr |
select * from Fahrer inner join Fahrt on Fahrer.PersonalNr = Fahrt.PersonalNr |
Ergebnis des Joins | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Erläuterung der Join-Tabelle |
Die Join-Tabelle besteht aus den sieben Spalten der Fahrer-Tabelle und den acht Spalten der Fahrt-Tabelle. Die Spalte PersonalNr kommt zweimal vor: einmal als Primärschlüssel von Fahrer und einmal als Fremdschlüssel von Fahrt. In jeder Zeile stimmen die beiden PersonalNr-Werte überein, das heißt die Fahrer sind mit den korrespondierenden Werten aus der Fahrtentabelle zusammen geführt worden. |
top |
Noch ein Beispiel: | |
Im ER-Diagramm besteht eine 1:n-Beziehung zwischen Fahrt und Bus, welche auf zwei Relationen abgebildet wurde:
• Fahrt(FahrtNr, ↑Kennzeichen, ↑PersonalNr, Datum , Preis, Dauer, Reisestart, Reiseziel) • Bus(Kennzeichen, Bustyp, Baujahr, Sitzplätze) Der Primärschlüssel Kennzeichen der Bus-Tabelle steht als Fremdschlüssel in der Fahrtentabelle. Es sollen die Fahrten ausgegeben werden, für die Busse mit mehr als 50 Sitzplätzen eingesetzt werden. Dazu verbinden wir die beiden Tabellen mit einem Join über das gemeinsame Attribut Kennzeichen und selektieren diejenigen mit Sitzplätze > 50. | |
select FahrtNr, Datum, Reiseziel from Fahrt, Bus where Fahrt.Kennzeichen = Bus.Kennzeichen and Sitzplätze > 50 |
select FahrtNr, Datum, Reiseziel from Fahrt inner join Bus on Fahrt.Kennzeichen = Bus.Kennzeichen where Sitzplätze > 50 |
Ergebnis des Joins | ||||||||||||||||||
|
top |
Der Doppel-Join | |
Wir können die drei Tabellen Fahrer, Fahrt und Bus mit einem Join zwischen Fahrer und Fahrt über das Schlüsselattribut PersonalNr und einen weiteren Join zwischen Fahrt und Bus über das Schlüsselattribut Kennzeichen zu einer Tabelle zusammenführen. | |
select Fahrer.PersonalNr, Name, Vorname, FahrtNr, Bus.Kennzeichen, Datum, Preis, Dauer, Reisestart, Reiseziel, Bustyp from Fahrer, Fahrt, Bus where Fahrer.PersonalNr = Fahrt.PersonalNr and Fahrt.Kennzeichen = Bus.Kennzeichen |
select Fahrer.PersonalNr, Name, Vorname, FahrtNr, Bus.Kennzeichen, Datum, Preis, Dauer, Reisestart, Reiseziel, Bustyp from Fahrer inner join Fahrt on Fahrer.PersonalNr = Fahrt.PersonalNr inner join Bus on Fahrt.Kennzeichen = Bus.Kennzeichen |
Ausschnitt aus der Doppel-Join-Tabelle | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
top |
Erläuterung der Doppel-Join-Tabelle | |
Die Doppel-Join-Tabelle besteht aus den elf bei select angegebenen Spalten.
Tipp: In der from-Klausel kann man den Tabellen kurze Alias-Namen geben, um in der select-Klausel Schreibarbeit zu sparen. In obiger select-Anweisung können wir zum Beispiel mit den Alias-Namen D, F und B arbeiten. | |
select * from Fahrer D, Fahrt F, Bus B where D.PersonalNr = F.PersonalNr and F.Kennzeichen = B.Kennzeichen |
select * from Fahrer D inner join Fahrt F on D.PersonalNr = F.PersonalNr inner join Bus B on F.Kennzeichen = B.Kennzeichen |
Hinweis: |
Will man auch die PersonalNr und das Kennzeichen ausgeben, so liefert die Datenbank den Hinweis, dass PersonalNr und Kennzeichen ambiguous (engl. = doppeldeutig) sind. Das rührt daher, dass das PersonalNr und Kennzeichen als Schlüsselattribute in jeweils zwei Tabellen vorkommt. Man muss daher angeben, aus welcher Tabelle man die PersonalNr und das Kennzeichen nehmen will, also zum Beispiel Bus.Kennzeichen schreiben: select Fahrer.PersonalNr, Platz, Name, Bus.Kennzeichen, ... from ... |
top |
Und jetzt die Krönung - der Triple-Join | |
Mit drei Joins können wir vier Tabellen der Busunternehmen-Datenbank verbinden.
Gesucht sind alle Namen (Nachname und Vorname) der Kunden, die einen Bus gebucht haben, der vor dem Jahr 2000 gebaut wurde. Zusätzlich sollen noch die Anzahl der gebuchten Plätze und das Baujahr ausgegeben werden. | |
select Nachname, Vorname, GebuchtePlätze, Baujahr from Fahrt F, Kunde K, Bus B, Bucht O where K.KundenNr = O.KundenNr and O.FahrtNr = F.FahrtNr and F.Kennzeichen = B.Kennzeichen and B.Baujahr < 2000 order by Nachname |
select Nachname, Vorname, GebuchtePlätze, Baujahr from Kunde K inner join Bucht O on O.KundenNr = K.KundenNr inner join Fahrt F on F.FahrtNr = O.FahrtNr inner join Bus B on F.Kennzeichen = B.Kennzeichen where Baujahr < 2000 order by Nachname |
Ergebnis des Tripel-Joins | ||||||||||||||||
|
top |
Jetzt geht's los! |
Genug der grauen Theorie - jetzt wird es praktisch. Hier geht es zu den Übungen der Lektion 3. |