Unterabfragen (Subqueries)

Subqueries - auch Unterabfragen genannt - sind Abfragen innerhalb einer anderen Abfrage. Sie ermoglichen es dir, komplexe Fragen zu beantworten, die mit einer einfachen Abfrage nicht moeglich waeren.

Was sind Subqueries?

Eine Subquery ist eine SELECT-Anweisung, die in Klammern innerhalb einer anderen Abfrage steht:

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

Diese Abfrage bedeutet: “Zeige alle Produkte, deren Preis ueber dem Durchschnitt liegt.”

Die innere Abfrage (SELECT AVG(preis) FROM produkte) wird zuerst ausgefuehrt und liefert den Durchschnittspreis. Dann verwendet die aeussere Abfrage diesen Wert als Filter.

Subqueries in WHERE

Vergleich mit einem einzelnen Wert (Skalare Subquery)

-- Produkte, die teurer sind als der Durchschnitt
SELECT name, preis
FROM produkte
WHERE preis > (SELECT AVG(preis) FROM produkte);

Ergebnis:

name          | preis
--------------+-------
Sneaker Sport | 79.99
Jacke Outdoor | 129.99

-- Die Bestellung mit dem hoechsten Betrag
SELECT *
FROM bestellungen
WHERE betrag = (SELECT MAX(betrag) FROM bestellungen);

