Einfache Tabellenverknüpfung
Dieses Kapitel behandelt den „traditionellen“ Weg, mehrere Tabellen gleichzeitig abzufragen. Dazu werden in der FROM-Klausel alle Tabellen aufgeführt; die WHERE-Klausel enthält neben den Auswahlbedingungen auch Verknüpfungsbedingungen, wie die Tabellen zueinander gehören.
Alle Kombinationen aller Datensätze
[Bearbeiten]Der einfachste Weg, Tabellen zu verknüpfen, ist ein Befehl wie der folgende, in dem verschiedene Spalten aus zwei Tabellen zusammengefasst werden. Aber das Ergebnis sieht reichlich seltsam aus.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID as DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
FROM Mitarbeiter mi, Dienstwagen dw;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN Typ
----- ------------ --------- --- ----------- ---
10001 Müller Kurt 1 DO-WB 421 14
10002 Schneider Daniela 1 DO-WB 421 14
20001 Meyer Walter 1 DO-WB 421 14
20002 Schmitz Michael 1 DO-WB 421 14
30001 Wagner Gaby 1 DO-WB 421 14
30002 Feyerabend Werner 1 DO-WB 421 14
40001 Langmann Matthias 1 DO-WB 421 14
40002 Peters Michael 1 DO-WB 421 14
/* usw. */
10001 Müller Kurt 2 DO-WB 422 14
10002 Schneider Daniela 2 DO-WB 422 14
20001 Meyer Walter 2 DO-WB 422 14
20002 Schmitz Michael 2 DO-WB 422 14
/* usw. */
Tatsächlich erzeugt dieser Befehl das „kartesische Produkt“ der beiden Tabellen: Jeder Datensatz der einen Tabelle wird (mit den gewünschten Spalten) mit jedem Datensatz der anderen Tabelle verbunden. Das sieht also so aus, als wenn alle Dienstwagen zu jedem Mitarbeiter gehören würden, was natürlich Quatsch ist.
Diese Variante ist also in aller Regel sinnlos (wenn auch syntaktisch korrekt). Nützlich ist sie nur dann, wenn auf einfachem Wege große Mengen von Testdaten erzeugt werden sollen, wie es im Kapitel Testdaten erzeugen benutzt wird.
Zwei Tabellen einfach verbinden
[Bearbeiten]Sinnvoll wird die vorstehende Abfrage durch eine kleine Ergänzung. Was will man denn eigentlich wissen?
- Gib mir (einige) Spalten der Tabelle Mitarbeiter zusammen mit (einigen) Spalten der Tabelle Dienstwagen, und zwar bei jedem Mitarbeiter denjenigen Dienstwagen, der zu diesem Mitarbeiter gehört.
Woran erkennt man, zu welchem Mitarbeiter ein Dienstwagen gehört? Nun, in der Tabelle Dienstwagen ist eine Spalte Mitarbeiter_ID enthalten; dieser Wert ist identisch mit der ID eines Eintrags in der Tabelle Mitarbeiter.
Wenn man diese Anfrage und diese Information in „Pseudocode“ übersetzt, dann kommt so etwas heraus:
Hole Spalten der Tabelle Mitarbeiter sowie Spalten der Tabelle Dienstwagen wobei die Mitarbeiter_ID eines Dienstwagens gleich ist der ID eines Mitarbeiters
Das können wir nun in eine vollständige SQL-Abfrage übersetzen; die obige Abfrage muss nur minimal erweitert werden:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi, Dienstwagen dw
WHERE dw.Mitarbeiter_ID = mi.ID
order by MitNr;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
100001 Grosser Horst 10 DO-WB 4210 14
10001 Müller Kurt 1 DO-WB 421 14
110001 Eggert Louis 11 DO-WB 4211 14
120001 Carlsen Zacharias 12 DO-WB 4212 14
20001 Meyer Walter 2 DO-WB 422 14
30001 Wagner Gaby 3 DO-WB 423 14
40001 Langmann Matthias 4 DO-WB 424 14
50001 Pohl Helmut 5 DO-WB 425 14
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
/* usw. */
Wir bekommen also tatsächlich genau diejenigen Mitarbeiter, die über einen (persönlichen) Dienstwagen verfügen.
Hinweis: Wundern Sie sich nicht über die seltsame Reihenfolge. Die Personalnummer wurde als VARCHAR definiert; also kommt das Ergebnis in alphabetischer und nicht in numerischer Reihenfolge.
Mehrere Tabellen verbinden
[Bearbeiten]In der gleichen Weise können auch mehr als zwei Tabellen verknüpft werden. Im Kapitel Gruppierungen steht ein Beispiel ähnlich wie dieses:
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf, Zuordnung_SF_FZ zu,
Fahrzeug fz, Fahrzeugtyp ft, Fahrzeughersteller fh
where sf.ID = zu.Schadensfall_ID
and fz.ID = zu.Fahrzeug_ID
and ft.ID = fz.Fahrzeugtyp_ID
and fh.ID = ft.Hersteller_ID
GROUP BY Hersteller_ID, Hersteller_Name, Jahr
ORDER BY Jahr, Hersteller_ID;
Wichtig ist, dass es immer eine eindeutige Zuordnung zwischen jeweils einer Spalte einer Tabelle und einer Spalte einer anderen Tabelle gibt. Bitte beachten Sie dabei:
- Statt einer einzigen Spalte kann auch eine Gruppe von Spalten verknüpft werden (z. B. Name + Vorname). Dies macht aber alles umständlicher, unübersichtlicher und unsicherer. Deshalb sollte vorzugsweise über eindeutige IDs o. ä. verknüpft werden.
- Wenn es zwischen einzelnen Tabellen keine „gemeinsamen“ Spalten gibt, dann kommt wieder das kartesische Produkt heraus; das Ergebnis ist dann eher sinnlos.
Verknüpfungs- und Abfragebedingungen
[Bearbeiten]Je mehr Kombinationen benötigt werden, desto unübersichtlicher wird diese Konstruktion. Dabei enthält die WHERE-Klausel bisher nur die Verknüpfungen zwischen den Tabellen, aber noch keine Suchbedingungen wie hier:
select ... from ... where ...
and Jahr in [2006, 2007, 2008]
and fhe.Land in ['Schweden', 'Norwegen', 'Finnland']
order by Jahr, Hersteller_ID;
Das führt außerdem dazu, dass die WHERE-Klausel sachlich gewünschte Suchbedingungen und logisch benötigte Verknüpfungsbedingungen vermischt. Wer soll da noch durchblicken? Besser ist das in den nächsten Kapiteln ausführlich behandelte Verfahren mit JOIN.
Zusammenfassung
[Bearbeiten]Dieses Kapitel erläutert, wie mehrere Tabellen einfach durch die FROM-Klausel und passende WHERE-Bedingungen verknüpft werden können:
- In der Spaltenliste sollte immer der jeweilige Tabellenname angegeben werden; es kann auch ein Kürzel als Tabellen-Alias verwendet werden.
- In der FROM-Klausel werden alle Tabellen aufgelistet und in der WHERE-Klausel durch geeignete Bedingungen aufeinander bezogen.
- Durch die Vermischung zwischen Verknüpfungs- und Auswahlbedingungen wird dieses Verfahren schnell unübersichtlich.
Übungen
[Bearbeiten]Bei den folgenden Abfragen beziehen wir uns auf den Bestand der Beispieldatenbank im „Anfangszustand“: die Tabellen Versicherungsvertrag, Fahrzeug, Mitarbeiter mit jeweils etwa 28 Einträgen und Versicherungsnehmer mit etwa 26 Einträgen.
| Übung 1 | Eine einfache Abfrage | Zur Lösung |
Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag, die nur die wichtigsten Informationen (einschließlich der IDs auf andere Tabellen) enthält. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 2 | Das kartesische Produkt | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 1, sodass anstelle der Versicherungsnehmer_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 3 | Das kartesische Produkt | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 2, sodass anstelle der Fahrzeug_ID das Kennzeichen und anstelle der Mitarbeiter_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 2, sodass Name und Vorname des Versicherungsnehmers genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 6 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 5, sodass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:
- Es geht ausschließlich um Eigene Kunden.
- Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
- Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
- Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.
Wie viele Einträge zeigt die Ergebnismenge an?
| Lösung zu Übung 1 | Eine einfache Abfrage | Zur Übung |
SELECT Vertragsnummer, Abschlussdatum, Art,
Versicherungsnehmer_ID, Fahrzeug_ID, Mitarbeiter_ID
from Versicherungsvertrag
Es werden 28 Zeilen angezeigt.
| Lösung zu Übung 2 | Das kartesische Produkt | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv, Versicherungsnehmer vn;
Es werden etwa 728 Zeilen angezeigt.
| Lösung zu Übung 3 | Das kartesische Produkt | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi;
Es werden etwa 570 752 Zeilen angezeigt.
| Lösung zu Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv, Versicherungsnehmer vn
where vn.ID = vv.Versicherungsnehmer_ID;
Es werden etwa 28 Zeilen angezeigt.
| Lösung zu Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi
where vn.ID = vv.Versicherungsnehmer_ID
and fz.ID = vv.Fahrzeug_ID
and mi.ID = vv.Mitarbeiter_ID;
Es werden etwa 28 Zeilen angezeigt.
| Lösung zu Übung 6 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi
where vn.ID = vv.Versicherungsnehmer_ID
and fz.ID = vv.Fahrzeug_ID
and mi.ID = vv.Mitarbeiter_ID
and vn.Eigener_kunde = 'J'
and ( ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
OR ( vv.Art = 'VK' )
or ( fz.Kennzeichen STARTING WITH 'RE-' ) );
Es werden etwa 19 Zeilen angezeigt. Die OR-Verknüpfungen könnten teilweise auch mit CASE geschrieben werden.
Siehe auch
[Bearbeiten]Bei Wikipedia stehen weitere Hinweise: