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.
7. Unterabfragen: Die eingebettete SELECT-Anweisung
Aggregatfunktionen bei Unterabfragen
Arbeiten mit EXISTS, ANY und ALL
Ziele
|
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:
SELECT *
FROM Tabelle1
WHERE Tabelle1.Eine_Spalte =
(SELECT Eine_Andere_Spalte
FROM Tabelle2
WHERE Eine_Andere_Spalte = Ein_Wert)
Da die zweite Abfrage in der ersten eingebaut ist, spricht man auch von einer verschachtelten AbfrageTEIL 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, |
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.
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
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, |
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
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) |
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
(...)
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
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 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) >
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
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 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
Hier sind doppelte Einträge zu verzeichnen. Warum finden sich diese Duplikate nicht mehr in der endgültigen Ergebnismenge?
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.