Subqueries

Eine Subquery (auch Unterabfrage oder verschachtelte Abfrage) ist eine SQL-Abfrage, die innerhalb einer anderen SQL-Abfrage eingebettet ist. Subqueries ermöglichen es uns, das Ergebnis einer Abfrage als Grundlage für eine übergeordnete Abfrage zu verwenden. Wir können in verschiedenen Klauseln eingesetzt werden: in SELECT, FROM, WHERE oder HAVING.

Subquery in der WHERE-Klausel

Die häufigste Verwendung ist die Filterung auf Basis eines berechneten Wertes.

Beispiel: Kunden, die mehr als den durchschnittlichen Umsatz generiert haben

SELECT 
    k.id,
    k.vorname,
    k.nachname,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS gesamtumsatz
FROM kunden k
LEFT JOIN bestellungen b ON k.id = b.kunden_id
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY k.id, k.vorname, k.nachname
HAVING gesamtumsatz > (
    SELECT AVG(kundenumsatz.umsatz)
    FROM (
        SELECT COALESCE(SUM(bp2.menge * bp2.einzelpreis), 0) AS umsatz
        FROM kunden k2
        LEFT JOIN bestellungen b2 ON k2.id = b2.kunden_id
        LEFT JOIN bestellpositionen bp2 ON b2.id = bp2.bestellung_id
        GROUP BY k2.id
    ) AS kundenumsatz
);

SELECT 
    k.id,
    k.vorname,
    k.nachname,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS gesamtumsatz
FROM kunden k
LEFT JOIN bestellungen b ON k.id = b.kunden_id
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY k.id, k.vorname, k.nachname
HAVING gesamtumsatz > (
    SELECT AVG(kundenumsatz.umsatz)
    FROM (
        SELECT COALESCE(SUM(bp2.menge * bp2.einzelpreis), 0) AS umsatz
        FROM kunden k2
        LEFT JOIN bestellungen b2 ON k2.id = b2.kunden_id
        LEFT JOIN bestellpositionen bp2 ON b2.id = bp2.bestellung_id
        GROUP BY k2.id
    ) AS kundenumsatz
);

Diese Abfrage zeigt die Leistungsfähigkeit von Subqueries: Die innere Abfrage berechnet zunächst den Umsatz für jeden Kunden, daraus wird der Durchschnitt gebildet, und die äußere Abfrage filtert dann die Kunden, die über diesem Durchschnitt liegen.

Einfacheres Beispiel: Produkte, die teurer sind als der Durchschnitt aller Produkte

SELECT name, preis
FROM produkte
WHERE preis > (SELECT AVG(preis) FROM produkte);

SELECT name, preis
FROM produkte
WHERE preis > (SELECT AVG(preis) FROM produkte);

Subquery in der SELECT-Klausel

Subqueries können auch direkt im SELECT-Teil verwendet werden, um berechnete Werte pro Zeile einzufügen.

Beispiel: Zu jedem Produkt die Anzahl der Bestellungen anzeigen, in denen es vorkam

SELECT 
    p.id,
    p.name,
    p.preis,
    (SELECT COUNT(*) 
     FROM bestellpositionen bp 
     WHERE bp.produkt_id = p.id) AS anzahl_bestellungen
FROM produkte p;

SELECT 
    p.id,
    p.name,
    p.preis,
    (SELECT COUNT(*) 
     FROM bestellpositionen bp 
     WHERE bp.produkt_id = p.id) AS anzahl_bestellungen
FROM produkte p;

Subquery mit IN, EXISTS, ANY, ALL

Subqueries lassen sich mit speziellen Operatoren kombinieren.

IN: Prüft, ob ein Wert in der Ergebnisliste der Subquery vorkommt.

-- Kunden, die mindestens eine Bestellung aufgegeben haben
SELECT vorname, nachname
FROM kunden
WHERE id IN (SELECT DISTINCT kunden_id FROM bestellungen);

-- Kunden, die mindestens eine Bestellung aufgegeben haben
SELECT vorname, nachname
FROM kunden
WHERE id IN (SELECT DISTINCT kunden_id FROM bestellungen);

EXISTS: Prüft, ob die Subquery mindestens eine Zeile zurückgibt. Oft effizienter als IN bei großen Datenmengen.

-- Kunden, die mindestens eine Bestellung aufgegeben haben (mit EXISTS)
SELECT vorname, nachname
FROM kunden k
WHERE EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunden_id = k.id);

-- Kunden, die mindestens eine Bestellung aufgegeben haben (mit EXISTS)
SELECT vorname, nachname
FROM kunden k
WHERE EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunden_id = k.id);