WITH RECURSIVE – Kategoriebäume und Kommentar-Threads in einer einzigen Abfrage

Eine Tabelle, die auf sich selbst zeigt, ist der Normalfall, sobald irgendetwas verschachtelt ist:

CREATE TABLE kategorien (
    id     INTEGER PRIMARY KEY,
    eltern INTEGER REFERENCES kategorien(id),
    name   TEXT NOT NULL
);

Kategorien mit Unterkategorien. Kommentare mit Antworten. Ordner in Ordnern. Aufgaben mit Teilaufgaben.

Und dann kommt die Frage: *Gib mir alles, was unter „Technik" hängt.* Nicht nur die direkte Ebene darunter – alles, bis ganz nach unten.

Was ich früher gemacht habe: eine Abfrage für Ebene eins, dann in PHP durch die Ergebnisse laufen und für jedes eine weitere Abfrage. Das funktioniert, aber es sind bei sechs Ebenen eben auch sechs Runden zur Datenbank – und wenn eine Ebene breit wird, deutlich mehr.

Es geht in einer Abfrage.

Nach unten: der ganze Teilbaum

WITH RECURSIVE baum(id, name, tiefe, pfad) AS (
    -- Anker: wo fangen wir an?
    SELECT id, name, 0, name
      FROM kategorien
     WHERE id = 2

    UNION ALL

    -- Schritt: alle Kinder von dem, was wir schon haben
    SELECT k.id, k.name, b.tiefe + 1, b.pfad || ' / ' || k.name
      FROM kategorien k
      JOIN baum b ON k.eltern = b.id
)
SELECT tiefe, pfad FROM baum ORDER BY pfad;

Ergebnis bei einem Beispielbaum:

0  Technik
1  Technik / Handys
2  Technik / Handys / Zubehör
1  Technik / Laptops

Der Aufbau ist immer derselbe, und wenn man ihn einmal so liest, ist er auch nicht mehr geheimnisvoll:

• Der Anker vor dem UNION ALL liefert die Startzeile(n).
• Der Schritt dahinter darf sich auf die CTE selbst beziehen – hier JOIN baum b. SQLite nimmt die Zeilen, die es gerade gefunden hat, sucht deren Kinder, und wiederholt das, bis nichts Neues mehr dazukommt.
• Die beiden Extraspalten sind der eigentliche Gewinn: tiefe für die Einrückung in der Ausgabe, pfad für Sortierung und Brotkrumen. Beide entstehen unterwegs, du musst sie nirgends speichern.

Dass die Sortierung nach pfad den Baum genau in die Reihenfolge bringt, in der man ihn anzeigen will, ist der Trick, den ich am längsten übersehen habe.

Nach oben: die Brotkrumen

Dieselbe Technik in die andere Richtung – vom Blatt zur Wurzel. Nur die Verknüpfung im Schritt dreht sich um:

WITH RECURSIVE pfad(id, name, eltern) AS (
    SELECT id, name, eltern FROM kategorien WHERE id = 5

    UNION ALL

    SELECT k.id, k.name, k.eltern
      FROM kategorien k
      JOIN pfad p ON k.id = p.eltern      -- Elternteil statt Kind
)
SELECT group_concat(name, ' / ') FROM (SELECT name FROM pfad ORDER BY id);

Shop / Technik / Handys / Zubehör

Eine Abfrage, fertige Brotkrumenleiste. Ohne das habe ich früher die Kategorie geladen, dann deren Elternteil, dann dessen Elternteil – dieselbe Schleifen-Nummer wie oben, nur andersherum.

Die Falle: ein Zyklus, und die Abfrage läuft ewig

Wenn irgendwann durch einen Bug Kategorie A ihr eigenes Enkelkind wird, dreht sich die Rekursion im Kreis. Mit UNION ALL produziert sie dann Zeilen, bis der Speicher voll ist. Ich habe das genau einmal auf einem Live-System erlebt, und einmal reicht.

Der Schutz ist eine Zeile: UNION statt UNION ALL.

WITH RECURSIVE baum(id, name) AS (
    SELECT id, name FROM kategorien WHERE id = 1
    UNION                                   -- nicht UNION ALL
    SELECT k.id, k.name FROM kategorien k JOIN baum b ON k.eltern = b.id
)
SELECT * FROM baum;

UNION verwirft Zeilen, die schon da sind – und damit endet der Kreis von selbst. Das kostet eine Duplikatprüfung, aber die ist bei Bäumen billiger als ein hängender Request. Bei sauber garantierten Baumstrukturen bleibe ich bei UNION ALL; überall, wo Nutzer die Verschachtelung selbst ändern dürfen, nehme ich UNION.

Ein zweiter, sehr pragmatischer Riegel: eine Höhenbegrenzung in den Schritt schreiben.

    ...
    JOIN baum b ON k.eltern = b.id
   WHERE b.tiefe < 20

Zwanzig Ebenen hat kein echter Kategoriebaum. Was tiefer geht, ist ein Datenfehler – und den will man abgeschnitten sehen, nicht ausgereizt.

Der Bonus, an den kaum jemand denkt: Reihen erzeugen

Rekursive CTEs brauchen gar keine Tabelle. Das hier ist mein meistgenutzter Einzeiler für Auswertungen:

WITH RECURSIVE tage(tag) AS (
    SELECT date('now', '-6 days')
    UNION ALL
    SELECT date(tag, '+1 day') FROM tage WHERE tag < date('now')
)
SELECT tag FROM tage;

2026-07-23  2026-07-24  2026-07-25  2026-07-26  2026-07-27  2026-07-28  2026-07-29

Warum das nützlich ist: Ein GROUP BY tag über deine Bestellungen liefert nur Tage, an denen etwas passiert ist. Ein Diagramm braucht aber alle Tage, auch die mit null. Ein LEFT JOIN von dieser Datumsreihe auf deine Auswertung schließt genau diese Lücken – und du musst die leeren Tage nicht im Anwendungscode nachfüllen.

Wann ich es nicht mache

Wenn ein Baum sehr tief und sehr breit ist und ständig gelesen wird, ist eine gespeicherte Pfad-Spalte (/1/2/5/) plus ein LIKE '/1/2/%' schneller – dafür muss man sie beim Verschieben pflegen. Für alles, was ich baue, ist die rekursive CTE die bessere Seite dieses Tauschs: nichts zu pflegen, nichts, was auseinanderlaufen kann.

WITH RECURSIVE gibt es in SQLite, PostgreSQL, MySQL ab 8.0 und MariaDB ab 10.2. Der Aufbau ist überall gleich; unterschiedlich sind nur Kleinigkeiten wie group_concat gegenüber string_agg.

Du hast eine verschachtelte Struktur und lädst sie gerade mit einer Schleife aus der Datenbank? Das ist fast immer eine rekursive CTE, die noch nicht geschrieben wurde – schreib mir kurz, ich schaue mir deine Abfrage an.

Quellen

• SQLite: The WITH Clause – Recursive Common Table Expressions
• PostgreSQL: WITH Queries (Common Table Expressions) – Recursive Queries
• MySQL 8.0 Reference Manual: Recursive Common Table Expressions