vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel


6


Tabellen verknüpfen

Ziele

Einführung

Mehrere Tabellen in einer einzelnen SELECT-Anweisung

Equi Joins

Non-Equi-Joins

Outer Joins vs. Inner Joins

Eine Tabelle mit sich selbst verknüpfen

Zusammenfassung

Fragen und Antworten

Workshop


Ziele

Mit Verknüpfungen - englisch Joins - lassen sich Daten über mehrere Tabellen hinweg manipulieren und abrufen. Die Themen der heutigen Lektion beschäftigen sich mit den folgenden Verknüpfungen:



Einführung

Zu den leistungsfähigsten Merkmalen von SQL gehört die Fähigkeit, Daten über mehrere Tabellen hinweg abzurufen und zu manipulieren. Wenn man auf diese Mechanismen nicht zurückgreifen könnte, müßte man alle für eine Anwendung erforderlichen Datenelemente in einer einzelnen Tabelle speichern. Bei mehreren Anwendungen müßte man ohne gemeinsame Tabellen die gleichen Daten in mehreren Tabellen ablegen. Stellen Sie sich den riesigen Aufwand vor, wenn man Datenbanken und Tabellen neu entwerfen, aufbauen und füllen müßte, nur weil der Benutzer eine Abfrage mit einem neuen Datenelement benötigt. Durch die JOIN-Anweisung von SQL lassen sich kleinere und speziellere Tabellen erstellen, die außerdem leichter zu warten sind als große Tabellen.



Mehrere Tabellen in einer einzelnen SELECT-Anweisung

Wie Dorothee im Zauberer von Oz verfügen Sie bereits seit dem zweiten Tag mit den Anweisungen SELECT und FROM über die Macht, Tabellen zu verknüpfen. Im Gegensatz zu Dorothee brauchen Sie aber keinen Zauberer, um eine Verknüpfung auszuführen. Die Beispiele arbeiten mit zwei Tabellen, die sehr naheliegend mit TABELLE1 und TABELLE2 benannt sind.

Die Abfragen in den heutigen Beispielen sind mit ISQL von Borland entstanden. Gegenüber den bisher im Buch gezeigten Abfragen sind einige Unterschiede festzustellen. Beispielsweise beginnen die mit ISQL erzeugten Abfragen nicht mit dem SQL-Prompt. Weiterhin muß man eine ISQL-Anweisung nicht mit einem Semikolon abschließen. (Das Semikolon ist in ISQL optional.) Die grundlegenden SQL-Konstruktionen sind aber trotzdem gleich.

SELECT *
FROM TABELLE1


ZEILE BEMERKUNGEN
========== ============

Zeile 1 Tabelle 1
Zeile 2 Tabelle 1
Zeile 3 Tabelle 1
Zeile 4 Tabelle 1
Zeile 5 Tabelle 1
Zeile 6 Tabelle 1


SELECT *
FROM TABELLE2

ZEILE BEMERKUNGEN
========== ============

Zeile 1 Tabelle 2
Zeile 2 Tabelle 2
Zeile 3 Tabelle 2
Zeile 4 Tabelle 2
Zeile 5 Tabelle 2
Zeile 6 Tabelle 2


Diese beiden Tabellen verknüpfen Sie mit der folgenden Anweisung:


SELECT *
FROM TABELLE1, TABELLE2

ZEILE BEMERKUNGEN ZEILE BEMERKUNGEN
========== ============ ========== ============
Zeile 1 Tabelle 1 Zeile 1 Tabelle 2
Zeile 1 Tabelle 1 Zeile 2 Tabelle 2
Zeile 1 Tabelle 1 Zeile 3 Tabelle 2
Zeile 1 Tabelle 1 Zeile 4 Tabelle 2
Zeile 1 Tabelle 1 Zeile 5 Tabelle 2
Zeile 1 Tabelle 1 Zeile 6 Tabelle 2
Zeile 2 Tabelle 1 Zeile 1 Tabelle 2
Zeile 2 Tabelle 1 Zeile 2 Tabelle 2
Zeile 2 Tabelle 1 Zeile 3 Tabelle 2
Zeile 2 Tabelle 1 Zeile 4 Tabelle 2
Zeile 2 Tabelle 1 Zeile 5 Tabelle 2
Zeile 2 Tabelle 1 Zeile 6 Tabelle 2
Zeile 3 Tabelle 1 Zeile 1 Tabelle 2
Zeile 3 Tabelle 1 Zeile 2 Tabelle 2
Zeile 3 Tabelle 1 Zeile 3 Tabelle 2
Zeile 3 Tabelle 1 Zeile 4 Tabelle 2
Zeile 3 Tabelle 1 Zeile 5 Tabelle 2
Zeile 3 Tabelle 1 Zeile 6 Tabelle 2
Zeile 4 Tabelle 1 Zeile 1 Tabelle 2
Zeile 4 Tabelle 1 Zeile 2 Tabelle 2
Zeile 4 Tabelle 1 Zeile 3 Tabelle 2
Zeile 4 Tabelle 1 Zeile 4 Tabelle 2
Zeile 4 Tabelle 1 Zeile 5 Tabelle 2
Zeile 4 Tabelle 1 Zeile 6 Tabelle 2
Zeile 5 Tabelle 1 Zeile 1 Tabelle 2
Zeile 5 Tabelle 1 Zeile 2 Tabelle 2
Zeile 5 Tabelle 1 Zeile 3 Tabelle 2
Zeile 5 Tabelle 1 Zeile 4 Tabelle 2
Zeile 5 Tabelle 1 Zeile 5 Tabelle 2
Zeile 5 Tabelle 1 Zeile 6 Tabelle 2
Zeile 6 Tabelle 1 Zeile 1 Tabelle 2
Zeile 6 Tabelle 1 Zeile 2 Tabelle 2
Zeile 6 Tabelle 1 Zeile 3 Tabelle 2
Zeile 6 Tabelle 1 Zeile 4 Tabelle 2
Zeile 6 Tabelle 1 Zeile 5 Tabelle 2
Zeile 6 Tabelle 1 Zeile 6 Tabelle 2


