Rekursive CTE: Was ist das und wofür braucht man sie?

Heute machen wir einen weiteren Schritt nach vorne und beschäftigen uns mit der Magie der Rekursion. Wenn du schon mal in einer Sprache mit Rekursion programmiert hast (zum Beispiel Python), hast du ungefähr eine Ahnung, worum es geht. Aber keine Sorge, falls das erstmal mysteriös klingt – wir gehen alles ganz ausführlich durch.

Rekursive CTE sind ein starkes Tool, um mit hierarchischen, baumartigen Datenstrukturen zu arbeiten, wie z.B. Organisationsstrukturen von Firmen, Familienstammbäumen oder Dateikatalogen.

Einfach gesagt: Das sind Ausdrücke, die sich "selbst aufrufen" können, um Schritt für Schritt alle Ebenen der Daten zu durchlaufen und zu verarbeiten.

Wichtige Eigenschaften von rekursiven CTE:

1. Sie benutzen das Schlüsselwort WITH RECURSIVE.
2. Rekursive CTE bestehen aus zwei Teilen:
   • Basis-Query: Definiert den Startpunkt (oder "Wurzel") der Rekursion.
   • Rekursiver Query: Verarbeitet die restlichen Daten, indem er das Ergebnis des vorherigen Schritts nutzt.

Der Ablauf eines rekursiven CTE ist wie das Hochsteigen einer Treppe:

• Erst stellst du dich auf die erste Stufe (das ist der Basis-Query).
• Dann gehst du auf die nächste Stufe, indem du das Ergebnis der ersten Stufe nutzt (rekursiver Query).
• Das wiederholt sich, bis die Stufen zu Ende sind (Abbruchbedingung erreicht).

Syntax eines rekursiven CTE

Schauen wir uns direkt ein Template-Beispiel an:

WITH RECURSIVE cte_name AS (
    -- Basis-Query
    SELECT column1, column2
    FROM table_name
    WHERE bedingung_für_basisfall

    UNION ALL

    -- Rekursiver Query
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON irgendeine_bedingung
    WHERE abbruchbedingung
)
SELECT * FROM cte_name;

Die Rolle von UNION und UNION ALL in rekursiven CTE

Jeder rekursive CTE muss die Operatoren UNION oder UNION ALL zwischen Basis- und rekursivem Teil verwenden.

Welchen Operator wählen: UNION oder UNION ALL?

Wenn du unsicher bist, was du nehmen sollst – nimm fast immer UNION ALL. Warum? Weil es schneller ist: Es fügt die Ergebnisse einfach zusammen, ohne zu prüfen, ob es Duplikate gibt. Das heißt: weniger Berechnungen, weniger Ressourcen und schnelleres Ergebnis.

Gerade bei rekursiven CTE ist das wichtig. Wenn du Hierarchien baust – zum Beispiel einen Kommentarbaum oder die Struktur von Untergebenen in einer Firma – brauchst du fast immer UNION ALL. Wenn du nur UNION nimmst, kann die Datenbank denken, dass manche Schritte schon da waren und schneidet einen Teil des Ergebnisses ab. Das zerstört die ganze Logik des Traversierens.

UNION solltest du nur nehmen, wenn du ganz sicher bist, dass Duplikate schädlich sind und raus müssen. Aber denk dran: Das ist immer ein Kompromiss zwischen Sauberkeit und Geschwindigkeit.

Beispiel für verschiedene Ansätze

-- UNION: Duplikate werden entfernt
SELECT 'A'
UNION
SELECT 'A';     -- Ergebnis: eine Zeile 'A'

-- UNION ALL: Duplikate bleiben erhalten
SELECT 'A'
UNION ALL
SELECT 'A';     -- Ergebnis: zwei Zeilen 'A'

Bei rekursiven Queries ist es sicherer, immer UNION ALL zu nehmen, damit keine wichtigen Schritte beim Traversieren der Struktur verloren gehen.

Schauen wir uns eine typische Aufgabe an: Wir haben eine Mitarbeitertabelle mit den Spalten employee_id, manager_id und name. Wir wollen eine Hierarchie aufbauen, beginnend beim Chef – der Person ohne Vorgesetzten (manager_id = NULL).

