vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel



7


Unterabfragen: Die eingebettete SELECT-Anweisung

Ziele

Eine Unterabfrage aufbauen

Aggregatfunktionen bei Unterabfragen

Verschachtelte Unterabfragen

Korrelierte Unterabfragen

Arbeiten mit EXISTS, ANY und ALL

Zusammenfassung

Fragen und Antworten

Workshop



Ziele

Eine Unterabfrage ist eine Abfrage, deren Ergebnisse als Argumente an eine andere Abfrage übergeben werden. Mit Unterabfragen lassen sich mehrere Abfragen verbinden. Am Ende des heutigen Tages sind Sie in der Lage, ...


Die Beispiele für die heutige Lektion übernehmen die Tabellen von Tag 6 und arbeiten ebenfalls mit ISQL von Borland. Wie Sie bereits wissen, zeigt diese Implementierung weder ein SQL-Prompt (SQL>) noch Zeilennummern an.


Eine Unterabfrage aufbauen

Vereinfacht ausgedrückt verbindet eine Unterabfrage die Ergebnismenge der einen Abfrage mit einer anderen. Die allgemeine Syntax lautet:


Da die zweite Abfrage in der ersten eingebaut ist, spricht man auch von einer verschachtelten Abfrage. Das folgende Beispiel verwendet die bereits aus der vorherigen Lektion bekannten Tabellen TEIL und BESTELLUNGEN:


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


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


Beiden Tabellen ist das Feld TEILNUM gemeinsam. Nehmen wir an, daß Sie die Teilnummer nicht kennen (oder nicht wissen wollen), sondern mit der Beschreibung des jeweiligen Teils arbeiten möchten. Mit einer Unterabfrage läßt sich das folgendermaßen realisieren:

SELECT *
FROM BESTELLUNGEN
WHERE TEILNUM =
(SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG LIKE 'ROAD%')

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

19-MAY-1997 TRUE WHEEL 76 3 BEZAHLT
17-JAN-1997 BIKE SPEC 76 11 BEZAHLT
17-JAN-1997 LE SHOPPE 76 5 BEZAHLT
1-JUL-1997 AAA BIKE 76 4 BEZAHLT
11-JUL-1997 JACKS BIKE 76 14 BEZAHLT


Mit den in Lektion 6 eingeführten Konzepten können Sie die Spalte TEILNUM in der Ergebnismenge sogar noch durch die Spalte BESCHREIBUNG ergänzen und die Ausgabe verständlicher gestalten:


SELECT B.BESTDATUM, B.TEILNUM,
T.BESCHREIBUNG, B.MENGE, B.BEMERKUNGEN
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.TEILNUM =
(SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG LIKE 'ROAD%')

BESTDATUM TEILNUM BESCHREIBUNG MENGE BEMERKUNGEN
=========== =========== ==================== =========== ==================

19-MAY-1997 76 ROAD BIKE 3 BEZAHLT
1-JUL-1997 76 ROAD BIKE 4 BEZAHLT
17-JAN-1997 76 ROAD BIKE 5 BEZAHLT
17-JAN-1997 76 ROAD BIKE 11 BEZAHLT
11-JUL-1997 76 ROAD BIKE 14 BEZAHLT


Der erste Teil der Abfrage ist Ihnen bereits vertraut:

SELECT B.BESTDATUM, B.TEILNUM,
T.BESCHREIBUNG, B.MENGE, B.BEMERKUNGEN
FROM BESTELLUNGEN B, TEIL T


Diese Anweisung wählt mit Hilfe der Aliasnamen B für BESTELLUNGEN und T für TEIL die fünf interessierenden Spalten aus. In diesem Beispiel sind die Aliasnamen eigentlich nicht erforderlich, da die zurückzugebenden Spalten eindeutig sind. Allerdings empfiehlt es sich, die Lesbarkeit einer Anweisung bereits von vornherein sicherzustellen. Die erste WHERE-Klausel


WHERE B.TEILNUM = T.TEILNUM