-- Kunden aus der Stadt mit den meisten Kunden
SELECT name, stadt
FROM kunden
WHERE stadt = (
    SELECT stadt
    FROM kunden
    GROUP BY stadt
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

Vergleich mit einer Liste (IN-Subquery)

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

Ergebnis:

name          | email
--------------+-------------------
Anna Schmidt  | anna@example.com
Max Mueller   | max@example.com
Lisa Weber    | lisa@example.com
Tom Becker    | tom@example.com

-- Kunden, die NOCH NIE bestellt haben
SELECT name, email
FROM kunden
WHERE id NOT IN (SELECT DISTINCT kunden_id FROM bestellungen);

Ergebnis:

name        | email
------------+-------------------
Sarah Klein | sarah@example.com

-- Produkte, die schon einmal bestellt wurden
SELECT name, preis
FROM produkte
WHERE id IN (SELECT DISTINCT produkt_id FROM bestellpositionen);

EXISTS - Existenzpruefung

EXISTS prueft, ob eine Subquery mindestens eine Zeile zurueckgibt:

-- Kunden mit mindestens einer Bestellung
SELECT k.name
FROM kunden k
WHERE EXISTS (
    SELECT 1
    FROM bestellungen b
    WHERE b.kunden_id = k.id
);

-- Kunden ohne Bestellungen
SELECT k.name
FROM kunden k
WHERE NOT EXISTS (
    SELECT 1
    FROM bestellungen b
    WHERE b.kunden_id = k.id
);

EXISTS vs. IN: EXISTS ist oft schneller bei grossen Datenmengen, weil es aufhoert zu suchen, sobald eine Uebereinstimmung gefunden wird.

Subqueries in SELECT

Du kannst Subqueries auch in der SELECT-Klausel verwenden, um berechnete Werte hinzuzufuegen:

-- Jedes Produkt mit dem Durchschnittspreis und der Abweichung
SELECT
    name,
    preis,
    (SELECT ROUND(AVG(preis), 2) FROM produkte) AS avg_preis,
    ROUND(preis - (SELECT AVG(preis) FROM produkte), 2) AS abweichung
FROM produkte;

Ergebnis:

name           | preis  | avg_preis | abweichung
---------------+--------+-----------+-----------
T-Shirt Basic  |  19.99 |     55.82 |     -35.83
Jeans Classic  |  49.99 |     55.82 |      -5.83
Sneaker Sport  |  79.99 |     55.82 |      24.17
Rucksack Urban |  39.99 |     55.82 |     -15.83
Muetze Winter  |  14.99 |     55.82 |     -40.83
Jacke Outdoor  | 129.99 |     55.82 |      74.17

Korrelierte Subquery

Eine korrelierte Subquery bezieht sich auf die aeussere Abfrage. Sie wird fuer jede Zeile der aeusseren Abfrage erneut ausgefuehrt:

-- Jeder Kunde mit der Anzahl seiner Bestellungen
SELECT
    k.name,
    k.stadt,
    (SELECT COUNT(*) FROM bestellungen b WHERE b.kunden_id = k.id) AS anzahl_bestellungen
FROM kunden k;

Ergebnis:

name          | stadt    | anzahl_bestellungen
--------------+----------+--------------------
Anna Schmidt  | Berlin   |                   2
Max Mueller   | Hamburg  |                   1
Lisa Weber    | Muenchen |                   1
Tom Becker    | Berlin   |                   1
Sarah Klein   | Koeln    |                   0

-- Jedes Produkt mit der Gesamtverkaufsmenge
SELECT
    p.name,
    p.preis,
    (SELECT COALESCE(SUM(bp.menge), 0) FROM bestellpositionen bp WHERE bp.produkt_id = p.id) AS verkauft
FROM produkte p
ORDER BY verkauft DESC;

Subqueries in FROM (Abgeleitete Tabellen)

Du kannst eine Subquery als “virtuelle Tabelle” in der FROM-Klausel verwenden:

-- Durchschnittlicher Bestellwert pro Kunde, dann Gesamtdurchschnitt
SELECT ROUND(AVG(kundenumsatz), 2) AS avg_umsatz_pro_kunde
FROM (
    SELECT
        kunden_id,
        SUM(betrag) AS kundenumsatz
    FROM bestellungen
    GROUP BY kunden_id
) AS kunden_umsaetze;

-- Top-Kunden (mit mehr als 100 Euro Umsatz) und ihre Details
SELECT k.name, ku.gesamtumsatz, ku.anzahl
FROM (
    SELECT
        kunden_id,
        SUM(betrag) AS gesamtumsatz,
        COUNT(*) AS anzahl
    FROM bestellungen
    GROUP BY kunden_id
    HAVING SUM(betrag) > 100
) AS ku
JOIN kunden k ON ku.kunden_id = k.id
ORDER BY ku.gesamtumsatz DESC;

ANY und ALL (PostgreSQL)

In PostgreSQL kannst du ANY und ALL mit Subqueries verwenden:

-- Produkte, die teurer sind als IRGENDEIN Produkt in 'Accessoires'
SELECT name, preis
FROM produkte
WHERE preis > ANY (
    SELECT preis FROM produkte WHERE kategorie = 'Accessoires'
);

-- Produkte, die teurer sind als ALLE Produkte in 'Accessoires'
SELECT name, preis
FROM produkte
WHERE preis > ALL (
    SELECT preis FROM produkte WHERE kategorie = 'Accessoires'
);

Praxisbeispiele

Kunden, die ueberdurchschnittlich viel bestellen

SELECT k.name, SUM(b.betrag) AS umsatz
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
GROUP BY k.name
HAVING SUM(b.betrag) > (SELECT AVG(betrag) FROM bestellungen);

Produkte, die noch nie verkauft wurden

SELECT name, preis, lagerbestand
FROM produkte
WHERE id NOT IN (
    SELECT DISTINCT produkt_id
    FROM bestellpositionen
);

Rang innerhalb der Kategorie

SELECT
    p.name,
    p.kategorie,
    p.preis,
    (SELECT COUNT(*)
     FROM produkte p2
     WHERE p2.kategorie = p.kategorie AND p2.preis > p.preis
    ) + 1 AS rang_in_kategorie
FROM produkte p
ORDER BY p.kategorie, rang_in_kategorie;

Letzte Bestellung jedes Kunden

SELECT b.*
FROM bestellungen b
WHERE b.bestelldatum = (
    SELECT MAX(b2.bestelldatum)
    FROM bestellungen b2
    WHERE b2.kunden_id = b.kunden_id
);

Subquery vs. JOIN

Viele Subqueries koennen auch als JOINs geschrieben werden:

-- Mit Subquery:
SELECT name FROM kunden
WHERE id IN (SELECT kunden_id FROM bestellungen);

-- Mit JOIN (gleich Ergebnis):
SELECT DISTINCT k.name
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id;

	Subquery	JOIN
Lesbarkeit	Oft intuitiver	Bei vielen Tabellen besser
Performance	Variabel	Oft besser optimiert
Flexibilitaet	Sehr flexibel	Standardmaessig
Empfehlung	Fuer Vergleiche	Fuer Verknuepfungen

Was kommt als Naechstes?

Im naechsten Tutorial lernst du INSERT kennen - damit fuegst du neue Daten in deine Tabellen ein.

Zusammenfassung

• Subqueries sind Abfragen innerhalb einer anderen Abfrage
• In WHERE: Vergleich mit berechneten Werten oder Listen (IN, EXISTS)
• In SELECT: Berechnete Spalten pro Zeile
• In FROM: Virtuelle Tabellen (abgeleitete Tabellen)
• Korrelierte Subqueries beziehen sich auf die aeussere Abfrage
• EXISTS ist oft effizienter als IN bei grossen Datenmengen
• Viele Subqueries koennen alternativ als JOINs geschrieben werden

Uebungen

1. Skalare Subquery: Finde alle Produkte, die mehr als doppelt so viel kosten wie das guenstigste Produkt.
2. IN-Subquery: Finde alle Kunden, die ein Produkt aus der Kategorie ‘Schuhe’ bestellt haben.
3. NOT IN: Welche Produkte wurden noch nie bestellt?
4. Korrelierte Subquery: Zeige jeden Kunden mit seiner letzten Bestellung.
5. FROM-Subquery: Berechne den Durchschnitt der Kundenumsaetze (d.h. den Durchschnitt der Summen pro Kunde).

-- Loesung zu Uebung 1:
SELECT name, preis
FROM produkte
WHERE preis > 2 * (SELECT MIN(preis) FROM produkte);

-- Loesung zu Uebung 3:
SELECT name, preis
FROM produkte
WHERE id NOT IN (SELECT DISTINCT produkt_id FROM bestellpositionen);