Angenommen, wir haben die Mitarbeitertabelle: employees

Wir müssen verstehen, wer wem unterstellt ist, und das Level jedes Mitarbeiters in der Struktur herausfinden. Das ist praktisch, wenn du z.B. einen Mitarbeiterbaum im Interface anzeigen oder einen Bericht über die Teamstruktur machen willst.

WITH RECURSIVE employee_hierarchy AS (
    -- Wir starten mit denen, die keinen Chef haben
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Wir fügen Untergebene hinzu und erhöhen das Level
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Das Ergebnis sieht so aus:

Diese Query zeigt anschaulich, wie man durch die Mitarbeiterhierarchie "laufen" kann – vom Chef bis zu den untersten Ebenen. Das Level ist praktisch zum Formatieren oder Visualisieren des Baums.

Beispiel: Produktkategorien

Stell dir jetzt vor, wir arbeiten mit einer Tabelle für Produktkategorien, wo jede Kategorie Unterkategorien haben kann, und die wiederum auch eigene Unterkategorien. Wie bauen wir den Kategoriebaum?

Tabelle categories

Rekursiver Query:

WITH RECURSIVE category_tree AS (
    -- Basisfall: Finde die Wurzelkategorien
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Rekursiver Teil: Finde Unterkategorien der aktuellen Kategorien
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Ergebnis:

Jetzt sehen wir den Kategoriebaum mit den Verschachtelungsebenen.

Warum sind rekursive CTE so cool?

Rekursive CTE sind eines der ausdrucksstärksten und mächtigsten SQL-Tools. Statt komplizierter verschachtelter Logik beschreibst du einfach, wo du startest (Basisfall) und wie es weitergeht (rekursiver Teil) – alles andere macht PostgreSQL für dich.

Solche Queries werden meistens genutzt, um Hierarchien zu durchlaufen: Mitarbeiter, Produktkategorien, Verzeichnisse auf der Festplatte, Graphen in sozialen Netzwerken. Sie sind easy zu erweitern: Wenn neue Daten in die Tabelle kommen, nimmt die Query sie automatisch mit. Das ist praktisch und skalierbar.

Aber es gibt auch Fallstricke. Achte unbedingt auf die Abbruchbedingungen – ohne die kann die Query in eine Endlosschleife laufen. Vergiss die Indizes nicht: Bei großen Tabellen können rekursive Queries ohne sie langsam werden. Und UNION ALL ist fast immer die beste Wahl, besonders bei Hierarchien, sonst verlierst du Schritte der Rekursion durch das Entfernen von Duplikaten.

Ein gut gebauter rekursiver CTE drückt komplexe Business-Logik in ein paar Zeilen aus – ohne Prozeduren, Schleifen oder extra Code. Das ist einer der Fälle, wo SQL nicht nur richtig, sondern auch schön funktioniert.

Typische Fehler bei rekursiven CTE

• Endlosrekursion: Wenn du keine korrekte Abbruchbedingung (WHERE) setzt, kann die Query in einer Schleife hängen bleiben.
• Zu viele Daten: Falsche Nutzung von UNION ALL erzeugt Duplikate.
• Performance: Rekursive Queries können bei großen Datenmengen schwerfällig sein. Indizes auf wichtigen Spalten (z.B. manager_id) helfen, das schneller zu machen.

Wann kommt man um rekursive Queries nicht herum?

Man denkt oft, rekursive Queries sind nur Theorie, aber in der Praxis begegnen sie dir ständig. Zum Beispiel:

• um Berichte über die Firmenstruktur oder Produktklassifikation zu bauen;
• um einen Verzeichnisbaum zu durchlaufen und alle Unterordner zu sammeln;
• um Graphen zu analysieren – soziale Verbindungen, Routen, Abhängigkeiten zwischen Tasks;
• um einfach komplexe Beziehungen zwischen Objekten lesbar darzustellen.

Wenn du eine Struktur durchlaufen musst, in der eins vom anderen abhängt, brauchst du fast immer WITH RECURSIVE.