9 Korrelierte Subqueries

150.422
9
Datenbanken – SQL Handout 5
Korrelierte Subqueries
In gewöhnlichen Subqueries wird die innere Abfrage einmal und unabhängig von der äußeren Abfrage
durchgeführt. In korrelierten Subqueries wird die innere Abfrage mehrmals durchgeführt, jedesmal für
einen anderen Parameter, der von der äußeren Abfrage kommt.
B Beispiele. Finde für jeden Teil den Lieferanten mit dem besten Angebot (d.h. kleinster Preis).
Mit einer gewöhnlichen Subquery lässt sich der günstigste Lieferant für einen gegebenen Teil finden:
1
SELECT S_No FROM Order_Options
WHERE P_No = ’ ... ’
AND Price_per_Unit =
( SELECT MIN ( Price_per_Unit ) FROM Order_Options
WHERE P_No = ’ ... ’ )
Um ein Ergebnis für alle Teile mit einer einzigen Abfrage zu erhalten, verwenden wir eine korrelierte
Subquery:
SELECT P_No , S_No , Price_per_Unit FROM Order_Options a
WHERE Price_per_Unit =
( SELECT MIN ( Price_per_Unit ) FROM Order_Options b
WHERE b . P_No = a . P_No )
Intern führt diese Abfrage eine Schleife über alle Teile aus: Die äußere Abfrage übergibt der inneren der
Reihe nach jede einzelne P No (entspricht dem a.P No in der Abfrage). Für jede P No wird dann die
innere Abfrage ausgeführt, um den entsprechenden kleinsten Preis zu finden. Dieser wird wieder an die
äußere Abfrage übergeben, die den Lieferanten zum kleinsten Preis sucht.
Regel: Möchte man für mehrere Werte eines Attributs A all jene Attribute B
finden, für die A ein Maximum oder Minimum annimmt, so muss eine korrelierte
Subquery verwendet werden.
Achtung: Zusätzliche Bedingungen müssen typischerweise in die innere und die äußere Abfrage platziert
werden! Möchte man im ersten Beispiel etwa nur inländische Lieferanten berücksichtigen, würde die
Abfrage wie folgt aussehen. (Um an die Länderinformation zu kommen, ist ein zusätzlicher Join nötig!)
1
6
SELECT P_No , a . S_No , Price_per_Unit FROM Order_Options a JOIN Supplier s
ON s . S_No = a . S_No
WHERE s . S_Countrycode = ’A ’
AND Price_per_Unit =
( SELECT MIN ( Price_per_Unit ) FROM Order_Options b JOIN Supplier t
ON t . S_No = b . S_No
WHERE b . P_No = a . P_No
AND t . S_Countrycode = ’A ’ )
Ohne die Länderbedingung in der äußeren Abfrage würden natürlich auch ausländische Firmen angezeigt werden, die ein Teil zum gleichen Preis anbieten wie der günstigste inländische Lieferant. Fehlt die
Länderbedingung in der inneren Abfrage, so ermittelt die innere Abfrage den günstigsten Preis aller Anbieter. Wird der Teil nur von ausländischen Lieferanten am günstigsten angeboten, so findet die äußere
Abfrage keinen inländischen Lieferanten zum entsprechenden Teil und dieses Teil fehlt im Ergebnis.
Zeige alle Angebote, bei denen der Preis eines Teils unter dem durchschnittlichen Preis dieses Teils
liegt.
2
SELECT P_No , S_No , Price_per_Unit FROM Order_Options a
WHERE Price_per_Unit <
( SELECT AVG ( Price_per_Unit ) FROM Order_Options b
WHERE b . P_No = a . P_No )
Ähnlich zu den obigen Abfragen berechnet die innere Abfrage der Reihe nach den durchschnittlichen
Preis jedes einzelnen Teils und gibt diesen an die äußere Abfrage zurück.
SS 2016
Man beachte, dass eine Änderung der Bedingung in der inneren Abfrage natürlich ein anderes Ergebnis
liefert. Ersetzt man in den obigen Abfragen etwa die Bedingung b.P No = a.P No durch b.S No =
a.S No, so berechnet die innere Query den günstigsten bzw. durchschnittlichen Preis pro Lieferanten.
B Beispiele. Zeige für jeden Lieferanten das günstigste Teil, das von ihm angeboten wird.
1
SELECT P_No , S_No , Price_per_Unit FROM Order_Options a
WHERE Price_per_Unit = ( SELECT MIN ( Price_per_Unit ) FROM Order_Options b
WHERE b . S_No = a . S_No )
Zeige alle Angebote, bei denen ein Lieferant ein Teil unter dem Durchschnittspreis eines Teiles bei
diesem Lieferanten anbietet.
2
SELECT P_No , S_No , Price_per_Unit FROM Order_Options a
WHERE Price_per_Unit < ( SELECT AVG ( Price_per_Unit ) FROM Order_Options b
WHERE b . S_No = a . S_No )
• ALL und ANY • können in (gewöhnlichen wie korrelierten) Subqueries verwendet werden, um eine
Bedingung für alle oder einige von einer Subquery zurückgegebenen Werte zu überprüfen. (Ähnlich wie
bei IN darf eine solche Subquery also auch mehrere Zeilen zurückgeben.)
B Examples. Zeige alle Angebote aus Order Options, bei denen der Preis unter dem Preis aller
Angebote für Teil DL481C576 liegt.
2
SELECT P_No , S_No , Price_Per_Unit from Order_Options
WHERE Price_Per_Unit < ALL
( SELECT Price_Per_Unit FROM Order_Options
WHERE P_No = ’ DL481C576 ’)
Zeige alle Angebote aus Order Options, bei denen der Preis unter dem Preis (mindestens) eines
Angebotes für Teil DL481C576 liegt.
1
SELECT P_No , S_No , Price_Per_Unit from Order_Options
WHERE Price_Per_Unit < ANY
( SELECT Price_Per_Unit FROM Order_Options
WHERE P_No = ’ DL481C576 ’)
Finde für jeden Teil den Lieferanten mit dem besten Angebot (d.h. kleinster Preis).
Alternativ zur oben gezeigten Lösung mit MIN, kann dieses Problem auch mit ALL gelöst werden.
1
SELECT P_No , S_No , Price_Per_Unit FROM Order_Options a
WHERE Price_Per_Unit <= ALL
( SELECT Price_Per_Unit FROM Order_Options b
WHERE a . P_No = b . P_No )
• EXISTS • wird dazu verwendet, um zu überprüfen, ob eine Subquery eine nichtleere Ergebnismenge
(d.h. mindestens eine Zeile) zurückgibt. Meist ist der Einsatz von EXISTS nur in Verbindung mit einer
korrelierten Subquery sinnvoll.
B Beispiele. Zeige alle Angebote aus Order Options für Teile, für die es mindestens ein Angebot
unter 1 Euro gibt.
1
SELECT P_No , S_No , Price_Per_Unit FROM Order_Options a
WHERE EXISTS
( SELECT * FROM Order_Options b
WHERE a . P_No = b . P_No and Price_Per_Unit < 1)
Analog kann mit NOT EXISTS überprüft werden, ob eine Subquery eine leere Ergebnismenge zurückgibt.
Abfragen mit ANY bzw. ALL können für gewöhnlich so umgeschrieben werden, dass stattdessen MIN
bzw. MAX verwendet wird. Ähnlich kann statt EXISTS eine Abfrage mit IN verwendet werden.