weist keine Besonderheiten auf und verknüpft die in der FROM-Klausel angegebenen Tabellen TEIL und BESTELLUNGEN. Ohne diese WHERE-Klausel erhält man alle möglichen Zeilenkombinationen aus beiden Tabellen zurück. Der nächste Teil der Anweisung bindet die Unterabfrage ein. Die Anweisung


AND
B.TEILNUM =
(SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG LIKE 'ROAD%')


schränkt die Ergebnismenge zusätzlich mit der Bedingung ein, daß B.TEILNUM gleich dem Ergebnis der Unterabfrage sein muß. Die unkomplizierte Unterabfrage ermittelt alle Teilnummern, die dem Muster 'ROAD%' entsprechen. Der Operator LIKE unterstützt hier lediglich die Tippfaulheit - man erspart sich die Eingabe der vollständigen Beschreibung »ROAD BIKE«. Allerdings lauert hier eine Falle. Was passiert, wenn ein neues Teil namens ROADKILL hinzukommt? Die neue Tabelle TEIL sieht dann folgendermaßen aus:


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
77 ROADKILL 7.99


Nehmen wir an, daß Sie von dieser Änderung nichts wissen und nach der Aufnahme des neuen Produkts die vorherige Abfrage


SELECT B.BESTDATUM, B.TEILNUM,
T.BESCHREIBUNG, B.MENGE, B.BEMERKUNGEN
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.TEILNUM =
(SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG LIKE 'ROAD%')


ausführen. Die SQL-Engine erzeugt dann die Fehlermeldung:


Statement failed, SQLCODE = -811
multiple rows in singleton select


In Personal Oracle8 lautet die entsprechende Fehlermeldung:


ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile


Die Fehlermeldungen können sich zwar in den einzelnen Implementierungen unterscheiden. Das ändert aber nichts an der Tatsache, daß die SQL-Engine einen Fehler bemängelt und Sie vergeblich auf die Ergebnisse warten.


Um diesem Fehler auf die Spur zu kommen, versetzen Sie sich wieder in die Rolle von SQL. Die Unterabfrage werden Sie wahrscheinlich zuerst auswerten und folgendes Ergebnis zurückgeben:


SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG LIKE 'ROAD%'

TEILNUM
===========

76
77


Dieses Ergebnis wenden Sie nun auf B.TEILNUM = an. Genau hier liegt das Problem.


TEILNUM kann bekanntlich nicht sowohl gleich 76 als auch gleich 77 sein. Mit der Formulierung der LIKE-Klausel haben Sie also den Grundstein für den Fehler gelegt. Wenn man die Ergebnisse eines relationalen Operators mit einem anderen relationalen Operator wie =, < oder > kombiniert, muß man sicherstellen, daß letztlich nur ein einziges Ergebnis entsteht. Im obigen Beispiel läßt sich das Problem beseitigen, indem man die Abfrage neu formuliert und statt mit dem Operator LIKE mit dem Gleichheitszeichen arbeitet:

SELECT B.BESTDATUM, B.TEILNUM,
T.BESCHREIBUNG, B.MENGE, B.BEMERKUNGEN
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.TEILNUM =
(SELECT TEILNUM
FROM TEIL
WHERE BESCHREIBUNG = 'ROAD BIKE')

BESTDATUM TEILNUM BESCHREIBUNG MENGE BEMERKUNGEN
=========== =========== ==================== =========== ==================

19-MAY-1997 76 ROAD BIKE 3 BEZAHLT
1-JUL-1997 76 ROAD BIKE 4 BEZAHLT
17-JAN-1997 76 ROAD BIKE 5 BEZAHLT
17-JAN-1997 76 ROAD BIKE 11 BEZAHLT
11-JUL-1997 76 ROAD BIKE 14 BEZAHLT


Diese Unterabfrage gibt jetzt ein eindeutiges Ergebnis zurück. Die Bedingung wird demzufolge mit dem Operator = auf einen einzelnen Wert eingeengt. Wie kann man aber sicher sein, daß die Unterabfrage nicht mehrere Werte zurückgibt, wenn man nur nach einem einzelnen Wert sucht?

