SQL-Handout

150.422
5
Datenbanken – SQL Handout 3
SS 2017
Subqueries
• Subqueries • 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?
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.
SELECT * FROM Supplier
WHERE S_No IN (3 , 5 , 11)
Zeige alle Angebote von Lieferant 1 aus Order Options.
SELECT * FROM Order_Options
WHERE P_No IN
( SELECT P_No FROM Order_Options
WHERE S_No = 1)
Eine innere Abfrage, die einen einzelnen Wert zurückgibt, kann an jeder Stelle statt einer Konstanten verwendet
werden.
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 ’
• Subqueries als Tabellen •
Neben der Möglichkeit, mit IN auf eine Liste von Werten abzufragen, kann das Ergebnis einer Subquery in
der äußeren Abfrage auch wie eine Tabelle verwendet werden. In diesem Fall steht die Subquery entsprechend
im FROM-Teil der äußeren Abfrage.
B Beispiel. Bestimme den höchsten Durchschnittspreis (pro Einheit) eines Teils in Order Options.
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!
• 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 jedes Teil nur einmal gelistet, auch wenn
er in beiden Tabellen vorkommt.
( 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!