Sechsunddreißig Zeilen! Woher kommen sie, und von welchem Typ ist diese Verknüpfung?

Eine nähere Untersuchung der Ergebnisse Ihrer ersten Verknüpfung zeigt, daß jede Zeile aus TABELLE1 zu jeder Zeile von TABELLE2 hinzugefügt wurde. Ein Auszug dieser Verknüpfung soll das verdeutlichen:

ZEILE BEMERKUNGEN ZEILE BEMERKUNGEN
========== ============ ========== ============

Zeile 1 Tabelle 1 Zeile 1 Tabelle 2
Zeile 1 Tabelle 1 Zeile 2 Tabelle 2
Zeile 1 Tabelle 1 Zeile 3 Tabelle 2
Zeile 1 Tabelle 1 Zeile 4 Tabelle 2
Zeile 1 Tabelle 1 Zeile 5 Tabelle 2
Zeile 1 Tabelle 1 Zeile 6 Tabelle 2


Alle Zeilen von TABELLE2 wurden mit Zeile 1 in TABELLE1 kombiniert. Gratulation! Gerade haben Sie Ihre erste Verknüpfung - oder einen Verbund (Join) - realisiert. Aber um welche Art von Verknüpfung handelt es sich? Eine Inner Join? Eine Outer Join? Die hier gezeigte Verknüpfung bezeichnet man als Cross Join. Normalerweise bringt eine Cross Join nicht soviel Nutzen wie die anderen der heute behandelten Verknüpfungen. Diese Verknüpfung demonstriert aber die grundlegende Kombinationseigenschaft aller Verbundtypen: Verknüpfungen bringen Tabellen zusammen.


Nehmen wir an, daß Sie Ihren Lebensunterhalt mit dem Verkauf von Fahrradteilen verdienen. Der Entwurf Ihrer Datenbank sieht eine große Tabelle mit allen relevanten Spalten vor. Bei jeder neuen Bedingung haben Sie eine neue Spalte hinzugefügt oder gleich eine neue Tabelle aufgebaut. In die neue Tabelle waren alle alten Daten zu übernehmen und die für eine bestimmte Abfrage erforderlichen neuen Daten hinzuzufügen. Die einzige Tabelle Ihrer Datenbank bricht schließlich unter ihrem eigenen Gewicht zusammen - keine rosigen Aussichten. Bei einem alternativen Entwurf, der auf einem relationalen Modell basiert, würde man zusammengehörige Daten in je einer eigenen Tabelle unterbringen. Die Tabelle KUNDE könnte dann folgendermaßen aussehen:


SELECT *
FROM KUNDE


NAME ADRESSE STAAT ZIP TELEFON BEMERKUNGEN
========== ========== ====== ====== ======== ====================

TRUE WHEEL 55O HUSKER NE 58702 555-4545 KEINE
BIKE SPEC CPT SHRIVE LA 45678 555-1234 KEINE
LE SHOPPE HOMETOWN KS 54678 555-1278 KEINE
AAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGR
JACKS BIKE 24 EGLIN FL 34567 555-2314 KEINE


Diese Tabelle enthält alle Informationen, die Sie zur Beschreibung Ihrer Kunden benötigen. Die verkauften Artikel kommen in eine andere Tabelle:

SELECT *
FROM TEIL

TEILNUM BESCHREIBUNG PREIS
=========== ==================== ===========

54 PEDALE 54.25
42 SATTEL 24.50
46 REIFEN 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00


Die Bestellungen erhalten ebenfalls eine eigene Tabelle:


SELECT *
FROM BESTELLUNGEN

BESTDATUM NAME TEILNUM MENGE BEMERKUNGEN
=========== ========== =========== =========== ====================

15-MAY-1997 TRUE WHEEL 23 6 BEZAHLT
19-MAY-1997 TRUE WHEEL 76 3 BEZAHLT
2-SEP-1997 TRUE WHEEL 10 1 BEZAHLT
30-JUN-1997 TRUE WHEEL 42 8 BEZAHLT
30-JUN-1997 BIKE SPEC 54 10 BEZAHLT
30-MAY-1997 BIKE SPEC 10 2 BEZAHLT
30-MAY-1997 BIKE SPEC 23 8 BEZAHLT
17-JAN-1997 BIKE SPEC 76 11 BEZAHLT
17-JAN-1997 LE SHOPPE 76 5 BEZAHLT
1-JUN-1997 LE SHOPPE 10 3 BEZAHLT
1-JUN-1997 AAA BIKE 10 1 BEZAHLT
1-JUL-1997 AAA BIKE 76 4 BEZAHLT
1-JUL-1997 AAA BIKE 46 14 BEZAHLT
11-JUL-1997 JACKS BIKE 76 14 BEZAHLT


Bei dieser Lösung können durchaus drei spezialisierte Mitarbeiter oder Abteilungen die Verwaltung der entsprechenden Tabellen in eigener Regie übernehmen. Damit ist ein Datenbankadministrator überflüssig, der alle Aspekte Ihres Projekts kennen muß, um eine riesige Datenbank für mehrere Abteilungen zu pflegen. Im Zeitalter der Netzwerke kann man die drei Tabellen sogar auf verschiedenen Computern unterbringen. Die mit den jeweiligen Daten am besten vertrauten Mitarbeiter übernehmen die Verwaltung der entsprechenden Tabelle und speichern sie auf der eigenen Maschine. (Damit erübrigen sich die großen Firmenrechner, um die sich Heerscharen von Systemadministratoren kümmern.)


Verbinden Sie nun die Tabellen TEIL und BESTELLUNGEN:


SELECT B.BESTDATUM, B.NAME, B.TEILNUM,
T.TEILNUM, T.BESCHREIBUNG
FROM BESTELLUNGEN B, TEIL T

BESTDATUM NAME TEILNUM TEILNUM BESCHREIBUNG
=========== ========== =========== =========== ====================

15-MAY-1997 TRUE WHEEL 23 54 PEDALE
19-MAY-1997 TRUE WHEEL 76 54 PEDALE
2-SEP-1997 TRUE WHEEL 10 54 PEDALE
30-JUN-1997 TRUE WHEEL 42 54 PEDALE
30-JUN-1997 BIKE SPEC 54 54 PEDALE
30-MAY-1997 BIKE SPEC 10 54 PEDALE
30-MAY-1997 BIKE SPEC 23 54 PEDALE
17-JAN-1997 BIKE SPEC 76 54 PEDALE
17-JAN-1997 LE SHOPPE 76 54 PEDALE
1-JUN-1997 LE SHOPPE 10 54 PEDALE
1-JUN-1997 AAA BIKE 10 54 PEDALE
1-JUL-1997 AAA BIKE 76 54 PEDALE
1-JUL-1997 AAA BIKE 46 54 PEDALE
11-JUL-1997 JACKS BIKE 76 54 PEDALE
...

Diese Ergebnisliste stellt nur einen Ausschnitt dar. Insgesamt besteht die Ergebnismenge aus 84 Zeilen - 14 Zeilen in der Tabelle BESTELLUNGEN multipliziert mit 6 Zeilen in der Tabelle TEIL. Die Liste ist dem Ergebnis des weiter vorn gezeigten Beispiels zur Verknüpfung der Tabellen TABELLE1 und TABELLE2 ähnlich. Noch fehlt die entscheidende Anweisung, um die Lücke zu einem sinnvollen Einsatz zu schließen. Bevor wir diese Anweisung offenlegen, wenden wir uns zunächst einem anderen Einsatzfall von Aliasnamen zu.


Die richtige Spalte ermitteln

Bei der Verbindung von TABELLE1 und TABELLE2 haben Sie mit SELECT * alle Spalten in beiden Tabellen zurückgegeben. In der Verknüpfung von BESTELLUNGEN mit TEIL sieht die SELECT-Anweisung etwas komplizierter aus:


SELECT B.BESTDATUM, B.NAME, B.TEILNUM,
T.TEILNUM, T.BESCHREIBUNG


Aus internen Tabellen kann SQL ableiten, daß BESTDATUM und NAME nur in BESTELLUNGEN existieren und daß BESCHREIBUNG ausschließlich in TEIL vorkommt. Wie steht es aber mit der Spalte TEILNUM, die in beiden Tabellen vorhanden ist? Wenn eine Spalte in zwei Tabellen denselben Namen hat, muß man in der SELECT-Klausel die anzuzeigende Spalte mit einem Alias spezifizieren. Gewöhnlich weist man jeder Tabelle einen einzelnen Buchstaben zu, wie es in der FROM-Klausel zu sehen ist:


FROM BESTELLUNGEN B, TEIL T


Analog zur obigen SELECT-Klausel spezifiziert man alle Spaltennamen mit diesem Zeichen. Man könnte die SELECT-Klausel auch folgendermaßen formulieren:


SELECT BESTDATUM, NAME, B.TEILNUM, T.TEILNUM, BESCHREIBUNG


Denken Sie aber daran, daß Sie irgendwann diese Abfrage überarbeiten müssen. Daher empfiehlt es sich, die Lesbarkeit der Anweisung von vornherein sicherzustellen. Kommen wir nun zur fehlenden Anweisung zurück.



Equi Joins

Ein Auszug der TEIL/BESTELLUNGEN-Verknüpfung soll einen Anhaltspunkt liefern, was noch fehlt:


30-JUN-1997 TRUE WHEEL 42 54 PEDALE
30-JUN-1997 BIKE SPEC 54 54 PEDALE
30-MAY-1997 BIKE SPEC 10 54 PEDALE


Die Felder TEILNUM sind beiden Tabellen gemeinsam. Hätte man die Abfrage auch wie folgt schreiben können?


SELECT B.BESTDATUM, B.NAME, B.TEILNUM,
T.TEILNUM, T.BESCHREIBUNG
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM


BESTDATUM NAME TEILNUM TEILNUM BESCHREIBUNG
=========== ========== =========== =========== ====================

1-JUN-1997 AAA BIKE 10 10 TANDEM
30-MAY-1997 BIKE SPEC 10 10 TANDEM
2-SEP-1997 TRUE WHEEL 10 10 TANDEM
1-JUN-1997 LE SHOPPE 10 10 TANDEM
30-MAY-1997 BIKE SPEC 23 23 MOUNTAIN BIKE
15-MAY-1997 TRUE WHEEL 23 23 MOUNTAIN BIKE
30-JUN-1997 TRUE WHEEL 42 42 SATTEL
1-JUL-1997 AAA BIKE 46 46 REIFEN
30-JUN-1997 BIKE SPEC 54 54 PEDALE
1-JUL-1997 AAA BIKE 76 76 ROAD BIKE
17-JAN-1997 BIKE SPEC 76 76 ROAD BIKE
19-MAY-1997 TRUE WHEEL 76 76 ROAD BIKE
11-JUL-1997 JACKS BIKE 76 76 ROAD BIKE
17-JAN-1997 LE SHOPPE 76 76 ROAD BIKE

Mit der in beiden Tabellen existierenden Spalte TEILNUM haben Sie gerade die Informationen aus der Tabelle BESTELLUNGEN mit den Informationen aus der Tabelle TEIL verknüpft, um eine Beschreibung der Teile anzuzeigen, die die Fahrradgeschäfte bei Ihnen bestellt haben. Die hier eingesetzte Verknüpfung ist eine sogenannte Equi Join (oder Gleichverbund), da die Tabellen über die Gleichheit aller gleichlautenden Spalten verknüpft werden.

