CTEs (WITH): komplexe Abfragen lesbar machen

Strukturiere deine Abfragen mit WITH in benannte Schritte und verkette CTEs wie eine Pipeline von Transformationen.

7 Min. · 3 Fragen

Eine dreifach verschachtelte Unterabfrage liest sich von innen nach außen: unlesbar und sechs Monate später unmöglich wieder aufzunehmen. Die CTE (Common Table Expression, die WITH-Klausel) dreht die Leserichtung um: Du benennst jeden Zwischenschritt genau so, wie du eine Funktion benennen würdest, und die Abfrage liest sich von oben nach unten. Lade diesen Datensatz in sqliteonline.com oder sqlite3.

SQL

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  paid_at TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO payments (customer, paid_at, amount)
VALUES
  ('alice', '2026-01-10', 120.0),
  ('bruno', '2026-01-22', 80.0),
  ('alice', '2026-02-05', 60.0),
  ('chloe', '2026-02-18', 200.0),
  ('bruno', '2026-03-03', 40.0),
  ('alice', '2026-03-15', 90.0),
  ('chloe', '2026-03-28', 150.0),
  ('bruno', '2026-04-09', 300.0);

Datensatz für diese Lektion.

Grundsyntax: WITH name AS (SELECT ...) gefolgt von der Hauptabfrage, die name wie eine gewöhnliche Tabelle verwendet. Hier berechnen wir den monatlichen Umsatz, indem wir den Monat mit substr aus dem Datum extrahieren (SQLite-Datumsangaben sind ISO-8601-Text, eine Entscheidung der Engine und kein Standard).

SQL

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
)
SELECT * FROM monthly ORDER BY month;

Eine einfache CTE: ein benannter, wiederverwendbarer Schritt.

Die eigentliche Stärke kommt vom Verketten: ein einziges WITH, mehrere durch Kommas getrennte CTEs, von denen jede die zuvor definierten lesen kann. Du baust eine Pipeline: aggregieren, eine Statistik berechnen, vergleichen. Hier suchen wir die Monate, deren Umsatz über dem monatlichen Durchschnitt liegt (erwartet: 2026-03 und 2026-04, der Durchschnitt beträgt 260).

SQL

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
),
stats AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM monthly
)
SELECT m.month, m.revenue,
       ROUND(s.avg_revenue, 1) AS avg_all
FROM monthly AS m
CROSS JOIN stats AS s
WHERE m.revenue > s.avg_revenue
ORDER BY m.month;

Verkettete CTEs: stats liest monthly, das abschließende SELECT liest beide.

Benenne deine CTEs wie fachliche Funktionen (monthly, active_users, late_invoices) und debugge sie isoliert: Ersetze die abschließende Abfrage vorübergehend durch SELECT * FROM monthly, um jede Stufe der Pipeline zu prüfen. Das ist der große Vorteil gegenüber einer verschachtelten Unterabfrage, die sich nicht eigenständig ausführen lässt.

Eine CTE ist keine Performance-Garantie. Je nach Engine wird sie entweder in die Hauptabfrage eingefügt oder in einem temporären Bereich materialisiert: SQLite überlässt die Entscheidung dem Optimierer, PostgreSQL fügt seit Version 12 standardmäßig ein (Schlüsselwörter MATERIALIZED / NOT MATERIALIZED, um es zu erzwingen). Eine zweimal referenzierte CTE kann daher zweimal neu berechnet werden: Prüfe das mit EXPLAIN QUERY PLAN.

Wissenscheck

Stelle sicher, dass du die wichtigsten Punkte dieser Lektion behalten hast.

Wie deklarierst du mehrere CTEs in derselben Abfrage?
- Ein WITH-Schlüsselwort vor jeder CTE
- Ein einziges WITH, die CTEs durch Kommas getrennt
- Indem man sie ineinander verschachtelt
Kann eine CTE eine andere CTE aus demselben WITH lesen?
- Nein, jede CTE ist isoliert
- Ja, aber nur die vor ihr definierten
- Ja, in beliebiger Reihenfolge
Was ist der Hauptvorteil einer CTE gegenüber einer verschachtelten Unterabfrage?
- Sie ist immer schneller
- Sie erstellt eine dauerhafte, wiederverwendbare Tabelle
- Sie benennt jeden Schritt und macht den Ablauf lesbar und testbar