So funktionieren rekursive CTEs
Basisfall plus rekursiver Schritt
So funktionieren rekursive CTEs ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 1 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Was ist eine rekursive CTE?
Eine rekursive CTE ist eine Common Table Expression, die auf sich selbst verweist. Damit können Sie Abfragen schreiben, die einen Schritt wiederholen, bis eine Bedingung erfüllt ist — ähnlich wie eine Schleife, aber als reines SQL formuliert.
Rekursive CTEs werden mit dem Schlüsselwort WITH RECURSIVE definiert und eignen sich ideal zum Durchlaufen hierarchischer oder graphähnlicher Daten wie Organigramme, Verzeichnisbäume und Stücklistenstrukturen.
Die zweiteilige Struktur
Jede rekursive CTE besteht aus genau zwei Teilen, die durch UNION ALL getrennt sind:
1. Basisfall — ein nicht rekursives SELECT, das die Ausgangszeilen zurückgibt.
2. Rekursiver Schritt — ein SELECT, das die CTE wieder mit sich selbst verknüpft und die nächste Zeilenebene erzeugt.
Die Engine führt den rekursiven Schritt weiter aus und sammelt Ergebnisse, bis keine neuen Zeilen mehr erzeugt werden.
WITH RECURSIVE cte_name AS (
-- Base case
SELECT ...
UNION ALL
-- Recursive step (references cte_name)
SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;Von 1 bis 5 zählen
Die einfachste rekursive CTE zählt Zahlen. Der Basisfall setzt den Wert 1 als Startwert. Der rekursive Schritt addiert in jeder Iteration 1. Die WHERE-Klausel innerhalb des rekursiven Schritts dient als Abbruchbedingung — ohne sie würde die Abfrage endlos laufen.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;Schrittweise Ausführung
So verarbeitet die Engine die Zähler-CTE Iteration für Iteration:
Iteration 0 (Basisfall): gibt {1} zurück.
Iteration 1: wendet den rekursiven Schritt auf {1} an und gibt {2} zurück.
Iteration 2: wendet den rekursiven Schritt auf {2} an und gibt {3} zurück.
Iteration 3, 4: gibt zunächst {4} und dann {5} zurück.
Iteration 5: WHERE n < 5 ist für n=5 falsch, daher werden null Zeilen zurückgegeben. Die Abfrage endet.
Alle gesammelten Zeilen — 1, 2, 3, 4, 5 — bilden das Endergebnis.
Eine Hierarchietabelle einrichten
Rekursive CTEs spielen ihre Stärken bei Tabellen mit Selbstreferenz aus. Erstellen Sie eine employees-Tabelle, in der jeder Mitarbeiter eine optionale manager_id hat, die auf dieselbe Tabelle verweist.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);Die Hierarchie durchlaufen
Nun können wir die gesamte Berichtslinie ausgehend vom CEO (Alice, id=1) durchlaufen. Der Basisfall wählt Alice aus; der rekursive Schritt findet alle Mitarbeiter, deren manager_id mit einer bereits in der CTE vorhandenen ID übereinstimmt.
Das Ergebnis enthält jeden von Alice aus erreichbaren Mitarbeiter, unabhängig davon, wie tief der Baum ist.
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;Den Pfad verfolgen
Eine häufige Erweiterung ist der Aufbau einer Pfadzeichenfolge, die die vollständige Kette von der Wurzel zu jedem Knoten zeigt. Beim Abstieg in der Rekursion verketten wir Namen, getrennt durch ' -> '.
So lassen sich Breadcrumb-Navigationen leicht anzeigen oder tiefe Hierarchien debuggen.
WITH RECURSIVE org_tree AS (
SELECT id, name, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.path || ' -> ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;Die Rekursionstiefe begrenzen
Sehr tiefe oder zyklische Daten können dazu führen, dass eine rekursive CTE sehr lange läuft. Zwei sichere Vorgehensweisen:
1. Tiefe verfolgen und eine WHERE-Klausel hinzufügen — WHERE depth < 10 stellt sicher, dass Sie nie über 10 Ebenen hinausgehen.
2. Eine Spalte zur Zykluserkennung verwenden — einige Datenbanken (PostgreSQL 14+) bieten die Syntax CYCLE, um wiederholte Knotenbesuche automatisch zu erkennen.
WITH RECURSIVE org_tree AS (
SELECT id, name, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;UNION oder UNION ALL in rekursiven CTEs
Der rekursive Schritt verwendet fast immer UNION ALL, nicht UNION. Hier ist der Grund:
UNION entfernt nach jeder Iteration Duplikate, indem es die gesamte Ergebnismenge vergleicht — das ist äußerst teuer und kann die Semantik von Graphen verändern, in denen derselbe Knoten legitimerweise über mehrere Pfade erreicht wird.
UNION ALL behält alle Zeilen ohne Deduplizierung bei, was sowohl schneller als auch für das Durchlaufen von Bäumen korrekt ist. Verwenden Sie UNION nur, wenn Sie Duplikate gezielt entfernen müssen und die damit verbundenen Leistungskosten kennen.
Eine Datumsfolge erzeugen
Rekursive CTEs eignen sich auch zum Erzeugen von Datumsfolgen. Dieses Beispiel erzeugt jeden Tag einer bestimmten Woche — ein Muster, das häufig zum Erstellen von Kalenderberichten oder zum Schließen von Lücken in Zeitreihendaten verwendet wird.
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM date_series
WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;Alle Untergebenen eines Managers finden
Sie können den Basisfall mit jedem beliebigen Knoten initialisieren — nicht nur mit der Wurzel. Hier beginnen wir bei Bob (id=2) und finden alle Personen, die direkt oder indirekt an ihn berichten.
Dieses Muster eignet sich für Berechtigungsprüfungen, Aggregationen für Teilbäume oder um Dashboards auf eine einzelne Abteilung zu begrenzen.
WITH RECURSIVE subordinates AS (
SELECT id, name
FROM employees
WHERE id = 2
UNION ALL
SELECT e.id, e.name
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;Kurztest
Testen Sie Ihr Verständnis davon, wie rekursive CTEs funktionieren.
Zusammenfassung der Lektion
In dieser Lektion haben Sie gelernt, wie rekursive CTEs funktionieren:
Struktur: Jede rekursive CTE besteht aus einem Basisfall (Ausgangszeilen), der über UNION ALL mit einem rekursiven Schritt (einem SELECT mit Selbstreferenz) verbunden wird.
Abbruch: Die Engine wiederholt den rekursiven Schritt und sammelt Ergebnisse, bis der Schritt null Zeilen zurückgibt.
Typische Anwendungen: Organigramme und Verzeichnisbäume durchlaufen, Zahlen- oder Datumsfolgen erzeugen, Pfade berechnen und alle Knoten in einem Teilbaum finden.
Sicherheitstipps: Fügen Sie immer eine Abbruchbedingung (Tiefenbegrenzung oder Zyklusschutz) ein und bevorzugen Sie aus Leistungsgründen UNION ALL gegenüber UNION.
Häufig gestellte Fragen
Ist die Lektion „So funktionieren rekursive CTEs“ kostenlos?
Ja — der vollständige Text von „So funktionieren rekursive CTEs“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Was lerne ich in „So funktionieren rekursive CTEs“?
Basisfall plus rekursiver Schritt Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.
Brauche ich Erfahrung, um SQL Academy zu starten?
Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 4.
Wie lange dauert die Lektion „So funktionieren rekursive CTEs“?
Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.
Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?
Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.
Alle Lektionen in diesem Kurs
- So funktionieren rekursive CTEs
- Einen Kategoriebaum durchlaufen
- Sequenzen und Reihen erzeugen
- Endlosschleifen vermeiden