Diese Abfrage läßt sich weiter spezifizieren, indem man zusätzliche Bedingungen in die WHERE-Klausel aufnimmt. Dazu ein Beispiel:


SELECT B.BESTDATUM, B.NAME, B.TEILNUM,
T.TEILNUM, T.BESCHREIBUNG
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND B.TEILNUM = 76

BESTDATUM NAME TEILNUM TEILNUM BESCHREIBUNG
=========== ========== =========== =========== ====================

1-JUL-1997 AAA BIKE 76 76 ROAD BIKE
17-JAN-1997 BIKE SPEC 76 76 ROAD BIKE
19-MAY-1997 TRUE WHEEL 76 76 ROAD BIKE
11-JUL-1997 JACKS BIKE 76 76 ROAD BIKE
17-JAN-1997 LE SHOPPE 76 76 ROAD BIKE


Die Zahl 76 ist nicht gerade aussagekräftig, und Sie wollen Ihren Kunden bestimmt nicht zumuten, sich eine Teilnummer zu merken. (Es gibt leider genügend Beispiele für Informationssysteme, bei denen der Benutzer einen unverständlichen Code kennen muß, obwohl es eigentlich eine treffende Bezeichnung gibt. Lassen Sie sich nicht zu derartigem Unsinn verleiten!) Die Abfrage kann man auch folgendermaßen formulieren:


SELECT B.BESTDATUM, B.NAME, B.TEILNUM,
T.TEILNUM, T.BESCHREIBUNG
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND T.BESCHREIBUNG = 'ROAD BIKE'

BESTDATUM NAME TEILNUM TEILNUM BESCHREIBUNG
=========== ========== =========== =========== ====================

1-JUL-1997 AAA BIKE 76 76 ROAD BIKE
17-JAN-1997 BIKE SPEC 76 76 ROAD BIKE
19-MAY-1997 TRUE WHEEL 76 76 ROAD BIKE
11-JUL-1997 JACKS BIKE 76 76 ROAD BIKE
17-JAN-1997 LE SHOPPE 76 76 ROAD BIKE


Sehen Sie sich in diesem Zusammenhang zwei andere Tabellen an. Untersuchen Sie, wie sich diese Tabellen verknüpfen lassen. Es liegt auf der Hand, daß die Spalte MITARBEITER_ID in diesem Beispiel eindeutige Werte enthalten sollte. Mitarbeiter können zwar den gleichen Namen haben, in derselben Abteilung arbeiten und das gleiche Gehalt beziehen. Allerdings sollte jeder Mitarbeiter eine unverwechselbare MITARBEITER_ID (oder Personalnummer) erhalten. Die Tabellen lassen sich nun über die Spalte MITARBEITER_ID verknüpfen.


MITARBEITER_TABELLE

MITARBEITER_LOHN_TABELLE

MITARBEITER_ID

MITARBEITER_ID

NACHNAME

GEHALT

VORNAME

ABTEILUNG

MITTL_NAME

ABTEILUNGSLEITER

 

FAMILIENSTAND

SELECT M.MITARBEITER_ID, M.NACHNAME, ML.GEHALT
FROM MITARBEITER_TBL M,
MITARBEITER_LOHN_TBL ML
WHERE M.MITARBEITER_ID = ML.MITARBEITER_ID
AND M.NACHNAME = 'SMITH';


M.MITARBEITER_ID M.NACHNAME ML.GEHALT
================ ========== =========

13245 SMITH 35000.00

Wenn man zwei Tabellen ohne WHERE-Klausel verknüpft, erhält man das sogenannte kartesische Produkt. Eine derartige Verknüpfung kombiniert alle Zeilen aus allen Tabellen in der FROM-Klausel. Hat jede Tabelle einen Umfang von 200 Zeilen, enthält die Ergebnismenge bei zwei Tabellen schließlich 40.000 Zeilen (200 mal 200). Daher sollten Sie Ihre Tabellen immer in der WHERE-Klausel verknüpfen, solange keine Notwendigkeit besteht, alle Zeilen aller Tabellen miteinander zu kombinieren.

Kehren wir zu den ursprünglichen Tabellen zurück. Mit dem bisher Gelernten sind Sie in der Lage, praktisch sinnvolle Verknüpfungen zu realisieren. Zunächst ermitteln wir den Umsatz durch den Verkauf von Road Bikes:


SELECT SUM(B.MENGE * T.PREIS) GESAMT
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND T.BESCHREIBUNG = 'ROAD BIKE'

GESAMT
===========

19610.00

Mit dieser Einrichtung können die Mitarbeiter der Verkaufsabteilung die Tabelle BESTELLUNGEN auf dem neuesten Stand halten, die Produktionsabteilung kann die Tabelle TEIL aktualisieren, und Sie können das Umsatzziel herausfinden, ohne die Datenbank neu entwerfen zu müssen.

Beachten Sie die einheitliche Verwendung von Aliasnamen für Tabellen und Spalten in den Beispielen der SQL-Anweisungen. Mit Aliasnamen lassen sich viele Tastenanschläge einsparen. Außerdem fördert diese Methode die Lesbarkeit der Anweisungen.

Kann man auch mehrere Tabellen verknüpfen? Die folgende Anweisung generiert zum Beispiel die Informationen, die man für das Verschicken einer Rechnung benötigt:


SELECT K.NAME, K.ADRESSE, (B.MENGE * T.PREIS) GESAMT
FROM BESTELLUNGEN B, TEIL T, KUNDE K
WHERE B.TEILNUM = T.TEILNUM
AND B.NAME = K.NAME

NAME ADRESSE GESAMT
========== ========== ===========

