5 Subqueries

150.422
5
Datenbanken – SQL Handout 3
SS 2016
Subqueries
• Subqueries im WHERE • werden verwendet, um das Ergebnis einer inneren Abfrage in der äußeren Abfrage
zu verwenden. Im einfachsten Fall gibt die innere Abfrage einen einzelnen Wert zurück, der als Konstante in
der äußeren Abfrage verwendet wird.
B Beispiele. Welcher Lieferant bietet den kleinsten Preis für Teil KT12AB321?
1
SELECT S_No FROM Order_Options
WHERE P_No = ’ KT12AB321 ’
AND Price_per_Unit =
( SELECT MIN ( Price_per_Unit ) FROM Order_Options
WHERE P_No = ’ KT12AB321 ’)
Die innere Abfrage bestimmt den minimalen Preis für Teil KT12AB321. Die äußere Abfrage sucht alle Supplier,
die Teil KT12AB321 zum minimalen Preis anbieten. Man beachte, dass die innere Abfrage einen einzelnen Wert
zurückgeben muss. Die äußere Abfrage könnte auch mehrere Werte zurückgeben, falls mehrere Lieferanten
zum günstigsten Preis anbieten.
Zeige alle Bestellungen, deren Wert höher ist als der von Bestellung 1-290605-1.
SELECT * FROM Orders
WHERE Price_per_Unit * No_of_Units_ordered >
( SELECT Price_per_Unit * No_of_Units_ordered FROM Orders
WHERE O_No = ’ 1 -290605 -1 ’)
Regel: Bei Vergleichen mit =, > etc. müssen innere Abfragen genau einen einzelnen Wert zurückgeben!
Allerdings gibt es auch die Möglichkeit zu überprüfen, ob ein Element in einer Liste von Werten vorkommt.
Dies lässt sich mit IN bewerkstelligen, das auch in Kombination mit einer Subquery verwendet werden kann.
B Beispiele. Zeige die Lieferanten mit den Nummern 3, 5 und 11.
1
SELECT * FROM Supplier
WHERE S_No IN (3 , 5 , 11)
Zeige alle Angebote von Lieferant 1 aus Order Options.
3
SELECT * FROM Order_Options
WHERE P_No IN
( SELECT P_No FROM Order_Options
WHERE S_No = 1)
• Subqueries im FROM • werden dazu verwendet, auf die Ergebnistabelle der inneren Abfrage eine weitere
(äußere) Abfrage durchzuführen.
B Beispiel. Bestimme den höchsten Durchschnittspreis (pro Einheit) eines Teils in Order Options.
1
SELECT MAX ( avg_p ) FROM
( SELECT P_No , AVG ( Price_per_Unit ) AS avg_p FROM Order_Options
GROUP BY P_No ) AS new_table
Regel: Bei Abfragen im FROM muss der inneren Abfrage mit AS ein Name zugewiesen werden!
• Subqueries im SELECT part •
Da eine innere Abfrage, die einen einzelnen Wert zurückgibt, an jeder Stelle statt einer Konstanten verwendet
werden kann, lassen sich auch innere Abfragen im SELECT verwenden.
B Beispiel. Zeige für jedes Angebot für Teil KT12AB321 in Order Options die Differenz zum günstigsten
Preis.
SELECT * ,
Price_per_Unit - ( SELECT MIN ( Price_per_Unit ) FROM Order_Options
WHERE P_No = ’ KT12AB321 ’) AS Differenz
FROM Order_Options
WHERE P_No = ’ KT12AB321 ’;
2
• Vergleich von Tupeln in Subqueries •
Im allgemeinen lassen sich in SQL auch Tupel von Werten (also z.B. mehrere Spalten gleichzeitig) miteinander
vergleichen.
B Beispiel. Zeige alle Lieferanten in Supplier, die dieselbe Evaluierung und denselben Vertragsstatus wie
Lieferant 1 haben.
SELECT * FROM Supplier
WHERE ( S_Evaluation , S_Contractstatus ) =
( SELECT S_Evaluation , S_Contractstatus FROM Supplier
WHERE S_No = 1)
6
UNION, MINUS und INTERSECT
• UNION, MINUS und INTERSECT • entsprechen den mengentheoretischen Verknüpfungen ∪ (Vereinigung),
\ (Differenz) und ∩ (Schnitt). Ein Beispiel zur Erinnerung: Für A = {1, 2, 3, 4} und B = {2, 4, 6} ist A ∪ B =
{1, 2, 3, 4, 6}, A\B = {1, 3} und A∩B = {2, 4}. Mit UNION lässt sich das Ergebnis zweier Abfragen vereinigen,
vorausgesetzt die beiden Abfragen haben dieselbe Spaltenanzahl. In der Ergebnistabelle sind alle doppelten
Einträge automatisch gelöscht. UNION ist vor allem dann nützlich, wenn Daten aus verschiedenen Tabellen
miteinander verknüpft werden sollen. Ansonsten lässt sich der Gebrauch von UNION durch die Verwendung von
OR und DISTINCT umgehen.
B Beispiel. Aus Gründen der Datenintegrität sollten alle Teile, die in Order Options vorkommen, auch in
Parts verzeichnet sein. Sollte dies nicht der Fall sein, so könnte man mit UNION alle Teile finden, die zumindest
in einer der beiden Tabellen vorkommen. In der Ergebnistabelle wird jeder Teil nur einmal gelistet, auch wenn
er in beiden Tabellen vorkommt.
1
( SELECT P_No FROM Parts )
UNION
( SELECT P_No FROM Order_Option )
Ein entsprechendes INTERSECT würde alle Teile ausgeben, die in beiden Tabellen vorkommen. Mit MINUS ließe
sich die Differenz berechnen, d.h. alle Teile, die in der einen Tabelle vorkommen, aber nicht in der anderen.
MySQL unterstützt MINUS und INTERSECT bisher leider nicht. Entsprechende Abfragen können mittels IN oder
über Self Joins (siehe weiter unten) in Kombination mit entsprechenden logischen Verknüpfungen simuliert
werden.
Regel: UNION funktioniert nur, wenn alle miteinander kombinierten Abfragen dieselbe Spaltenanzahl haben!