Das Formulieren der Bedingung ohne den Operator LIKE ist bereits ein Anfang. Die Eindeutigkeit des Suchfeldes läßt sich aber bereits beim Tabellenentwurf sicherstellen. Wenn Sie ein mißtrauischer Typ sind, können Sie auch eine Tabelle zu sich selbst verknüpfen (wie es die gestrige Lektion erläutert hat), um ein bestimmtes Feld auf Eindeutigkeit zu prüfen. Falls Sie die Tabelle (nach den in Lektion 9 beschriebenen Methoden) selbst erstellen, legen Sie eindeutige Werte als Einschränkung für die Suchspalte fest. Schließlich besteht die Möglichkeit, auf die Aggregatfunktionen von SQL zurückzugreifen, da diese Funktionen nur einen Wert zurückgeben.



Aggregatfunktionen bei Unterabfragen

Die Aggregatfunktionen SUM, COUNT, MIN, MAX und AVG geben jeweils einen einzelnen Wert zurück. Der durchschnittliche Betrag einer Bestellung läßt sich mit der folgenden Anweisung ermitteln:


SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM


AVG
===========

2419.16


Diese Anweisung gibt nur einen einzigen Wert zurück. Um zu ermitteln, welche Bestellungen über dem Durchschnitt liegen, verwenden Sie die obige SELECT-Anweisung für die Unterabfrage. Die vollständige Abfrage sieht dann folgendermaßen aus:

SELECT B.NAME, B.BESTDATUM,
B.MENGE * T.PREIS GESAMT
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.MENGE * T.PREIS >
(SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM)

NAME BESTDATUM GESAMT
========== =========== ===========

LE SHOPPE 1-JUN-1997 3600.00
BIKE SPEC 30-MAY-1997 2803.60
LE SHOPPE 17-JAN-1997 2650.00
BIKE SPEC 17-JAN-1997 5830.00
JACKS BIKE 11-JUL-1997 7420.00


Die SELECT/FROM/WHERE-Klausel des Beispiels weist keine Besonderheiten auf:

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


Diese Zeilen stellen die normale Methode zur Verbindung zweier Tabellen dar. Die Verknüpfung ist erforderlich, da der Preis in der Tabelle TEIL und die Menge in der Tabelle BESTELLUNGEN enthalten ist. Die WHERE-Klausel stellt sicher, daß nur die aus der Verknüpfung resultierenden und miteinander in Beziehung stehenden Zeilen untersucht werden. Schließlich kommt noch die Unterabfrage hinzu:


AND
B.MENGE * T.PREIS >
(SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM)


Die obige Bedingung vergleicht die Gesamtsumme jeder Bestellung mit dem in der Unterabfrage berechneten Mittelwert. Die Verknüpfung in der Unterabfrage ist aus den gleichen Gründen wie in der übergeordneten SELECT-Anweisung erforderlich. Der Aufbau der Verknüpfung ist ebenfalls identisch. Unterabfragen weisen nichts Mystisches auf, sondern verhalten sich genauso wie eigenständige Abfragen. Häufig erstellt man Unterabfragen zunächst als selbständige Abfragen und baut sie erst als Unterabfrage ein, wenn die Ergebnisse getestet sind.



Verschachtelte Unterabfragen

Beim Verschachteln einer Unterabfrage bettet man eine Unterabfrage in einer anderen Unterabfrage ein. Die folgende Zeile zeigt den prinzipiellen Aufbau einer verschachtelten Unterabfrage:


SELECT * FROM Irgendwas WHERE (Unterabfrage(Unterabfrage(Unterabfrage)));


Die Verschachtelungstiefe von Unterabfragen ist von der jeweiligen SQL-Implementierung abhängig. Wollen Sie zum Beispiel allen Kunden, die überdurchschnittlich viel bestellt haben, eine spezielle Mitteilung schicken, kombinieren Sie die Informationen aus der Tabelle KUNDE ...


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


mit einer leicht modifizierten Version der Abfrage, mit der Sie im letzten Beispiel die über dem Durchschnitt liegenden Bestellungen ausgegeben haben:


SELECT ALL K.NAME, K.ADRESSE, K.STAAT, K.ZIP
FROM KUNDE K
WHERE K.NAME IN
(SELECT B.NAME
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.MENGE * T.PREIS >
(SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM))

NAME ADRESSE STAAT ZIP
========== ========== ====== ======

BIKE SPEC CPT SHRIVE LA 45678
LE SHOPPE HOMETOWN KS 54678
JACKS BIKE 24 EGLIN FL 34567


Sehen wir uns die Abfrage im einzelnen an. Im innersten Klammernpaar steht eine bekannte Anweisung:

SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM


Das Ergebnis geht in eine leicht modifizierte Version der bereits weiter oben verwendeten SELECT-Anweisung ein:


SELECT B.NAME
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.MENGE * T.PREIS >
(...)


Die SELECT-Anweisung wurde modifiziert und gibt nur noch die Spalte NAME zurück, die - nicht ganz zufällig - den Tabellen BESTELLUNGEN und KUNDE gemeinsam ist. Führt man diesen Teil der Anweisung für sich aus, erhält man folgendes Zwischenergebnis:


SELECT B.NAME
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM
AND
B.MENGE * T.PREIS >
(SELECT AVG(B.MENGE * T.PREIS)
FROM BESTELLUNGEN B, TEIL T
WHERE B.TEILNUM = T.TEILNUM)

NAME
==========

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE


Gerade noch haben wir erklärt, warum Unterabfragen genau einen Wert zurückgeben sollten. In Kürze werden Sie verstehen, warum diese Abfrage mehrere Werte zurückgeben kann.

Die obigen Ergebnisse gehen in die folgende Anweisung ein:


SELECT ALL K.NAME, K.ADRESSE, K.STAAT, K.ZIP
FROM KUNDE K
WHERE K.NAME IN
(...)


Die beiden ersten Zeilen weisen keine spektakulären Besonderheiten auf. In der dritten Zeile erscheint wieder das Schlüsselwort IN, das wir zuletzt in Lektion 2 behandelt haben. Dieses IN ist das Werkzeug, mit dem sich die mehrzeilige Ausgabe einer Unterabfrage verarbeiten läßt. Wie Sie wissen, sucht IN nach Übereinstimmungen in der Menge der Werte, die in den darauffolgenden Klammern eingeschlossen sind. Im Beispiel handelt es sich dabei um die Werte:

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE


Diese Unterabfrage liefert die Bedingungen, aus denen sich die Postliste ergibt:


NAME ADRESSE STAAT ZIP
========== ========== ====== ======

BIKE SPEC CPT SHRIVE LA 45678
LE SHOPPE HOMETOWN KS 54678
JACKS BIKE 24 EGLIN FL 34567


Bei Unterabfragen setzt man den Operator IN häufig ein. Da IN den Vergleich mit einem Satz von Werten ausführt, kommt es in der SQL-Engine nicht zu den weiter oben erwähnten Konflikten.


Unterabfragen lassen sich auch bei den Klauseln GROUP BY und HAVING verwenden. Studieren Sie die folgende Abfrage:


SELECT NAME, AVG(MENGE)
FROM BESTELLUNGEN
GROUP BY NAME
HAVING AVG(MENGE) >
(SELECT AVG(MENGE)
FROM BESTELLUNGEN)

NAME AVG
========== ===========

BIKE SPEC 8
JACKS BIKE 14


Untersuchen wir diese Abfrage in der Reihenfolge, wie die SQL-Engine vorgeht. Zuerst werfen wir einen Blick auf die Unterabfrage:

SELECT AVG(MENGE)
FROM BESTELLUNGEN

AVG
===========

6


Die Abfrage an sich lautet:


SELECT NAME, AVG(MENGE)
FROM BESTELLUNGEN
GROUP BY NAME

NAME AVG
========== ===========

AAA BIKE 6
BIKE SPEC 8
JACKS BIKE 14
LE SHOPPE 4
TRUE WHEEL 5