TRUE WHEEL 55O HUSKER 1200.00
BIKE SPEC CPT SHRIVE 2400.00
LE SHOPPE HOMETOWN 3600.00
AAA BIKE 10 OLDTOWN 1200.00
TRUE WHEEL 55O HUSKER 2102.70
BIKE SPEC CPT SHRIVE 2803.60
TRUE WHEEL 55O HUSKER 196.00
AAA BIKE 10 OLDTOWN 213.50
BIKE SPEC CPT SHRIVE 542.50
TRUE WHEEL 55O HUSKER 1590.00
BIKE SPEC CPT SHRIVE 5830.00
JACKS BIKE 24 EGLIN 7420.00
LE SHOPPE HOMETOWN 2650.00
AAA BIKE 10 OLDTOWN 2120.00


Mit der folgenden Anweisung läßt sich die Ausgabe übersichtlicher gestalten:


SELECT K.NAME, K.ADRESSE,
B.MENGE * T.PREIS GESAMT
FROM BESTELLUNGEN B, TEIL T, KUNDE K
WHERE B.TEILNUM = T.TEILNUM
AND B.NAME = K.NAME
ORDER BY K.NAME

NAME ADRESSE GESAMT
========== ========== ===========

AAA BIKE 10 OLDTOWN 213.50
AAA BIKE 10 OLDTOWN 2120.00
AAA BIKE 10 OLDTOWN 1200.00
BIKE SPEC CPT SHRIVE 542.50
BIKE SPEC CPT SHRIVE 2803.60
BIKE SPEC CPT SHRIVE 5830.00
BIKE SPEC CPT SHRIVE 2400.00
JACKS BIKE 24 EGLIN 7420.00
LE SHOPPE HOMETOWN 2650.00
LE SHOPPE HOMETOWN 3600.00
TRUE WHEEL 55O HUSKER 196.00
TRUE WHEEL 55O HUSKER 2102.70
TRUE WHEEL 55O HUSKER 1590.00
TRUE WHEEL 55O HUSKER 1200.00


Bei der Verknüpfung der drei Tabellen BESTELLUNGEN, TEIL und KUNDE ist die Tabelle BESTELLUNGEN an zwei Verknüpfungen beteiligt, während die anderen beiden Tabellen nur einmal verwendet werden. Man bezeichnet Tabellen, die mit den angegebenen Bedingungen die wenigsten Zeilen zurückgeben, auch als Basistabellen. Man verknüpft normalerweise alle anderen Tabellen in einer Abfrage mit der Basistabelle, um das Abrufen der Daten effizienter zu gestalten. In diesem Beispiel übernimmt folglich die Tabelle BESTELLUNGEN die Rolle der Basistabelle. In den meisten Datenbanken verknüpfen wenige Basistabellen alle anderen Tabellen (entweder direkt oder indirekt). Tag 15 geht näher auf das Konzept der Basistabellen ein.

Die vorherige Abfrage läßt sich erweitern, um die Aussagekraft mit der zusätzlichen Spalte BESCHREIBUNG zu erhöhen:


SELECT K.NAME, K.ADRESSE,
B.MENGE * T.PREIS GESAMT,
T.BESCHREIBUNG
FROM BESTELLUNGEN B, TEIL T, KUNDE K
WHERE B.TEILNUM = T.TEILNUM
AND B.NAME = K.NAME
ORDER BY K.NAME

NAME ADRESSE GESAMT BESCHREIBUNG
========== ========== =========== ====================

AAA BIKE 10 OLDTOWN 213.50 REIFEN
AAA BIKE 10 OLDTOWN 2120.00 ROAD BIKE
AAA BIKE 10 OLDTOWN 1200.00 TANDEM
BIKE SPEC CPT SHRIVE 542.50 PEDALE
BIKE SPEC CPT SHRIVE 2803.60 MOUNTAIN BIKE
BIKE SPEC CPT SHRIVE 5830.00 ROAD BIKE
BIKE SPEC CPT SHRIVE 2400.00 TANDEM
JACKS BIKE 24 EGLIN 7420.00 ROAD BIKE
LE SHOPPE HOMETOWN 2650.00 ROAD BIKE
LE SHOPPE HOMETOWN 3600.00 TANDEM
TRUE WHEEL 55O HUSKER 196.00 SATTEL
TRUE WHEEL 55O HUSKER 2102.70 MOUNTAIN BIKE
TRUE WHEEL 55O HUSKER 1590.00 ROAD BIKE
TRUE WHEEL 55O HUSKER 1200.00 TANDEM


Diese Liste ist das Ergebnis von drei verknüpften Tabellen. Mit diesen Angaben läßt sich nun eine Rechnung erstellen.

Im Beispiel am Beginn dieser Lektion hat SQL die TABELLE1 und die TABELLE2 gruppiert, um eine neue Tabelle aus X Zeilen von TABELLE1 mal Y Zeilen von TABELLE2 zu erzeugen. Bei dieser Verknüpfung entsteht keine physikalische, sondern eine virtuelle Tabelle. Die Verknüpfung zwischen den beiden Tabellen produziert eine neue Menge, die allen Bedingungen in der WHERE-Klausel einschließlich der Verknüpfung selbst genügt. Die SELECT-Anweisung hat zwar die Anzahl der angezeigten Zeilen reduziert, zur Auswertung der WHERE-Klausel erzeugt SQL aber trotzdem alle möglichen Zeilen. Die Tabellen in den heute gezeigten Beispielen enthalten nur eine Handvoll Zeilen. In der Praxis hat man es normalerweise mit Tausenden von Zeilen zu tun. Auf einem gut ausgestatteten Computer sind Auswirkungen auf die Ressourcen bei Verknüpfung mehrerer Tabellen kaum festzustellen. Wenn man jedoch in einer langsameren Umgebung arbeitet, können Verknüpfungen einen deutlichen Bremseffekt zeigen.

