Unterabfragen (Subqueries) sind ein mächtiges Werkzeug in SQL, um komplexe Auswertungen durchzuführen. Sie ermöglichen es, Ergebnisse einer Abfrage in einer anderen Abfrage zu verwenden und so Informationen zu kombinieren, die nicht direkt in einer Tabelle verfügbar sind.
Oft benötigen wir bei Abfragen Informationen, die erst durch eine andere Abfrage ermittelt werden müssen. Hier kommen Unterabfragen ins Spiel:
-- Problem: Mitarbeiter mit überdurchschnittlichem Gehalt
-- Ohne Unterabfrage bräuchten wir zwei separate Abfragen:
SELECT AVG(gehalt) FROM mitarbeiter; -- 1. Durchschnitt ermitteln
SELECT * FROM mitarbeiter WHERE gehalt > 60000; -- 2. Vergleichen
-- Lösung mit Unterabfrage:
SELECT *
FROM mitarbeiter
WHERE gehalt > (
SELECT AVG(gehalt)
FROM mitarbeiter
);In diesem Beispiel wollen wir alle Mitarbeiter finden, die mehr als das durchschnittliche Gehalt verdienen. Ohne Unterabfrage müssten wir zuerst den Durchschnitt berechnen und dann in einer zweiten Abfrage die Mitarbeiter damit vergleichen.
Mit einer Unterabfrage können wir beides in einem Schritt erledigen.
Die Unterabfrage SELECT AVG(gehalt) FROM mitarbeiter wird
zuerst ausgeführt und liefert das durchschnittliche Gehalt. Dieses
Ergebnis wird dann in der äußeren Abfrage als Vergleichswert
verwendet.
Eine nicht korrelierende Unterabfrage ist unabhängig von der äußeren Abfrage. Sie bezieht sich nicht auf Tabellen oder Spalten der äußeren Abfrage und wird nur einmal ausgeführt, bevor die äußere Abfrage ausgeführt wird.
Eine Skalarunterabfrage ist eine Unterabfrage, die genau einen Wert zurückliefert. Sie kann überall dort verwendet werden, wo ein einzelner Wert erwartet wird, z.B. in einer WHERE-Klausel oder einer SELECT-Liste.
-- Finde die Bank(en) mit der höchsten BLZ
SELECT name, blz
FROM banken
WHERE blz = (
SELECT MAX(blz)
FROM banken
);In diesem Beispiel wollen wir die Bank(en) mit der höchsten
Bankleitzahl (BLZ) finden. Die Unterabfrage
SELECT MAX(blz) FROM banken ermittelt die höchste BLZ.
Dieser Wert wird dann in der äußeren Abfrage verwendet, um die
entsprechenden Banken zu finden.
SELECT
artikelnummer,
bezeichnung,
preis
FROM artikel
WHERE preis > (
SELECT AVG(preis)
FROM artikel
);Hier wollen wir alle Artikel finden, die teurer als der Durchschnittspreis sind. Die Unterabfrage berechnet den durchschnittlichen Preis aller Artikel. Die äußere Abfrage vergleicht dann den Preis jedes Artikels mit diesem Durchschnittswert.
SELECT
bestellnummer,
datum,
gesamtbetrag
FROM bestellungen
WHERE gesamtbetrag > (
SELECT AVG(gesamtbetrag)
FROM bestellungen
WHERE YEAR(datum) = YEAR(CURRENT_DATE)
);In diesem Beispiel suchen wir Bestellungen, deren Gesamtbetrag über
dem Durchschnitt aller Bestellungen des aktuellen Jahres liegt. Die
Unterabfrage berechnet den durchschnittlichen Gesamtbetrag der
Bestellungen des aktuellen Jahres
(YEAR(datum) = YEAR(CURRENT_DATE)). Die äußere Abfrage
vergleicht dann den Gesamtbetrag jeder Bestellung mit diesem
Durchschnittswert.
Eine Listenunterabfrage ist eine Unterabfrage, die eine Liste von
Werten zurückliefert. Sie kann mit den Operatoren IN,
ANY, ALL oder EXISTS verwendet
werden.
-- Finde alle Mitarbeiter aus Vertrieb oder Marketing
SELECT *
FROM mitarbeiter
WHERE abteilung_id IN (
SELECT abteilung_id
FROM abteilungen
WHERE name IN ('Vertrieb', 'Marketing')
);In diesem Beispiel wollen wir alle Mitarbeiter finden, die in der
Abteilung Vertrieb oder Marketing arbeiten. Die Unterabfrage ermittelt
die Abteilungs-IDs der Abteilungen mit dem Namen ‘Vertrieb’ oder
‘Marketing’. Die äußere Abfrage verwendet dann den
IN-Operator, um alle Mitarbeiter zu finden, deren
Abteilungs-ID in dieser Liste vorkommt.
-- Finde Produkte, die teurer sind als alle Produkte einer Kategorie
SELECT *
FROM produkte
WHERE preis > ALL (
SELECT preis
FROM produkte
WHERE kategorie_id = 5
);Hier suchen wir Produkte, die teurer sind als alle Produkte der
Kategorie mit der ID 5. Die Unterabfrage liefert eine Liste aller Preise
der Produkte in Kategorie 5. Der ALL-Operator in der
äußeren Abfrage stellt sicher, dass der Preis des Produkts größer sein
muss als jeder Wert in dieser Liste.
-- Finde Bestellungen, die größer sind als alle Bestellungen aus 2023
SELECT *
FROM bestellungen
WHERE betrag > ALL (
SELECT betrag
FROM bestellungen
WHERE YEAR(datum) = 2023
);In diesem Beispiel suchen wir Bestellungen, deren Betrag größer ist
als alle Bestellungen aus dem Jahr 2023. Die Unterabfrage liefert eine
Liste aller Beträge der Bestellungen aus 2023. Der
ALL-Operator stellt sicher, dass der Betrag der Bestellung
größer sein muss als jeder Wert in dieser Liste.
-- Finde Produkte, die teurer sind als irgendein Produkt einer Kategorie
SELECT *
FROM produkte
WHERE preis > ANY (
SELECT preis
FROM produkte
WHERE kategorie_id = 5
);Hier suchen wir Produkte, die teurer sind als mindestens ein Produkt
der Kategorie mit der ID 5. Die Unterabfrage liefert eine Liste aller
Preise der Produkte in Kategorie 5. Der ANY-Operator in der
äußeren Abfrage stellt sicher, dass der Preis des Produkts größer sein
muss als mindestens ein Wert in dieser Liste.
-- IN(): Wert muss in der Liste vorkommen
WHERE abteilung_id IN (1, 2, 3)
-- ALL(): Bedingung muss für alle Werte erfüllt sein
WHERE gehalt > ALL(
SELECT gehalt
FROM mitarbeiter
WHERE abteilung_id = 5
)
-- ANY(): Bedingung muss für mindestens einen Wert erfüllt sein
WHERE gehalt > ANY(
SELECT gehalt
FROM mitarbeiter
WHERE abteilung_id = 5
)IN prüft, ob ein Wert in einer Liste von Werten
vorkommt. Die Liste kann als Unterabfrage oder als Werteliste angegeben
werden.ALL vergleicht einen Wert mit allen Werten einer
Unterabfrage. Die Bedingung muss für alle Werte der Unterabfrage erfüllt
sein.ANY vergleicht einen Wert mit allen Werten einer
Unterabfrage. Die Bedingung muss für mindestens einen Wert der
Unterabfrage erfüllt sein. ANY ist äquivalent zu
SOME.-- NOT IN(): Vorsicht bei NULL-Werten
WHERE abteilung_id NOT IN (
SELECT abteilung_id
FROM geschlossene_abteilungen
)
-- <> ALL(): Alternative Schreibweise
WHERE abteilung_id <> ALL (
SELECT abteilung_id
FROM geschlossene_abteilungen
)NOT IN und <> ALL sind äquivalent,
wenn die Unterabfrage keine NULL-Werte enthält. Wenn die Unterabfrage
jedoch NULL-Werte enthalten kann, gibt es einen Unterschied:
NOT IN gibt keine Zeilen zurück, wenn die Unterabfrage
einen NULL-Wert enthält, auch wenn die Bedingung für alle anderen Werte
erfüllt ist.<> ALL gibt Zeilen zurück, wenn die Bedingung für
alle Nicht-NULL-Werte erfüllt ist, unabhängig davon, ob die Unterabfrage
NULL-Werte enthält.Eine Tabellenunterabfrage ist eine Unterabfrage, die mehrere Spalten
und Zeilen zurückliefert. Sie kann wie eine normale Tabelle verwendet
werden, z.B. in einer FROM-Klausel oder einem
JOIN.
-- Verwendung als abgeleitete Tabelle
SELECT
t.abteilung,
t.anzahl_mitarbeiter,
t.durchschnittsgehalt
FROM (
SELECT
abteilung,
COUNT(*) as anzahl_mitarbeiter,
AVG(gehalt) as durchschnittsgehalt
FROM mitarbeiter
GROUP BY abteilung
) t
WHERE t.anzahl_mitarbeiter > 10;In diesem Beispiel wird die Unterabfrage als abgeleitete Tabelle verwendet. Sie berechnet für jede Abteilung die Anzahl der Mitarbeiter und das Durchschnittsgehalt. Die äußere Abfrage verwendet dann diese abgeleitete Tabelle, um nur die Abteilungen auszuwählen, die mehr als 10 Mitarbeiter haben.
Eine korrelierende Unterabfrage bezieht sich auf Spalten der äußeren Abfrage. Sie wird für jede Zeile der äußeren Abfrage ausgeführt und kann dadurch unterschiedliche Ergebnisse für jede Zeile liefern.
-- Finde Mitarbeiter, die mehr verdienen als der
-- Durchschnitt ihrer Abteilung
SELECT m1.name, m1.gehalt, m1.abteilung
FROM mitarbeiter m1
WHERE m1.gehalt > (
SELECT AVG(m2.gehalt)
FROM mitarbeiter m2
WHERE m2.abteilung = m1.abteilung
);
-- Finde Produkte, die teurer sind als der
-- Durchschnitt ihrer Kategorie
SELECT p1.*,
(
SELECT AVG(p2.preis)
FROM produkte p2
WHERE p2.kategorie_id = p1.kategorie_id
) AS kategorie_durchschnitt
FROM produkte p1
WHERE p1.preis > (
SELECT AVG(p2.preis)
FROM produkte p2
WHERE p2.kategorie_id = p1.kategorie_id
);Im ersten Beispiel wollen wir Mitarbeiter finden, die mehr verdienen
als der Durchschnitt ihrer Abteilung. Die Unterabfrage bezieht sich auf
die Abteilung des aktuellen Mitarbeiters in der äußeren Abfrage
(m1.abteilung). Für jeden Mitarbeiter wird die Unterabfrage
ausgeführt, um den Durchschnitt seiner Abteilung zu berechnen.
Im zweiten Beispiel suchen wir Produkte, die teurer sind als der
Durchschnitt ihrer Kategorie. Hier wird die Unterabfrage sogar zweimal
verwendet: Einmal in der SELECT-Liste, um den
Kategoriedurchschnitt anzuzeigen, und einmal in der
WHERE-Klausel, um die Produkte zu filtern. In beiden Fällen
bezieht sich die Unterabfrage auf die Kategorie-ID des aktuellen
Produkts in der äußeren Abfrage (p1.kategorie_id).
Praktische Tipps für Unterabfragen:
-- Besser: JOIN statt korrelierender Unterabfrage
SELECT m.*, a.durchschnittsgehalt
FROM mitarbeiter m
JOIN (
SELECT
abteilung,
AVG(gehalt) as durchschnittsgehalt
FROM mitarbeiter
GROUP BY abteilung
) a ON m.abteilung = a.abteilung
WHERE m.gehalt > a.durchschnittsgehalt;Korrelierende Unterabfragen können die Performanz beeinträchtigen, da sie für jede Zeile der äußeren Abfrage ausgeführt werden. In vielen Fällen lässt sich das gleiche Ergebnis effizienter mit einem JOIN erreichen. Im Beispiel wird statt der korrelierenden Unterabfrage eine Tabellenunterabfrage verwendet und mit der Mitarbeitertabelle verknüpft.
-- Unterabfrage separat testen
SELECT AVG(gehalt) FROM mitarbeiter;
-- Dann in Hauptabfrage einbauen
SELECT *
FROM mitarbeiter
WHERE gehalt > (SELECT AVG(gehalt) FROM mitarbeiter);Bei komplexen Unterabfragen ist es hilfreich, sie zuerst separat zu testen, bevor man sie in die Hauptabfrage einbaut. So kann man sicherstellen, dass die Unterabfrage das erwartete Ergebnis liefert und Fehler leichter eingrenzen.
-- Index für häufig verwendete Unterabfragen
CREATE INDEX idx_abteilung_gehalt
ON mitarbeiter(abteilung_id, gehalt);Wenn Unterabfragen häufig verwendet werden, kann es sinnvoll sein,
Indizes für die beteiligten Spalten zu erstellen. Im Beispiel wird ein
zusammengesetzter Index für die Spalten abteilung_id und
gehalt erstellt, der sowohl für die Filterung als auch für
die Aggregation in der Unterabfrage genutzt werden kann.
-- Komplexe Unterabfragen als Views speichern
CREATE
VIEW abteilungsstatistik AS
SELECT
abteilung,
COUNT(*) as anzahl_mitarbeiter,
AVG(gehalt) as durchschnittsgehalt
FROM mitarbeiter
GROUP BY abteilung;Komplexe Unterabfragen können die Lesbarkeit einer Abfrage
beeinträchtigen. In solchen Fällen kann es sinnvoll sein, die
Unterabfrage als View zu speichern und dann in der Hauptabfrage zu
verwenden. Im Beispiel wird eine View abteilungsstatistik
erstellt, die für jede Abteilung die Anzahl der Mitarbeiter und das
Durchschnittsgehalt berechnet. Diese View kann dann in anderen Abfragen
verwendet werden, z.B. um Mitarbeiter zu finden, die mehr als der
Durchschnitt ihrer Abteilung verdienen:
SELECT m.*
FROM mitarbeiter m
JOIN abteilungsstatistik a
ON m.abteilung = a.abteilung
WHERE m.gehalt > a.durchschnittsgehalt;Unterabfragen sind ein leistungsstarkes Werkzeug, um komplexe Auswertungen in SQL durchzuführen. Sie ermöglichen es, Informationen aus verschiedenen Tabellen zu kombinieren und Ergebnisse von Abfragen in anderen Abfragen zu verwenden. Hier noch einmal die wichtigsten Punkte zusammengefasst:
Unterabfragen können überall dort verwendet werden, wo ein einzelner Wert (Skalarunterabfrage), eine Liste von Werten (Listenunterabfrage) oder eine Tabelle (Tabellenunterabfrage) erwartet wird. Nicht korrelierende Unterabfragen sind unabhängig von der äußeren Abfrage und werden nur einmal ausgeführt. Korrelierende Unterabfragen beziehen sich auf die äußere Abfrage und werden für jede Zeile der äußeren Abfrage ausgeführt. Unterabfragen können die Lesbarkeit und Performanz beeinträchtigen. Alternative Lösungen wie JOINs oder Views sollten in Betracht gezogen werden. Komplexe Unterabfragen sollten zuerst separat getestet werden, bevor sie in die Hauptabfrage eingebaut werden. Indizes können die Performanz von Unterabfragen verbessern.
Unterabfragen erfordern ein gutes Verständnis der Tabellenstrukturen und der Beziehungen zwischen den Tabellen. Sie sollten mit Bedacht eingesetzt werden, da sie die Komplexität einer Abfrage erhöhen und die Performanz beeinträchtigen können. Gleichzeitig bieten sie aber auch enormes Potenzial, um Daten flexibel auszuwerten und Ergebnisse zu kombinieren, die mit einfachen Abfragen nicht möglich wären. Üben Sie die Verwendung von Unterabfragen anhand praktischer Beispiele aus Ihrem Arbeitsalltag. Analysieren Sie bestehende Abfragen und überlegen Sie, wo Unterabfragen sinnvoll eingesetzt werden können. Experimentieren Sie mit verschiedenen Arten von Unterabfragen und testen Sie ihre Performanz. Mit der Zeit werden Sie ein Gefühl dafür entwickeln, wann Unterabfragen die beste Lösung sind und wann alternative Ansätze wie JOINs oder Views geeigneter sind. So können Sie Ihre Abfragen optimieren und die volle Leistungsfähigkeit von SQL für Ihre Datenanalysen nutzen.