Bei der Kombination über die HAVING-Klausel produziert die Unterabfrage zwei Zeilen, die die überdurchschnittliche MENGE enthalten.


SELECT NAME, AVG(MENGE)
FROM BESTELLUNGEN
GROUP BY NAME
HAVING AVG(MENGE) >
(SELECT AVG(MENGE)
FROM BESTELLUNGEN)

NAME AVG
========== ===========

BIKE SPEC 8
JACKS BIKE 14



Korrelierte Unterabfragen

Die bisher dargestellten Unterabfragen sind eigenständig, da keine Referenzen zu äußeren Elementen bestehen. Mit korrelierten Unterabfragen kann man eine äußere Referenz verwenden, wobei zum Teil seltsame Ergebnisse entstehen. Sehen Sie sich die folgende Abfrage an:


SELECT *
FROM BESTELLUNGEN B
WHERE 'ROAD BIKE' =
(SELECT BESCHREIBUNG
FROM TEIL T
WHERE T.TEILNUM = B.TEILNUM)


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

19-MAY-1997 TRUE WHEEL 76 3 BEZAHLT
17-JAN-1997 BIKE SPEC 76 11 BEZAHLT
17-JAN-1997 LE SHOPPE 76 5 BEZAHLT
1-JUL-1997 AAA BIKE 76 4 BEZAHLT
11-JUL-1997 JACKS BIKE 76 14 BEZAHLT


Diese Abfrage ähnelt der folgenden Verknüpfung:


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

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

19-MAY-1997 TRUE WHEEL 76 3 BEZAHLT
1-JUL-1997 AAA BIKE 76 4 BEZAHLT
17-JAN-1997 LE SHOPPE 76 5 BEZAHLT
17-JAN-1997 BIKE SPEC 76 11 BEZAHLT
11-JUL-1997 JACKS BIKE 76 14 BEZAHLT


Mit Ausnahme der Reihenfolge sind die Ergebnisse tatsächlich identisch. Die korrelierte Unterabfrage verhält sich fast wie eine Verknüpfung. Die Wechselbeziehung zur Unterabfrage wird mit einem Element aus der Abfrage eingerichtet. In diesem Beispiel handelt es sich dabei um die Anweisung:

WHERE T.TEILNUM = B.TEILNUM


Diese Anweisung vergleicht T.TEILNUM aus der Tabelle innerhalb der Unterabfrage mit B.TEILNUM aus der Tabelle in der - übergeordneten - Abfrage. Da B.TEILNUM in jeder Zeile einen anderen Wert aufweisen kann, wird die korrelierte Unterabfrage für alle Zeilen der Abfrage ausgeführt. Das nächste Beispiel vergleicht alle Zeilen der 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


mit den Kriterien der Unterabfrage:


SELECT BESCHREIBUNG
FROM TEIL T
WHERE T.TEILNUM = B.TEILNUM


Diese Operation liefert die BESCHREIBUNG aller Zeilen in TEIL zurück, wenn die Bedingung T.TEILNUM = B.TEILNUM erfüllt ist. Die WHERE-Klausel vergleicht dann diese Beschreibungen:

WHERE 'ROAD BIKE' =


Die Unterabfrage in einer korrelierten Unterabfrage kann mehrere Werte liefern, da alle Zeilen untersucht werden. Allerdings sollte man nicht versuchen, mehrere Spalten oder Spalten, die im Kontext der WHERE-Klausel nicht sinnvoll sind, zurückzugeben. Die Rückgabewerte müssen noch zu der in der WHERE-Klausel spezifizierten Operation passen. In der gerade ausgeführten Abfrage liefert die Rückgabe von PREIS zum Vergleich mit ROAD BIKE das folgende Ergebnis:


SELECT *
FROM BESTELLUNGEN B
WHERE 'ROAD BIKE' =
(SELECT PREIS
FROM TEIL T
WHERE T.TEILNUM = B.TEILNUM)

Statement failed, SQLCODE = -413
conversion error from string "ROAD BIKE"


Die Meldung weist auf einen Fehler bei der Umwandlung des Strings ROAD BIKE hin.


