🪆Unterabfragen und CTEs

Abfragen lassen sich schachteln. Eine Common Table Expression (WITH name AS (…)) gibt einer Unterabfrage einen Namen – lesbarer, wiederverwendbar und als WITH RECURSIVE sogar für Bäume und Hierarchien geeignet.

🧱Vier Arten von Unterabfragen

Skalar

liefert genau einen Wert – überall einsetzbar, wo ein Wert stehen darf.

SELECT titel, preis
FROM buecher
WHERE preis > (SELECT AVG(preis) FROM buecher);

Liste (IN)

liefert eine Spalte mit mehreren Werten.

SELECT name FROM kunden
WHERE kunde_id IN (SELECT kunde_id FROM bestellungen
                   WHERE datum >= '2026-03-01');

Korreliert (EXISTS)

bezieht sich auf die äußere Zeile – läuft logisch einmal pro Zeile.

SELECT a.name FROM autoren a
WHERE EXISTS (SELECT 1 FROM buecher b
              WHERE b.autor_id = a.autor_id
                AND b.jahr >= 2020);

Abgeleitete Tabelle

Unterabfrage im FROM – eine Tabelle auf Zeit.

SELECT genre, schnitt
FROM (SELECT genre, AVG(preis) AS schnitt
      FROM buecher GROUP BY genre) AS t
WHERE schnitt > 15;

⚖️Unterabfrage oder CTE?

Beide Varianten liefern dasselbe Ergebnis – die CTE liest sich von oben nach unten wie ein Rezept.

Verschachtelt

SELECT k.name, u.summe
FROM kunden k
  JOIN (SELECT o.kunde_id, SUM(p.menge * b.preis) AS summe
        FROM bestellungen o
          JOIN positionen p ON p.bestell_id = o.bestell_id
          JOIN buecher b ON b.buch_id = p.buch_id
        GROUP BY o.kunde_id) AS u
    ON u.kunde_id = k.kunde_id
WHERE u.summe > (SELECT AVG(preis) * 2 FROM buecher);

Mit CTE

WITH umsatz AS (
  SELECT o.kunde_id, SUM(p.menge * b.preis) AS summe
  FROM bestellungen o
    JOIN positionen p ON p.bestell_id = o.bestell_id
    JOIN buecher b ON b.buch_id = p.buch_id
  GROUP BY o.kunde_id
),
schwelle AS (
  SELECT AVG(preis) * 2 AS wert FROM buecher
)
SELECT k.name, u.summe
FROM kunden k
  JOIN umsatz u ON u.kunde_id = k.kunde_id
WHERE u.summe > (SELECT wert FROM schwelle);
💡 Wird die CTE einmal berechnet?
Das entscheidet der Optimierer. PostgreSQL (ab 12) bettet CTEs meist ein, mit AS MATERIALIZED erzwingt man einmalige Berechnung; SQLite kennt ebenfalls MATERIALIZED/NOT MATERIALIZED.
⚠️ Korreliert = pro Zeile
Eine korrelierte Unterabfrage im SELECT läuft logisch für jede Zeile neu. Bei großen Tabellen ist ein JOIN mit vorab gruppierter Tabelle oft schneller – EXPLAIN zeigt es.

🔁Rekursive CTE Schritt für Schritt

Anker liefert den Start, der rekursive Teil hängt Runde um Runde neue Zeilen an – bis keine mehr kommen.
WITH RECURSIVE baum (kat_id, name, ebene, pfad) AS (
  -- Anker: die Wurzel(n)
  SELECT kat_id, name, 0, name
  FROM kategorien
  WHERE eltern_id IS NULL
  UNION ALL
  -- rekursiver Teil: Kinder der zuletzt gefundenen Zeilen
  SELECT k.kat_id, k.name, b.ebene + 1, b.pfad || ' / ' || k.name
  FROM kategorien k
  JOIN baum b ON k.eltern_id = b.kat_id
)
SELECT * FROM baum
ORDER BY pfad;
BücherBelletristikSachbuchKrimiRomanLyrikInformatikDatenbankenNaturwissenschaft
Schritt 1 / 5 · Tasten ← →
AnkerDer Ankerteil läuft genau einmal und liefert die Startzeilen.
kat_idnameebenepfad
1Bücher0Bücher

Bisher gesammelt: 1 Zeile(n). Enthalten die Daten einen Zyklus, endet die Rekursion nie – dann hilft eine Tiefenbegrenzung (WHERE ebene < 10) oder UNION statt UNION ALL.

🧪Ausprobieren

SQL-Engine wird geladen …