Zum Inhalt

Common Table Expressions (CTEs) & Rekursive Hierarchien: Graphtraversierung in SQL

Erstellt: 08.11.2026  ·  Zuletzt aktualisiert: 08.11.2026  ·  Nejat Hakan

Management Summary: Tief verschachtelte Unterabfragen verschleiern die Geschäftslogik, erschweren das Debugging und zwingen Anwendungsentwickler nicht selten in ineffiziente „N+1“-Schleifen im Applikationscode. Common Table Expressions (WITH-Klauseln) lösen dieses Problem, indem sie komplexe Abfragen in sequenzielle, modulare Pipelines zerlegen. Darüber hinaus verwandelt WITH RECURSIVE relationale Datenbank-Engines in leistungsfähige Graphen-Traversierungs-Systeme: Organigramme, Netzwerk-Topologien und Stücklisten-Bäume lassen sich in einem einzigen atomaren Roundtrip mit nativer Zyklen-Erkennung abfragen.

  • Modulare Abfrage-Pipelines

    Verschachtelte Subqueries in lesbare Stufen zerlegen; Ausführungskontrolle über MATERIALIZED vs. NOT MATERIALIZED steuern.

  • Rekursive Graphen-Traversierung

    Hierarchien beliebiger Tiefe (Verzeichnisbäume, Netzwerk-Hops, Kategorie-Pfade) in rein mengentheoretischem SQL durchlaufen.

  • Endlosschleifen & Zyklen-Schutz

    Endlose Schleifen in zyklischen Graphen mit dem PostgreSQL-14+-Standard CYCLE oder Array-Pfadverfolgung deterministisch unterbinden.

  • Datenverändernde CTEs

    Atomare Archivierung ohne Downtime: DELETE ... RETURNING und INSERT in einer einzigen nebenläufigen Transaktion kombinieren.

1. Standard Common Table Expressions (WITH)

Eine Common Table Expression definiert eine temporäre, benannte Ergebnismenge, die ausschließlich für die Dauer der Ausführung der Hauptanweisung (SELECT, INSERT, UPDATE oder DELETE) existiert.

-- Lesbare Pipeline: Die Top-5-Umsatzkunden unter den Großunternehmen ermitteln
WITH enterprise_customers AS (
    SELECT customer_id, company_name, email
    FROM customers
    WHERE company_name ILIKE '%Corp%' OR company_name ILIKE '%Enterprise%'
),
customer_spending AS (
    SELECT 
        o.customer_id,
        COUNT(o.order_id) AS order_count,
        SUM(oi.quantity * oi.unit_cents) AS total_spent_cents
    FROM orders o
    INNER JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'PAID'
    GROUP BY o.customer_id
)
SELECT 
    ec.company_name,
    ec.email,
    cs.order_count,
    ROUND(cs.total_spent_cents / 100.0, 2) AS total_spent_eur
FROM enterprise_customers ec
INNER JOIN customer_spending cs ON cs.customer_id = ec.customer_id
ORDER BY cs.total_spent_cents DESC
LIMIT 5;

Optimizer-Semantik: MATERIALIZED vs. NOT MATERIALIZED

In älteren PostgreSQL-Versionen fungierten CTEs als strikte Optimierungs-Barrieren (Optimization Fences): Die Engine berechnete die CTE immer vollständig in eine temporäre Zwischentabelle im Arbeitsspeicher, bevor die äußere Abfrage startete. Seit PostgreSQL 12+ kann der Query-Planner einfache CTEs jedoch inlinen (wie Subqueries behandeln), um WHERE-Bedingungen nach unten durchzureichen und Indizes optimal zu nutzen.

Dieses Verhalten lässt sich explizit steuern:

-- Erzwingt die einmalige Berechnung in einen temporären Puffer:
WITH expensive_calculation AS MATERIALIZED (
    SELECT heavy_function(x) AS val FROM large_table
)
SELECT * FROM expensive_calculation WHERE val > 100;

-- Weist den Optimizer an, Filter nach unten durchzureichen:
WITH customer_lookup AS NOT MATERIALIZED (
    SELECT customer_id, email FROM customers
)
SELECT * FROM orders o JOIN customer_lookup c ON c.customer_id = o.customer_id;

2. WITH RECURSIVE: Die Mechanik hierarchischer Abfragen

Eine rekursive CTE besteht aus drei Kernkomponenten: Einem Ankerelement (Anchor Member), einem rekursiven Element (Recursive Member) – verknüpft via UNION ALL – sowie einer Abbruchbedingung:

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    ANCHOR["1. Ankerelement<br/>Ermittelt Startzeilen<br/>Initialisiert Menge"]
    RECUR["2. Rekursives Element<br/>Verknüpft Arbeitsmenge<br/>Erzeugt Zwischenmenge"]
    DEC{"Zwischenmenge<br/>leer?"}
    FINAL["3. Endergebnis<br/>Vereinigt alle Ebenen<br/>zu Gesamtergebnis"]

    ANCHOR --> RECUR
    RECUR --> DEC
    DEC -->|Nein: Daten vorhanden| RECUR
    DEC -->|Ja: 0 Zeilen| FINAL

Der interne Ausführungszyklus der Engine

  1. Arbeitsmenge initialisieren: Die Engine führt die Anker-Abfrage aus und schreibt die Ergebniszeilen sowohl in die finale Ausgabemenge als auch in eine interne temporäre Working Table.
  2. Rekursiven Schritt ausführen: Die Engine führt die rekursive Abfrage aus. Dabei wird der Name der CTE durch den aktuellen Inhalt der Working Table ersetzt.
  3. Austausch & Anhängen: Liefert der rekursive Schritt neue Zeilen zurück, werden diese an das Gesamtergebnis angehängt, die Working Table wird mit den neuen Zeilen überschrieben und der Zyklus wiederholt sich.
  4. Terminierung: Sobald der rekursive Schritt null neue Zeilen erzeugt, bricht die Schleife deterministisch ab.