Wenn Sie das folgende Beispiel ausprobieren möchten, sollten Sie sicherheitshalber alle anderen Anwendungen schließen:


SELECT *
FROM BESTELLUNGEN B
WHERE 'ROAD BIKE' =
(SELECT *
FROM TEIL T
WHERE T.TEILNUM = B.TEILNUM)


Die Ausführung dieser SELECT-Anweisung bringt das berüchtigte Dialogfeld mit der Meldung »Diese Anwendung wird aufgrund eines ungültigen Vorgangs geschlossen« auf den Bildschirm. Man gelangt zwar wieder zu ISQL zurück und kann die Fehlermeldung

Statement failed, SQLCODE = -901
correlation lost to database


einsehen, der Computer scheint aber nahezu stillzustehen. Die SQL-Engine kann einfach nicht alle Spalten in der Tabelle TEIL mit dem Operator = korrelieren.


Korrelierte Unterabfragen lassen sich auch in den Klauseln GROUP BY und HAVING verwenden. Die folgende Abfrage sucht über eine korrelierte Unterabfrage die durchschnittliche Gesamtsumme der Bestellungen für ein bestimmtes Teil heraus und übernimmt dann diesen Mittelwert, um die Gesamtbestellung gruppiert nach TEILNUM zu filtern:


SELECT B.TEILNUM, SUM(B.MENGE*T.PREIS), COUNT(TEILNUM)
FROM BESTELLUNGEN B, TEIL T
WHERE T.TEILNUM = B.TEILNUM
GROUP BY B.TEILNUM
HAVING SUM(B.MENGE*T.PREIS) >
(SELECT AVG(B1.MENGE*T1.PREIS)
FROM TEIL T1, BESTELLUNGEN B1
WHERE T1.TEILNUM = B1.TEILNUM
AND T1.TEILNUM = B.TEILNUM)

TEILNUM SUM COUNT
=========== =========== ===========

10 8400.00 4
23 4906.30 2
76 19610.00 5


Die Unterabfrage berechnet nicht einfach einen Mittelwert:

AVG(B1.MENGE*T1.PREIS)


Da eine Korrelation zwischen der Abfrage und der Unterabfrage mit


AND T1.TEILNUM = B.TEILNUM


besteht, wird dieser Mittelwert für alle Teilgruppen berechnet und dann verglichen:


HAVING SUM(B.MENGE*T.PREIS) >


Wenn man Unterabfragen mit GROUP BY und HAVING verwendet, müssen die Spalten in der HAVING-Klausel entweder in der SELECT-Klausel oder der GROUP-BY-Klausel vorkommen. Andernfalls erhält man eine Fehlermeldung wie invalid column reference (ungültiger Spaltenbezug), da die Unterabfrage für jede Gruppe und nicht für jede Zeile aufgerufen wird. Mit einem Element, das bei der Gruppenbildung nicht beteiligt ist, kann man keinen gültigen Vergleich ausführen.


Arbeiten mit EXISTS, ANY und ALL

Für den gelegentlichen Benutzer sind die Schlüsselwörter EXISTS, ANY und ALL nicht ohne weiteres verständlich. EXISTS übernimmt eine Unterabfrage als Argument. Der Rückgabewert ist TRUE, wenn die Unterabfrage beliebige Werte liefert, und FALSE, wenn die Ergebnismenge leer ist. Das folgende Beispiel zeigt den Einsatz von EXISTS:


SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE EXISTS
(SELECT *
FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997
BIKE SPEC 30-JUN-1997
BIKE SPEC 30-MAY-1997
BIKE SPEC 30-MAY-1997
BIKE SPEC 17-JAN-1997
LE SHOPPE 17-JAN-1997
LE SHOPPE 1-JUN-1997
AAA BIKE 1-JUN-1997
AAA BIKE 1-JUL-1997
AAA BIKE 1-JUL-1997
JACKS BIKE 11-JUL-1997


Das Ergebnis entspricht vielleicht nicht Ihren Erwartungen. Die Unterabfrage innerhalb von EXISTS wird in diesem unkorrelierten Beispiel nur einmal ausgewertet. Da die Ergebnismenge der Unterabfrage aus mindestens einer Zeile besteht, ergibt EXISTS den Wert TRUE. Damit werden alle Zeilen in der Abfrage ausgegeben. Ändert man die Unterabfrage wie im nachstehenden Beispiel, erhält man gar keine Ergebnisse zurück:

SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE EXISTS
(SELECT *
FROM BESTELLUNGEN
WHERE NAME = 'RACING BIKE')


keine


EXISTS liefert den Wert FALSE. Die Unterabfrage erzeugt keine Ergebnismenge, da RACING BIKE nicht zu den vorhandenen Namen gehört.

Beachten Sie die Anweisung SELECT * in der Unterabfrage innerhalb von EXISTS. Der Vergleichsoperator EXISTS kümmert sich nicht darum, wie viele Spalten zurückgegeben werden.

Mit dem Operator EXISTS können Sie auf diese Weise die Existenz bestimmter Zeilen prüfen und die Ausgabe der Abfrage davon abhängig machen, ob diese Zeilen vorhanden sind.


Wenn Sie EXISTS in einer korrelierten Unterabfrage einsetzen, findet die Auswertung für jeden Fall statt, der sich aus der festgelegten Wechselbeziehung ergibt. Dazu ein Beispiel:


SELECT NAME, BESTDATUM
FROM BESTELLUNGEN B
WHERE EXISTS
(SELECT *
FROM KUNDE K
WHERE STAAT = 'NE'
AND K.NAME = B.NAME)

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997
AAA BIKE 1-JUN-1997
AAA BIKE 1-JUL-1997
AAA BIKE 1-JUL-1997


Diese leichte Modifikation der ersten, unkorrelierten Abfrage liefert alle Fahrradgeschäfte aus Nebraska zurück, für die Bestellungen gespeichert sind. Die Unterabfrage

(SELECT *
FROM KUNDE K
WHERE STAAT = 'NE'
AND K.NAME = B.NAME)


wird für alle Zeilen der Abfrage ausgeführt, die über die Spalte NAME der Tabellen KUNDE und BESTELLUNGEN korreliert sind. Der Vergleichsoperator EXISTS liefert für diejenigen Zeilen das Ergebnis TRUE, die über entsprechende Namen in der Tabelle KUNDE verfügen und als Staat NE enthalten.


Eng verwandt mit dem Vergleichsoperator EXISTS sind die Schlüsselwörter ANY, ALL und SOME. Funktionell sind ANY und SOME gleichwertig. Ein Optimist würde sagen, daß der Benutzer dadurch eine Wahl hat, während ein Pessimist diesen Zustand als zusätzliche Komplikation ansieht. Sehen Sie sich die folgende Abfrage an:


SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE NAME = ANY
(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997


Der Operator ANY vergleicht die Ausgabe der Unterabfrage

(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL')


mit allen Zeilen in der Abfrage und liefert TRUE für alle Zeilen der Abfrage zurück, die ein Ergebnis aus der Unterabfrage erhalten.


Ersetzt man ANY durch SOME, erhält man ein identisches Ergebnis:


SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE NAME = SOME
(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997


Sicherlich haben Sie die Ähnlichkeit zum Operator IN festgestellt. Die gleiche Abfrage mit dem Operator IN sieht folgendermaßen aus:

SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE NAME IN
(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997


Wie Sie sich überzeugen können, liefert IN die gleichen Ergebnisse wie ANY und SOME. Wozu dann die verschiedenen Schlüsselwörter? Sehen Sie sich die folgende Abfrage an:

SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE NAME > ANY
(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'JACKS BIKE')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997
LE SHOPPE 17-JAN-1997
LE SHOPPE 1-JUN-1997


Läßt sich eine derartige Abfrage mit IN realisieren? Die Antwort lautet: Nein. Der Operator IN arbeitet etwa wie mehrere Gleichheitszeichen. ANY und SOME lassen sich mit anderen relationalen Operatoren verwenden, beispielsweise mit Größer als oder Kleiner als. Dieses Werkzeug sollten Sie in Ihren Werkzeugkasten aufnehmen.


Der Operator ALL liefert nur dann TRUE, wenn die Ergebnisse einer Unterabfrage die angegebene Bedingung erfüllen. Sonderbarerweise verwendet man ALL am häufigsten in einer doppelten Verneinung wie in der folgenden Abfrage:


SELECT NAME, BESTDATUM
FROM BESTELLUNGEN
WHERE NAME <> ALL
(SELECT NAME
FROM BESTELLUNGEN
WHERE NAME = 'JACKS BIKE')

NAME BESTDATUM
========== ===========

TRUE WHEEL 15-MAY-1997
TRUE WHEEL 19-MAY-1997
TRUE WHEEL 2-SEP-1997
TRUE WHEEL 30-JUN-1997
BIKE SPEC 30-JUN-1997
BIKE SPEC 30-MAY-1997
BIKE SPEC 30-MAY-1997
BIKE SPEC 17-JAN-1997
LE SHOPPE 17-JAN-1997
LE SHOPPE 1-JUN-1997
AAA BIKE 1-JUN-1997
AAA BIKE 1-JUL-1997
AAA BIKE 1-JUL-1997


Diese Abfrage liefert alles außer JACKS BIKE zurück. Der Ausdruck <>ALL ergibt nur dann TRUE, wenn in der Ergebnismenge nicht das links von <> spezifizierte Element enthalten ist.


Zusammenfassung

In zahlreichen Übungen mit Unterabfragen haben Sie heute einen der wichtigsten Bestandteile von SQL kennengelernt. Außerdem sind Sie mit einem der schwierigsten Aspekte von SQL konfrontiert worden: der korrelierten Unterabfrage. Die korrelierte Unterabfrage erzeugt eine Beziehung zwischen der Abfrage und der Unterabfrage, die für jede Instanz dieser Beziehung ausgewertet wird. Lassen Sie sich von der Länge der Abfragen nicht abschrecken. Untersuchen Sie die Anweisungen in Ruhe, indem Sie die Unterabfragen einzeln ausführen.



Fragen und Antworten

Frage:

F Manchmal bietet SQL verschiedene Möglichkeiten, um zum gleichen Ergebnis zu gelangen. Ist diese Flexibilität nicht verwirrend?

Antwort:

A Eigentlich nicht. Wenn man die gebotene Flexibilität nutzt, kann man recht elegante Anweisungen formulieren. Die Flexibilität gehört zu den Tugenden von SQL.


Workshop


Kontrollfragen

1. Im Abschnitt über die verschachtelten Unterabfragen hat die als Beispiel dargestellte Unterabfrage mehrere Werte zurückgegeben:


LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE


2. Sind die folgenden Aussagen richtig oder falsch?


a. Die Aggregatfunktionen SUM, COUNT, MIN, MAX und AVG geben mehrere Werte zurück.


b. Es lassen sich maximal zwei Unterabfragen verschachteln.


c. Korrelierte Unterabfragen sind komplett eigenständig.


3. Funktionieren die nachstehend aufgeführten Unterabfragen mit den Tabellen BESTELLUNGEN und TEIL?


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


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


a. SELECT * FROM BESTELLUNGEN
WHERE TEILNUM =
SELECT TEILNUM FROM TEIL
WHERE BESCHREIBUNG = 'TRUE WHEEL';


b. SELECT * FROM TEILNUM
FROM BESTELLUNGEN
WHERE TEILNUM =
(SELECT * FROM TEIL
WHERE BESCHREIBUNG = 'LE SHOPPE');


c. SELECT NAME, TEILNUM
FROM BESTELLUNGEN
WHERE EXISTS
(SELECT * FROM BESTELLUNGEN
WHERE NAME = 'TRUE WHEEL');



Übung

Schreiben Sie eine Abfrage für die Tabelle BESTELLUNGEN, um alle Namen (Spalte NAME) und Bestelldaten (Spalte BESTDATUM) für jedes Geschäft zu ermitteln, das im Alphabet nach JACKS BIKE steht.



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