Es empfiehlt sich nun nicht, einfach ohne Verknüpfungen zu arbeiten, da man damit auf die mehrfach dargestellten Vorteile des relationalen Konzepts verzichtet. Behalten Sie die vorgesehene Plattform und die Anforderungen Ihrer Kunden im Auge, um das bestmögliche an Geschwindigkeit und Zuverlässigkeit aus der Datenbank herauszuholen.


Non-Equi-Joins

Da SQL eine Equi Join unterstützt, könnte man davon ausgehen, daß es auch eine Nicht-Equi-Join gibt. Diese Annahme stimmt genau! Während bei der Equi Join das Gleichheitszeichen in der WHERE-Anweisung steht, verwendet man bei der Nicht-Equi-Join alle anderen Operatoren - außer dem Gleichheitszeichen. Sehen Sie sich folgendes Beispiel an:


SELECT B.NAME, B.TEILNUM, T.TEILNUM,
B.MENGE * T.PREIS GESAMT
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM > T.TEILNUM


NAME TEILNUM TEILNUM GESAMT
========== =========== =========== ===========

TRUE WHEEL 76 54 162.75
BIKE SPEC 76 54 596.75
LE SHOPPE 76 54 271.25
AAA BIKE 76 54 217.00
JACKS BIKE 76 54 759.50
TRUE WHEEL 76 42 73.50
BIKE SPEC 54 42 245.00
BIKE SPEC 76 42 269.50
LE SHOPPE 76 42 122.50
AAA BIKE 76 42 98.00
AAA BIKE 46 42 343.00
JACKS BIKE 76 42 343.00
TRUE WHEEL 76 46 45.75
BIKE SPEC 54 46 152.50
BIKE SPEC 76 46 167.75
LE SHOPPE 76 46 76.25
AAA BIKE 76 46 61.00
JACKS BIKE 76 46 213.50
TRUE WHEEL 76 23 1051.35
TRUE WHEEL 42 23 2803.60
...

Die Liste setzt sich noch weiter fort und beschreibt alle Zeilen in der Verknüpfung entsprechend der Bedingung WHERE B.TEILNUM > T.TEILNUM. Für Ihr Fahrradgeschäft haben diese Angaben nur eine geringe Bedeutung. In der Praxis findet man Equi Joins auch weitaus häufiger als Nicht-Equi-Joins. Trotzdem gibt es Anwendungen, in denen gerade eine Nicht-Equi-Join die perfekte Konstruktion darstellt.


Outer Joins vs. Inner Joins

So wie Nicht-Equi-Joins das Gegenstück zu Equi-Joins bilden, ergänzen Outer Joins die Inner Joins. Eine Inner Join kombiniert die Zeilen einer Tabelle miteinander und liefert eine Anzahl neuer Zeilen, die gleich dem Produkt aus den Zeilenzahlen in jeder Tabelle ist. Die Inner Join greift auf diese Zeilen zurück, um das Ergebnis der WHERE-Klausel zu ermitteln. Eine Outer Join gruppiert die beiden Tabellen in einer etwas anderen Art. Führen Sie mit den Tabellen TEIL und BESTELLUNGEN der vorherigen Beispiele die folgende Inner Join aus:


SELECT T.TEILNUM, T.BESCHREIBUNG, T.PREIS,
B.NAME, B.TEILNUM
FROM TEIL T
JOIN BESTELLUNGEN B ON BESTELLUNGEN.TEILNUM = 54


TEILNUM BESCHREIBUNG PREIS NAME TEILNUM
=========== ==================== =========== ========== ===========
54 PEDALE 54.25 BIKE SPEC 54
42 SATTEL 24.50 BIKE SPEC 54
46 REIFEN 15.25 BIKE SPEC 54
23 MOUNTAIN BIKE 350.45 BIKE SPEC 54
76 ROAD BIKE 530.00 BIKE SPEC 54
10 TANDEM 1200.00 BIKE SPEC 54

Die hier angegebene Syntax für die Verknüpfung - JOIN ON - ist nicht im ANSI-Standard definiert. ISQL von Borland hat an dieser Stelle die Syntax erweitert. Damit erzeugen Sie eine Inner Join und eine Outer Join. Die meisten Implementierungen von SQL verfügen über ähnliche Erweiterungen. Beachten Sie, daß bei derartigen Verknüpfungen keine WHERE-Klausel vorkommt.

Im Ergebnis sind alle Zeilen in TEIL auf Zeilen mit TEILNUM gleich 54 aus der Tabelle BESTELLUNGEN aufgeteilt. Die folgende Anweisung zeigt eine RIGHT OUTER JOIN - eine sogenannte rechte Inklusionsverknüpfung:

SELECT T.TEILNUM, T.BESCHREIBUNG, T.PREIS,
B.NAME, B.TEILNUM
FROM TEIL T
RIGHT OUTER JOIN BESTELLUNGEN B ON BESTELLUNGEN.TEILNUM = 54

TEILNUM BESCHREIBUNG PREIS NAME TEILNUM
=========== ==================== =========== ========== ===========

<null> <null> <null> TRUE WHEEL 23
<null> <null> <null> TRUE WHEEL 76
<null> <null> <null> TRUE WHEEL 10
<null> <null> <null> TRUE WHEEL 42
54 PEDALE 54.25 BIKE SPEC 54
42 SATTEL 24.50 BIKE SPEC 54
46 REIFEN 15.25 BIKE SPEC 54
23 MOUNTAIN BIKE 350.45 BIKE SPEC 54
76 ROAD BIKE 530.00 BIKE SPEC 54
10 TANDEM 1200.00 BIKE SPEC 54
<null> <null> <null> BIKE SPEC 10
<null> <null> <null> BIKE SPEC 23
<null> <null> <null> BIKE SPEC 76
<null> <null> <null> LE SHOPPE 76
<null> <null> <null> LE SHOPPE 10
<null> <null> <null> AAA BIKE 10
<null> <null> <null> AAA BIKE 76
<null> <null> <null> AAA BIKE 46
<null> <null> <null> JACKS BIKE 76

