SQL Lektion 5: Unterabfragen
Lösung geprüft
Die Aufgabe der heutigen Stunde ist die fünfte SQL-Lektion:
geschachtelte Abfragen. In den Zusatzaufgaben davor haben
wir sie schon benutzt, jetzt schauen wir sie uns richtig an. Eine
Unterabfrage ist einfach ein SELECT in Klammern innerhalb
eines anderen SELECT.
a) Bearbeiten Sie die fünfte Lektion und erstellen Sie in ihrem
OneNote-Heft eine kompakte übersichtliche Kurzzusammenfassung der
SQL-Befehle.
b) Bearbeiten Sie die Übung 5
Zusatzaufgabe: Gib alle Regionen und deren durchschnittliches BIP an, deren durchschnittliches BIP über dem BIP-Gesamtdurchschnitt liegt.
Die zwei Sorten von Unterabfragen
-- Zusammenfassung der fuenften SQL-Lektion: Unterabfragen
-- @meininfokurs.cmrg.site
-- Sorte 1: Die Unterabfrage liefert EINEN Wert -> Vergleich mit = < >
SELECT Fahrerweltmeister, WM_Punkte
FROM WMTitel
WHERE WM_Punkte > (SELECT AVG(WM_Punkte) FROM WMTitel);
-- Sorte 2: Die Unterabfrage liefert MEHRERE Werte -> Vergleich mit IN
SELECT Saison, Fahrerweltmeister
FROM WMTitel
WHERE Fahrerweltmeister IN (SELECT Fahrerweltmeister
FROM WMTitel
WHERE WM_Punkte > 100);| Unterabfrage liefert | Passender Operator |
|---|---|
| genau einen Wert | = < > <= >= <> |
| mehrere Werte | IN oder NOT IN |
Das ist die häufigste Fehlerquelle: benutzt man = und die
Unterabfrage liefert mehr als eine Zeile, bricht die Datenbank mit
Subquery returns more than 1 row ab. Dann muss man entweder
auf IN wechseln oder die Unterabfrage so bauen, dass sie
wirklich nur einen Wert liefert – zum Beispiel mit einer
Aggregatfunktion.
Wann Unterabfrage, wann JOIN?
Vieles geht mit beidem. Die Faustregel:
| Du willst | Nimm |
|---|---|
| Spalten aus zwei Tabellen anzeigen | JOIN |
| Nur filtern, ohne die zweite Tabelle anzuzeigen | Unterabfrage |
| Mit einem berechneten Wert vergleichen (Durchschnitt, Maximum) | Unterabfrage – mit JOIN geht das nicht |
Der dritte Fall ist der, für den es keine Alternative gibt, und genau der kommt in der Zusatzaufgabe vor.
Die Zusatzaufgabe
cia ist im OneNote nicht abgedruckt. Ich
schreibe gegen die Spalten region und bip –
Namen gegebenenfalls anpassen.
SELECT region, AVG(bip) AS DurchschnittBIP
FROM cia
GROUP BY region
HAVING AVG(bip) > (SELECT AVG(bip) FROM cia)
ORDER BY DurchschnittBIP DESC;Zwei Durchschnitte, die verschieden sind
Der Kern der Aufgabe ist, dass hier zwei verschiedene Durchschnitte vorkommen und man sie nicht verwechseln darf:
| Wo | Was gerechnet wird |
|---|---|
AVG(bip) im SELECT und HAVING | Durchschnitt innerhalb einer Region, weil GROUP BY aktiv ist |
(SELECT AVG(bip) FROM cia) | Durchschnitt über alle Länder, unabhängig von der Gruppierung |
Zweimal derselbe Text AVG(bip), und trotzdem zwei völlig
verschiedene Zahlen – der Unterschied entsteht nur dadurch, dass die
Unterabfrage ihr eigenes FROM cia hat und vom
GROUP BY außen nichts mitbekommt.
Und es muss HAVING sein, nicht WHERE: der
Regionsdurchschnitt existiert erst, nachdem gruppiert wurde. In
WHERE könnte man ihn gar nicht ansprechen.
Wie die Datenbank das abarbeitet
- Die Unterabfrage läuft einmal und liefert eine feste Zahl, zum Beispiel 18 500
- Die äußere Abfrage gruppiert alle Länder nach Region
- Für jede Region wird der Durchschnitt gebildet
HAVINGwirft alle Regionen raus, deren Durchschnitt unter 18 500 liegtORDER BYsortiert die übrigen
Wichtig ist Schritt 1: die Unterabfrage hängt von nichts ab, was außen passiert, also muss sie nur ein einziges Mal ausgeführt werden. Solche Unterabfragen nennt man unkorreliert, und sie sind schnell. Es gibt auch korrelierte, die für jede Zeile neu laufen – die braucht man im Grundkurs aber nicht.