3. Hands-on-Labor: Netzwerk-Topologie traversieren

Im Linux-Betrieb bilden Infrastruktur-Abhängigkeiten hierarchische Bäume. In diesem Labor modellieren wir die Abhängigkeitskette von Switches über Hypervisor bis zu containerisierten Datenbanken:

-- Datei: /opt/db-ops/infrastructure_tree_lab.sql
BEGIN;

CREATE TABLE infra_topology (
    node_id         INT PRIMARY KEY,
    parent_node_id  INT REFERENCES infra_topology(node_id),
    node_name       VARCHAR(64) NOT NULL,
    role            VARCHAR(32) NOT NULL
);

INSERT INTO infra_topology (node_id, parent_node_id, node_name, role) VALUES
    (1, NULL, 'core-spine-01',   'Datacenter Switch'),
    (2, 1,    'leaf-tor-01',     'Top-of-Rack Switch'),
    (3, 2,    'hv-kvm-node01',   'Debian 12 Hypervisor'),
    (4, 3,    'vm-db-primary',   'PostgreSQL 17 VM'),
    (5, 4,    'pg-tenant-app1',  'Application Database'),
    (6, 2,    'hv-kvm-node02',   'Compute Hypervisor'),
    (7, 6,    'vm-ingress-01',   'Nginx Edge Gateway');

-- Baum von der Wurzel bis zu den Blättern mit Tiefe und Pfad durchlaufen
WITH RECURSIVE topology_tree AS (
    -- 1. Anker: Obersten Datacenter-Switch finden
    SELECT
        node_id,
        parent_node_id,
        node_name,
        role,
        1 AS depth_level,
        node_name::TEXT AS path_trace
    FROM infra_topology
    WHERE parent_node_id IS NULL

    UNION ALL

    -- 2. Rekursiver Schritt: Kindknoten an Elternknoten binden
    SELECT
        child.node_id,
        child.parent_node_id,
        child.node_name,
        child.role,
        parent.depth_level + 1,
        parent.path_trace || ' → ' || child.node_name
    FROM infra_topology child
    INNER JOIN topology_tree parent ON parent.node_id = child.parent_node_id
)
SELECT 
    depth_level,
    REPEAT('  ', depth_level - 1) || '└─ ' || node_name AS tree_view,
    role,
    path_trace
FROM topology_tree
ORDER BY path_trace;

COMMIT;

Ausgabe der Abfrage

 depth_level | tree_view                        | role                  | path_trace
-------------+----------------------------------+-----------------------+-----------------------------------------------------------
           1 | └─ core-spine-01                 | Datacenter Switch     | core-spine-01
           2 |   └─ leaf-tor-01                 | Top-of-Rack Switch    | core-spine-01 → leaf-tor-01
           3 |     └─ hv-kvm-node01             | Debian 12 Hypervisor  | core-spine-01 → leaf-tor-01 → hv-kvm-node01
           4 |       └─ vm-db-primary           | PostgreSQL 17 VM      | core-spine-01 → leaf-tor-01 → hv-kvm-node01 → vm-db-primary
           5 |         └─ pg-tenant-app1        | Application Database  | ... → vm-db-primary → pg-tenant-app1
           3 |     └─ hv-kvm-node02             | Compute Hypervisor    | core-spine-01 → leaf-tor-01 → hv-kvm-node02
           4 |       └─ vm-ingress-01           | Nginx Edge Gateway    | ... → hv-kvm-node02 → vm-ingress-01

4. Endlosschleifen verhindern: Zyklen-Erkennung

Enthalten Daten zyklische Verweise (A → B → C → A), geraten Standard-Rekursionen in eine Endlosschleife, die Speicher und CPU blockiert, bis ein Statement-Timeout greift.

Standard-Syntax via CYCLE (PostgreSQL 14+)

PostgreSQL bietet standardkonforme Syntax zur automatischen Zyklen-Erkennung:

WITH RECURSIVE graph_walk AS (
    SELECT node_id, parent_node_id, node_name
    FROM infra_topology
    WHERE node_id = 1

    UNION ALL

    SELECT c.node_id, c.parent_node_id, c.node_name
    FROM infra_topology c
    JOIN graph_walk p ON p.node_id = c.parent_node_id
)
CYCLE node_id SET is_cycle USING path_history
SELECT * FROM graph_walk WHERE NOT is_cycle;

5. Datenverändernde CTEs: Atomare Archivierung

CTEs können DELETE-, UPDATE- oder INSERT-Statements mit einer RETURNING-Klausel enthalten. Dies ermöglicht das Verschieben von Daten zwischen Tabellen in einem einzigen atomaren Schritt ohne zeitraubende Multi-Roundtrip-Sperren:

-- Datei: /opt/db-ops/atomic_log_archival.sql
-- Verarbeitete Logs älter als 30 Tage atomar löschen und ins Cold-Storage überführen
WITH purged_logs AS (
    DELETE FROM audit_logs_hot
    WHERE created_at < NOW() - INTERVAL '30 days'
      AND processed = TRUE
    RETURNING log_id, event_type, payload, created_at
)
INSERT INTO audit_logs_cold (log_id, event_type, payload, archived_at)
SELECT log_id, event_type, payload, NOW()
FROM purged_logs;