Wir haben es hier mit einem neuen Verknüpfungstyp zu tun. Eine RIGHT OUTER JOIN gibt alle Datensätze aus der zweiten bzw. rechten Tabelle (BESTELLUNGEN) zurück, auch wenn keine entsprechenden Werte für Datensätze in der ersten Tabelle existieren. Hier erscheinen NULL-Werte in den Feldern, für die BESTELLUNGEN.TEILNUM <> 54 gilt. Die folgende Anweisung realisiert eine LEFT OUTER JOIN - eine sogenannte linke Inklusionsverknüpfung:

SELECT T.TEILNUM, T.BESCHREIBUNG, T.PREIS,
B.NAME, B.TEILNUM
FROM TEIL T
LEFT OUTER JOIN BESTELLUNGEN B ON BESTELLUNGEN.TEILNUM = 54

TEILNUM BESCHREIBUNG PREIS NAME TEILNUM
=========== ==================== =========== ========== ===========

54 PEDALE 54.25 BIKE SPEC 54
42 SATTEL 24.50 BIKE SPEC 54
46 REIFEN 15.25 BIKE SPEC 54
23 MOUNTAIN BIKE 350.45 BIKE SPEC 54
76 ROAD BIKE 530.00 BIKE SPEC 54
10 TANDEM 1200.00 BIKE SPEC 54


Die Ergebnismenge enthält die gleichen sechs Zeilen wie die INNER JOIN. Da Sie mit LEFT die linke Tabelle spezifiziert haben, bestimmt TEIL die Anzahl der zurückgegebenen Zeilen. Die Tabelle TEIL ist kleiner als die Tabelle BESTELLUNGEN, so daß SQL keine Felder mit einer NULL-Kennzeichnung auffüllen muß.

Mit Inner Joins und Outer Joins haben Sie es kaum zu tun. Die meisten SQL-Produkte bestimmen die optimale JOIN-Anweisung für die Abfrage automatisch. Auch wenn Ihre SQL-Implementierung über die entsprechenden Syntaxelemente verfügt, sollten Sie keinen Verknüpfungstyp angeben, wenn Sie die Abfrage in einer gespeicherten Prozedur oder in einem Programm verwenden. (Auf gespeicherte Prozeduren und eingebettetes SQL geht Tag 13 ein.) Falls Sie einen Verknüpfungstyp spezifizieren, wählt der Optimierer Ihre Vorgabe und nicht die optimale Verknüpfung.


Verschiedene SQL-Implementierungen kennzeichnen eine OUTER-JOIN-Anweisung mit einem Pluszeichen. Das einfache + bedeutet: Zeige mir alles, auch wenn irgend etwas fehlt. Die entsprechende Syntax sieht folgendermaßen aus:

Die Anweisung verknüpft beide Tabellen. Das Pluszeichen neben der Spalte ml.mitarbeiter_id gibt alle Zeilen zurück, auch wenn diese leer sind.


Eine Tabelle mit sich selbst verknüpfen

Das letzte Thema des heutigen Tages beschäftigt sich mit der häufig verwendeten Verknüpfung einer Tabelle mit sich selbst. Die Syntax dieser Operation ist der Verknüpfung von zwei Tabellen ähnlich. Beispielsweise verknüpft die folgende Anweisung die TABELLE1 zu sich selbst:


SELECT *
FROM TABELLE1, TABELLE1


ZEILE BEMERKUNGEN ZEILE BEMERKUNGEN
========== ============ ========== ============

Zeile 1 Tabelle 1 Zeile 1 Tabelle 1
Zeile 1 Tabelle 1 Zeile 2 Tabelle 1
Zeile 1 Tabelle 1 Zeile 3 Tabelle 1
Zeile 1 Tabelle 1 Zeile 4 Tabelle 1
Zeile 1 Tabelle 1 Zeile 5 Tabelle 1
Zeile 1 Tabelle 1 Zeile 6 Tabelle 1
Zeile 2 Tabelle 1 Zeile 1 Tabelle 1
Zeile 2 Tabelle 1 Zeile 2 Tabelle 1
Zeile 2 Tabelle 1 Zeile 3 Tabelle 1
Zeile 2 Tabelle 1 Zeile 4 Tabelle 1
Zeile 2 Tabelle 1 Zeile 5 Tabelle 1
Zeile 2 Tabelle 1 Zeile 6 Tabelle 1
Zeile 3 Tabelle 1 Zeile 1 Tabelle 1
Zeile 3 Tabelle 1 Zeile 2 Tabelle 1
Zeile 3 Tabelle 1 Zeile 3 Tabelle 1
Zeile 3 Tabelle 1 Zeile 4 Tabelle 1
Zeile 3 Tabelle 1 Zeile 5 Tabelle 1
Zeile 3 Tabelle 1 Zeile 6 Tabelle 1
Zeile 4 Tabelle 1 Zeile 1 Tabelle 1
Zeile 4 Tabelle 1 Zeile 2 Tabelle 1
...

Die Liste ist nur zum Teil dargestellt. Diese Verknüpfung produziert die gleiche Anzahl von Kombinationen wie die Verknüpfung zweier 6zeiliger Tabellen. Mit einer derartigen Verknüpfung läßt sich die interne Konsistenz der Daten überprüfen. Was würde passieren, wenn jemand in der Produktionsabteilung eine Teilnummer eingibt, die bereits in der Spalte TEILNUM existiert? Dieser Fehler hätte weitreichende Auswirkungen: Rechnungen werden falsch ausgestellt, die Anwendung kann abstürzen, und Sie hätten eine Weile mit der Fehlersuche zu tun. Die Ursache für alle Ihre Probleme wäre die doppelte Teilnummer in der folgenden Tabelle:

SELECT * FROM TEIL

TEILNUM BESCHREIBUNG PREIS
=========== ==================== ===========

