SQL Tutorial

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

PersonalNr	Name	Vorname	StraßeNr	PLZ	Ort	Telefon	FahrtNr	Kennzeichen	PersonalNr	Datum	Preis	Dauer	Reisestart	Reiseziel

1	Meier	Manfred	Maintalstr 5	69546	Mannheim	06958/512385	4	HH-BV 775	1	2014-04-12	199.99	2400	Madrid	Moskau
2	Müller	Doris	Neue Str 3	69553	Erfelden	06253/654127	1	MA-IN 248	2	2013-06-21	12.85	50	Mannheim	Darmstadt
2	Müller	Doris	Neue Str 3	69553	Erfelden	06253/654127	5	F-AZ 1234	2	2015-01-04	24.99	240	Jena	Köln
3	Scharf	Wilhelm	Erfelderstr 136	66659	Leeheim	06157/12399	2	MA-IN 248	3	2014-03-13	32.5	540	Bern	Berlin
4	Niemand	Josef	Seeheimerstr 24	64297	Darmstadt-Eberstadt	06151/517989	3	DA-AB 123	4	2012-06-18	31.95	330	München	Dortmund
4	Niemand	Josef	Seeheimerstr 24	64297	Darmstadt-Eberstadt	06151/517989	8	F-AZ 123	4	2013-08-18	52.6	840	Florenz	Darmstadt
5	Grieser	Bianca	Friedrich-Ebert-Str 55	64342	Seeheim-Jugenheim	06257/095509	7	F-FH 1059	5	2014-03-28	33	300	Worms	Hamburg
6	Mayer	Daniel	Heinrichstr 174	64287	Darmstadt	06151/18564	6	DA-KK 007	6	2013-08-08	64.5	600	Darmstadt	Cannes

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.

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:

FahrtNr	Datum	Reiseziel

7	2013-03-20	Worms
4	2012-04-17	Madrid
1	2012-06-07	Mannheim
2	2013-03-21	Bern

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

PersonalNr	Name	Vorname	FahrtNr	Kennzeichen	Datum	Preis	Strecke	Reiseziel	Reisestart	Bustyp

4	Niemand	Josef	3	DA-AB 123	2012-06-18	31.95	330	München	Dortmund	Volkswagen 7HM
6	Mayer	Daniel	6	DA-KK 007	2013-08-08	64.5	600	Cannes	Darmstadt	Mercedes-Benz O 345
2	Müller	Doris	5	F-AZ 1234	2015-01-04	24.99	240	Köln	Jena	Mercedes-Benz O 303
4	Niemand	Josef	8	F-AZ 1234	2013-08-18	52.6	840	Darmstadt	Florenz	Mercedes-Benz O 303
5	Bianca	Grieser	7	F-FH 1059	2014-03-28	33	300	Hamburg	Worms	Mercedes-Benz 510
1	Meier	Manfred	4	HH-BV 775	2014-04-12	199.99	2400	Moskau	Madrid	Volvo Sideral
2	Müller	Doris	1	MA-IN 248	2013-06-21	12.85	50	Darmstadt	Mannheim	Mercedes-Benz O 135
3	Scharf	Wilhelm	2	MA-IN 248	2014-03-13	32.5	540	Berlin	Bern	Mercedes-Benz O 135

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 ...

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 der Triple-Joins:

Nachname	Vorname	GebuchtePlätze	Baujahr

Struve	Johannes	2	1997
Wutz	Martin	3	1983

Jetzt geht's los!

Genug der grauen Theorie - jetzt wird es praktisch.
Hier geht es zu den Übungen der Lektion 3.