Bäume und Folgen: WITH RECURSIVE
Kurz gesagt: WITH RECURSIVE ist eine Abfrage mit einem Schritt, der sein eigenes Ergebnis liest, sodass sie Bäume wie Kategorien durchlaufen oder eine Folge von Daten erzeugen kann.
Problem
Der Pfad zur Kategorie
Über jedem Produkt zeigt die Website den Pfad dorthin: „Lebensmittel > Milchprodukte“. Die Kategorien sind ein Baum: Jede hat ein parent_id, das auf die Kategorie über ihr zeigt. Mit einem Self Join aus dem Modul über das Verbinden geht das so:
Das funktioniert, weil der Baum zwei Ebenen hat. Wenn morgen „Käse“ unter „Milchprodukte“ auftaucht, brauchst du einen Join mehr, und für vier Ebenen wieder einen mehr. Du weißt nicht im Voraus, wie viele Ebenen es werden.
Die Idee
Ein Schritt, der sich selbst liest
Mit WITH RECURSIVE kann ein Schritt seine eigenen Zeilen lesen. Hier das kleinste Beispiel, die Zahlen von 1 bis 5:
Der Schritt hat zwei Teile, verbunden durch UNION ALL:
- Der Anfang
SELECT 1liefert die erste Zeile. - Der rekursive Teil
SELECT x + 1 FROM n WHERE x < 5nimmt die Zeilen aus der vorigen Runde und macht neue: aus 1 macht er 2, aus 2 macht er 3.
Das wiederholt sich, bis der rekursive Teil nichts mehr liefert: Bei x = 5 ist die Bedingung nicht mehr wahr. Ein solcher Schritt heißt rekursive CTE.
Ohne WHERE x < 5 würde die Abfrage nie aufhören. Jeder rekursive Teil braucht eine Bedingung, die ihn früher oder später stoppt.
Die Idee
Der Baum der Kategorien
Jetzt der Pfad. Der Anfang sind die Hauptkategorien (ohne Elternteil). Der rekursive Teil findet die Kinder von allem, was bisher gefunden wurde, und hängt ihren Namen an den Pfad:
In der ersten Runde sind „Lebensmittel“ und „Haushalt“ da (Ebene 1). In der zweiten kommen die sechs Unterkategorien (Ebene 2), jede mit dem Pfad ihres Elternteils, „ > “ und ihrem eigenen Namen. In der dritten Runde gibt es keine Kinder mehr, und alles hört auf. Wird der Baum tiefer, erreicht dieselbe Abfrage auch die neuen Ebenen.
Probier es aus
Alles in „Lebensmittel“
Zeige Name und Preis aller Produkte in „Lebensmittel“ samt seinen Unterkategorien. Hol die Kategorienummern mit einem rekursiven Schritt, nicht von Hand. Das Feld beginnt mit dem Anfang des Schritts.
Lösung
WITH RECURSIVE sub(id) AS (
SELECT id FROM categories WHERE name = 'Lebensmittel'
UNION ALL
SELECT c.id FROM categories c JOIN sub s ON c.parent_id = s.id
)
SELECT name, price FROM products
WHERE category_id IN (SELECT id FROM sub);Der Anfang ist „Lebensmittel“, der rekursive Teil ergänzt seine Kinder, und die Produkte findest du mit category_id IN (SELECT id FROM sub). 18 Produkte. „Lebensmittel“ selbst hat keine Produkte, steht aber im Schritt und schadet nicht.
Sieh es dir an
Die Tage ohne Verkäufe
Die Shopleitung will ein Diagramm der Verkäufe nach Tagen für Oktober 2026. Der erste Versuch:
Fünf Zeilen. Der Oktober hat 31 Tage, und das Diagramm würde den 3. Oktober direkt mit dem 5. verbinden und verstecken, dass vom 7. bis zum 17. Oktober nichts verkauft wurde. Ein Tag ohne Bestellungen steht nicht in orders, also hat GROUP BY nichts, woraus es eine Zeile machen könnte.
Die Idee
Eine Folge von Daten
Ein rekursiver Schritt kann alle Tage selbst erzeugen. Der Anfang ist der 1. Oktober, und jede Runde fügt mit date(day, '+1 day') einen Tag hinzu:
31 Zeilen. Jetzt werden die Verkäufe mit LEFT JOIN an die Tage gehängt, und für die Tage ohne Bestellungen setzt COALESCE eine 0:
Alle 31 Tage sind da, auch die mit null. Die Reihenfolge beim LEFT JOIN zählt: Links stehen die Tage, von denen du alle willst, rechts die Bestellungen, die fehlen können.
Genauer, für Neugierige
PostgreSQL hat eine fertige Funktion für eine Folge: generate_series(DATE '2026-10-01', DATE '2026-10-31', INTERVAL '1 day'). In SQLite und in MySQL (ab Version 8) wird so eine Folge mit WITH RECURSIVE gemacht, wie hier.
Übung
Zwei Aufgaben
1. An wie vielen Tagen im Oktober 2026 hatte der Shop gar keine Bestellung?
Zähle die Tage im Oktober 2026 ohne eine einzige Bestellung, bis zum Ende des Monats, auch die Tage nach der letzten Bestellung (Spalte quiet_days). Das Feld hat die Folge der Tage schon.
Lösung
WITH RECURSIVE days(day) AS (
SELECT '2026-10-01'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT COUNT(*) AS quiet_days FROM days d
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.ordered_at = d.day);WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.ordered_at = d.day) und COUNT(*): 26 Tage. Nur an 5 Tagen gibt es Bestellungen.
2. Die Website will für jedes Produkt den ganzen Pfad: „Lebensmittel > Milchprodukte > Weißkäse“.
Zeige den Namen jedes Produkts, das eine Kategorie hat, und seinen ganzen Pfad (Spalte breadcrumb), von der Hauptkategorie bis zum Produktnamen. Das Feld hat den Baum schon.
Lösung
WITH RECURSIVE tree(id, name, path, level) AS (
SELECT id, name, name, 1
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT p.name, t.path || ' > ' || p.name AS breadcrumb
FROM products p
JOIN tree t ON t.id = p.category_id;JOIN tree t ON t.id = p.category_id und t.path || ' > ' || p.name. 24 Zeilen: Die Geschenkkarte hat keine Kategorie, und JOIN lässt sie weg. Mit LEFT JOIN wäre sie dabei, aber mit NULL als Pfad.
Test