54 PEDALE 54.25
42 SATTEL 24.50
46 REIFEN 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
76 SCHALTUNG 65.00 <- doppelte TEILNUM


Ihre Firma können Sie vor der Katastrophe bewahren, indem Sie die Tabelle TEIL überprüfen, bevor sie irgend jemand verwendet:


SELECT A.TEILNUM, A.BESCHREIBUNG,
B.TEILNUM, B.BESCHREIBUNG
FROM TEIL A, TEIL B
WHERE A.TEILNUM = B.TEILNUM
AND A.BESCHREIBUNG <> B.BESCHREIBUNG

TEILNUM BESCHREIBUNG TEILNUM BESCHREIBUNG
=========== ==================== =========== ====================

76 ROAD BIKE 76 SCHALTUNG
76 SCHALTUNG 76 ROAD BIKE


Sie gelten nun als Experte, bis Sie jemand fragt, warum die Tabelle nur zwei Einträge hat. Mit all Ihren Kenntnissen über JOINs halten Sie Ihren Expertenstatus aufrecht und erklären, wie die Verknüpfung zwei Zeilen erzeugt hat, die die Bedingung WHERE A.TEILNUM = B.TEILNUM AND A.BESCHREIBUNG <> B.BESCHREIBUNG erfüllen. Natürlich ist noch die Datenzeile mit der doppelten Teilnummer zu korrigieren.


Zusammenfassung

Heute haben Sie gelernt, daß eine Verknüpfung alle möglichen Kombinationen der in den ausgewählten Tabellen vorhandenen Zeilen bildet. Diese neuen Zeilen stehen dann zur Auswahl bereit, um die gewünschten Informationen abzurufen.


Über die SELECT-Anweisung sind Sie nun umfassend informiert. Es fehlen nur noch die Unterabfragen, die der morgige Tag behandelt.



Fragen und Antworten

Frage:

Warum wurden die Outer, Inner, Left und Right Joins behandelt, wenn ich diese Verknüpfungen wahrscheinlich sowieso nicht brauche?

Antwort:

Geringe Kenntnisse sind gefährlich, gar nichts zu wissen, kann teuer werden. Damit Sie die Abläufe in der SQL-Engine bei der Optimierung Ihrer Abfragen verstehen können, wurden hier zumindest die Grundlagen vermittelt.

Frage:

Wie viele Tabellen kann man in eine Verknüpfung einbeziehen?

Antwort:

Das hängt von der jeweiligen Implementierung ab. Bei einigen Implementierungen gibt es eine Beschränkung auf 25 Tabellen, während andere Datenbanken keine Grenzen setzen. Denken Sie aber immer daran, daß die Reaktionszeit der Datenbank mit zunehmender Zahl verknüpfter Tabellen steigt. Um sicherzugehen sollten Sie sich in der Dokumentation über die maximale Zahl der in einer Abfrage zu verknüpfenden Tabellen informieren.

Frage:

Kann man sagen, daß verknüpfte Tabellen praktisch zu einer einzigen Tabelle verschmelzen?

Antwort:

Stark vereinfacht ist es genau das, was passiert. Wenn man die Tabellen verknüpft, kann man aus allen Spalten in allen Tabellen auswählen.


Workshop


Kontrollfragen

1. Wie viele Zeilen produziert eine Verknüpfung mit zwei Tabellen, wenn eine Tabelle 50.000 Zeilen und die andere Tabelle 100.000 Zeilen enthält?


2. Welcher Verknüpfungstyp ist in der folgenden SELECT-Anweisung realisiert?


select m.name, m.mitarbeiter_id, ml.gehalt
from mitarbeiter_tbl m,
mitarbeiter_lohn_tbl ml
where m.mitarbeiter_id = ml.mitarbeiter_id;


3. Funktionieren die folgenden SELECT-Anweisungen?


a. select name, mitarbeiter_id, gehalt
from mitarbeiter_tbl m,
mitarbeiter_lohn_tbl ml
where mitarbeiter_id = mitarbeiter_id
and name like '%MITH';

b. select m.name, m.mitarbeiter_id, ml.gehalt
from mitarbeiter_tbl m,
mitarbeiter_lohn_tbl ml
where name like '%MITH';
c. select m.name, m.mitarbeiter_id, ml.gehalt
from mitarbeiter_tbl m,
mitarbeiter_lohn_tbl ml
where m.mitarbeiter_id = ml.mitarbeiter_id
and m.name like '%MITH';


4. Gibt man bei der Verknüpfung von Tabellen zuerst die Verknüpfung oder die Bedingungen in der WHERE-Klausel an?


5. Ist man bei der Verknüpfung von Tabellen auf einspaltige Verknüpfungen eingeschränkt, oder lassen sich auch mehrere Spalten verknüpfen?



Übungen

1. Im Abschnitt zur Verknüpfung von Tabellen mit sich selbst hat das letzte Beispiel zwei Kombinationen zurückgegeben. Formulieren Sie die Abfrage neu, so daß nur noch ein Eintrag für die redundanten Teilnummern erscheint.


2. Schreiben Sie die folgende Abfrage neu, damit sie sich besser lesen läßt und kürzer wird.


select bestellungen.bestdatum, bestellungen.name, teil.teilnum,
teil.preis, teil.beschreibung from bestellungen, teil
where bestellungen.teilnum = teil.teilnum and
bestellungen.bestdatum
between '01.09.97' and '30.09.97'
order by teil.teilnum;


3. Bilden Sie bezüglich der Tabellen TEIL und BESTELLUNGEN eine Abfrage, die folgendes zurückgibt:


BESTDATUM NAME TEILNUM MENGE
=========== ========== =========== ===========

2-SEP-1997 TRUE WHEEL 10 1


Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3

vorheriges KapitelTop Of PageInhaltsverzeichnisIndexInfoseitenächstes Kapitel