7 Verknüpfen von Tabellen

150.422
7
Datenbanken – SQL Handout 4
SS 2016
Verknüpfen von Tabellen
• Joins • werden zur Verknüpfung von Informationen aus zwei oder mehreren Tabellen verwendet. Die
allgemeine Form eines Joins sieht dabei wie folgt aus:
SELECT spalte1, spalte2,...
FROM tabelle1 JOIN tabelle2 JOIN ...
ON bedingung
In bedingung wird die Verbindung der verknüpften Tabellen angegeben. Kann der Name einer Spalte nicht eindeutig einer Tabelle zugeordnet werden, so muss die Spalte genauer über tabelle.spalte
angesprochen werden. Dabei können für Tabellen Abkürzungen verwendet werden.
B Beispiele. Die Tabelle Supplier enthält in der Spalte S Evaluation für jeden Lieferaten eine Evaluierung, die in der Tabelle S Evaluation genauer spezifiziert ist. Die beiden Tabellen Supplier und
S Evaluation sind über die Spalte S Evaluation verbunden und lassen sich damit über den folgenden
Join verknüpfen, um alle Lieferanten mit dem ausführlichen Evaluierungstext auszugeben.
1
SELECT s . S_Company , e . S_Evaluation_Text
FROM Supplier s JOIN S_Evaluation e
ON s . S_Evaluation = e . S_Evaluation
Möchte man alle Lieferanten (mit vollem Namen) zusammen mit den von ihnen angebotenen Teilen (in
Form des P Text) ausgeben, so benötigt man einen Join auf die drei Tabellen Parts, Order Options
und Supplier. Dabei sind die Tabellen Parts und Order Options über die gemeinsame Spalte P No
verbunden, während Supplier und Order Options die gemeinsame Spalte S No haben.
2
SELECT s . S_Company , p . P_Text
FROM Supplier s JOIN Order_Options o JOIN Parts p
ON s . S_No = o . S_No AND o . P_No = p . P_No
Manchmal –insbesondere wenn der Primär- bzw. Fremdschlüssel aus mehreren Spalten besteht– sind
auch für das Verknüpfen von zwei Tabellen mehrere Join-Bedingungen im ON nötig.
• Kombination von Joins mit anderen Abfragetechniken •
Jeder Join kombiniert mehrere Tabellen zu einer neuen Tabelle. Auf diese neue Tabelle lassen sich alle
bisher kennengelernten Abfragetechniken anwenden (z.B. Bedingungen mit WHERE, Aggregatfunktionen,
Subqueries, etc.).
B Beispiele. Zeige alle Bestellungen aus Orders, die bei ausländischen Lieferanten bestellt wurden.
2
SELECT * FROM Supplier s JOIN Orders o
ON s . S_No = o . S_No
WHERE s . S_Countrycode <> ’A ’
Zeige für jedes Land den Gesamtwert aller Bestellungen bei Lieferanten dieses Landes.
2
SELECT s . S_Country , SUM ( o . No_of_Units_ordered * o . Price_per_Unit )
FROM Supplier s JOIN Orders o ON s . S_No = o . S_No
GROUP BY s . S_Country
Wie heißt der Lieferant, der Teil KT12AB321 zum kleinsten Preis anbietet?
SELECT s . S_Company FROM Order_Options o JOIN Supplier s
ON o . S_No = s . S_No
WHERE o . P_No = ’ KT12AB321 ’
AND o . Price_per_Unit =
( SELECT MIN ( Price_per_Unit ) FROM Order_Options
WHERE P_No = ’ KT12AB321 ’)
2
• Left und Right Joins •
Gewöhnliche Joins geben nur jene Werte zurück, die in den gemeinsamen Spalten beider verknüpfter
Tabellen vorkommen. Um alle Einträge einer Tabelle zu erhalten (also insbesondere jene, die in der
anderen am Join beteiligten Tabelle nicht vorkommen) kann ein sogenannter Left (bzw. Right) Join
verwendet werden. Mit einem LEFT JOIN werden alle Werte der “linken” Tabelle des Joins angezeigt,
mit einem RIGHT JOIN alle Werte der “rechten” Tabelle.
B Beispiel. Wir haben bereits die Möglichkeit erörtert, dass Teile in Parts u.U. bei keinem Lieferanten
erhältlich sind, d.h. in Order Options nicht vorkommen. Solche Teile lassen sich über den folgenden
Left Join auffinden.
SELECT P_Text , o . S_No
FROM Parts p LEFT JOIN Order_Options o
ON p . P_No = o . P_No
Diese Abfrage gibt auch einen Teil aus, der in der Spalte S No einen NULL-Wert für den Lieferanten
angibt. Dieser Teil wird von keinem Lieferanten angeboten. Beachten Sie, dass dieser Teil bei einem
gewöhnlichen Join nicht angezeigt wird. Der folgende Right Join (mit den Tabellennamen vertauscht)
gibt dasselbe Ergebnis.
SELECT P_Text , o . S_No
FROM Order_Options o RIGHT JOIN Parts p
ON p . P_No = o . P_No
2
8
NULL-Werte
• NULL • steht für nicht verfügbare bzw. undefinierte Werte. Um auf NULL-Werte abfragen zu können,
müssen spezielle Funktionen wie IS NULL oder IS NOT NULL verwendet werden, eine Abfrage über
= NULL funktioniert nicht! Eine weitere nützliche Funktion ist IFNULL(ausdruck1, ausdruck2 ), die
ausdruck2 zurückgibt, wenn ausdruck1 gleich NULL ist, und ausdruck1 sonst.
B Beispiel. Zeige alle Lieferanten, die eine Webseite haben.
2
SELECT * FROM Supplier
WHERE S_Website IS NOT NULL
Zeige alle Lieferanten aus Supplier zusammen mit ihrer Webseite. Gib “nicht verfuegbar” an, falls der
Lieferant keine Webseite hat.
SELECT S_Company , IFNULL ( S_Website , ’ nicht verfuegbar ’) FROM Supplier
Anmerkungen:
• Berechnungen mit beteiligten NULL-Werten ergeben immer NULL: 3+NULL = NULL, LENGTH(NULL)
= NULL etc. Auch bei undefinierten Ergebnissen einer Berechnung, etwa einer Division durch 0,
erhält man als Ergebnis NULL.
• Bei der Berechnung von Aggregatfunktionen werden NULL-Werte automatisch ignoriert. Auch
COUNT(spalte) zählt nur Werte in spalte die nicht NULL sind.