CTEs mit WITH: Lesbarere Queries statt Subquery-Chaos

CTE-Grundsyntax: WITH ... AS (...)

Eine Common Table Expression (CTE) ist eine temporäre, benannte Ergebnismenge, die innerhalb einer einzigen SQL-Anweisung definiert und referenziert wird. Die Syntax beginnt mit dem Schlüsselwort WITH, gefolgt vom CTE-Namen, dem Schlüsselwort AS und der eigentlichen Abfrage in Klammern. Danach folgt die Hauptabfrage, die den CTE-Namen wie eine normale Tabelle oder View verwenden kann.

CTEs sind in MySQL seit Version 8.0 verfügbar und in MariaDB seit Version 10.2. In älteren MySQL-5.7-Umgebungen musste man auf korrelierte Subqueries oder temporäre Tabellen ausweichen — beides ist deutlich unhandlicher. Der wesentliche Vorteil einer CTE gegenüber einer anonymen Subquery ist die Benennbarkeit: Jeder Schritt einer komplexen Abfrage erhält einen aussagekräftigen Namen, der beschreibt, was er berechnet. Das macht komplexe Abfragen deutlich wartbarer und debuggbarer.

Der Begriff CTE wird im Deutschen manchmal als "gemeinsamer Tabellenausdruck" übersetzt, ist aber in der Praxis unter dem englischen Kürzel CTE geläufig. Eine CTE verhält sich syntaktisch wie eine temporäre View, die nur für die Dauer einer einzigen SQL-Anweisung existiert und nicht dauerhaft in der Datenbank gespeichert wird.

-- Basic CTE syntax: WITH name AS (query) SELECT ... FROM name
WITH active_customers AS (
  -- CTE: select all active customers on website 1
  SELECT entity_id, email, firstname, lastname, created_at
  FROM customer_entity
  WHERE is_active  = 1
    AND website_id = 1
)
-- Main query references the CTE like a regular table
SELECT
  ac.entity_id,
  ac.email,
  ac.firstname,
  ac.lastname,
  COUNT(so.entity_id)  AS order_count,
  SUM(so.grand_total)  AS lifetime_value
FROM active_customers   AS ac
LEFT JOIN sales_order   AS so ON so.customer_id = ac.entity_id
GROUP BY ac.entity_id, ac.email, ac.firstname, ac.lastname
ORDER BY lifetime_value DESC
LIMIT 20;
  

Mehrere CTEs in einer Abfrage verketten

Eine WITH-Klausel kann mehrere CTEs gleichzeitig definieren, getrennt durch Kommas. Spätere CTEs können dabei auf frühere referenzieren — das ermöglicht eine Kette klar benannter Berechnungsschritte, die von einfachen Filterschritten bis zu komplexen Aggregationen reicht.

Dieses Muster ist besonders nützlich für Reporting-Abfragen, die mehrere Aggregationen auf denselben Basisdaten durchführen. Statt die Basisdaten mehrfach in verschachtelten Subqueries zu wiederholen, definiert man sie einmal als CTE und referenziert diesen in allen nachfolgenden Schritten. Das Ergebnis ist eine Abfrage, die wie ein strukturierter Algorithmus gelesen werden kann: Schritt 1 ergibt X, Schritt 2 berechnet Y aus X, Schritt 3 leitet Z aus Y ab.

-- Chain of CTEs: each step builds on the previous one
WITH
-- Step 1: Base: orders from last 30 days
recent_orders AS (
  SELECT entity_id, customer_id, grand_total, status, created_at
  FROM sales_order
  WHERE created_at >= NOW() - INTERVAL 30 DAY
    AND customer_id IS NOT NULL
),
-- Step 2: Aggregate per customer (references recent_orders)
customer_totals AS (
  SELECT
    customer_id,
    COUNT(*)           AS order_count,
    SUM(grand_total)   AS total_spent,
    MAX(created_at)    AS last_order_date
  FROM recent_orders
  GROUP BY customer_id
),
-- Step 3: Classify customers (references customer_totals)
vip_candidates AS (
  SELECT
    customer_id,
    order_count,
    total_spent,
    last_order_date,
    CASE WHEN total_spent >= 500 THEN 'VIP' ELSE 'Standard' END AS segment
  FROM customer_totals
  WHERE order_count >= 2
)
-- Final: join CTE with customer master data
SELECT
  ce.email,
  ce.firstname,
  ce.lastname,
  vc.order_count,
  ROUND(vc.total_spent, 2) AS total_spent,
  vc.last_order_date,
  vc.segment
FROM vip_candidates  AS vc
JOIN customer_entity AS ce ON ce.entity_id = vc.customer_id
ORDER BY vc.total_spent DESC;
  

CTE vs. Subquery: Lesbarkeit und Performance im Vergleich

Eine häufige Frage ist, ob ein CTE schneller ist als eine äquivalente Subquery. Die ehrliche Antwort: Nicht automatisch. CTEs lösen in erster Linie ein Lesbarkeits- und Wartungsproblem, kein Performance-Problem. Für identische Logik erzeugt MySQL oft denselben Ausführungsplan — der Optimizer erkennt die Äquivalenz und behandelt CTE und Subquery gleich.

Es gibt jedoch Fälle, in denen MySQL einen CTE anders behandelt, nämlich wenn er materialisiert wird. Das kann die Performance verbessern (wenn die CTE-Ergebnisse zwischengespeichert werden und mehrfach genutzt werden) oder verschlechtern (wenn der Optimizer durch die Materialisierung Prädikate nicht mehr pushdown kann). Hier der direkte Vergleich derselben Logik als Subquery und als CTE:

-- SUBQUERY VERSION: deeply nested, hard to follow
SELECT
  ce.email,
  order_data.order_count,
  order_data.total_spent
FROM customer_entity AS ce
JOIN (
  SELECT
    customer_id,
    COUNT(*)         AS order_count,
    SUM(grand_total) AS total_spent
  FROM (
    SELECT customer_id, grand_total
    FROM sales_order
    WHERE status   = 'complete'
      AND store_id = 1
  ) AS filtered_orders
  GROUP BY customer_id
  HAVING COUNT(*) >= 3
) AS order_data ON order_data.customer_id = ce.entity_id
ORDER BY order_data.total_spent DESC;

-- CTE VERSION: same logic, each step has a descriptive name
WITH
filtered_orders AS (
  -- Step 1: filter relevant orders
  SELECT customer_id, grand_total
  FROM sales_order
  WHERE status   = 'complete'
    AND store_id = 1
),
order_aggregates AS (
  -- Step 2: aggregate per customer
  SELECT
    customer_id,
    COUNT(*)         AS order_count,
    SUM(grand_total) AS total_spent
  FROM filtered_orders
  GROUP BY customer_id
  HAVING order_count >= 3
)
-- Step 3: join with customer data
SELECT
  ce.email,
  oa.order_count,
  oa.total_spent
FROM order_aggregates AS oa
JOIN customer_entity  AS ce ON ce.entity_id = oa.customer_id
ORDER BY oa.total_spent DESC;

-- Use EXPLAIN to compare execution plans — they are often identical
EXPLAIN FORMAT=JSON
WITH filtered_orders AS (
  SELECT customer_id, grand_total
  FROM sales_order
  WHERE status = 'complete' AND store_id = 1
),
order_aggregates AS (
  SELECT customer_id, COUNT(*) AS cnt, SUM(grand_total) AS total
  FROM filtered_orders GROUP BY customer_id HAVING cnt >= 3
)
SELECT ce.email, oa.cnt, oa.total
FROM order_aggregates oa JOIN customer_entity ce ON ce.entity_id = oa.customer_id;
  

Rekursiver CTE: WITH RECURSIVE für Hierarchien

Das mächtigste Feature von CTEs ist die Rekursion. Ein rekursiver CTE, eingeleitet mit WITH RECURSIVE, kann sich selbst referenzieren und ist damit das geeignete Werkzeug für hierarchische Datenstrukturen wie Kategoriebäume, Organisationshierarchien oder Stücklisten.

Ein rekursiver CTE besteht immer aus zwei Teilen, verbunden durch UNION ALL: dem Ankermember (eine normale Abfrage für den Ausgangspunkt der Rekursion) und dem rekursiven Member (eine Abfrage, die den CTE selbst referenziert und die nächste Ebene der Hierarchie holt). MySQL führt den rekursiven Member so lange aus, bis keine neuen Zeilen zurückgegeben werden oder das Rekursionslimit erreicht ist (standardmäßig 1000, einstellbar mit SET SESSION cte_max_recursion_depth).

-- Generic recursive CTE pattern for tree traversal
WITH RECURSIVE tree AS (
  -- Anchor member: root node(s) — the starting point
  SELECT
    id,
    parent_id,
    name,
    0    AS depth,
    CAST(id AS CHAR(200)) AS path
  FROM nodes
  WHERE parent_id IS NULL   -- root nodes have no parent

  UNION ALL

  -- Recursive member: children of already-found nodes
  -- References the CTE itself (tree)
  SELECT
    n.id,
    n.parent_id,
    n.name,
    t.depth + 1,
    CONCAT(t.path, '/', n.id)  -- build breadcrumb path
  FROM nodes AS n
  JOIN tree  AS t ON t.id = n.parent_id
  -- MySQL stops when this produces no new rows
  -- Safety: SET SESSION cte_max_recursion_depth = 5000 for deep trees
)
SELECT id, parent_id, name, depth, path
FROM tree
ORDER BY path;
  

Magento catalog_category_entity: Baum mit rekursivem CTE

Magento speichert Kategorien in der Tabelle catalog_category_entity mit einem parent_id-Feld und einem path-Feld (z.B. "1/2/10/45"), das die vollständige Pfadhierarchie als String kodiert. Der rekursive CTE-Ansatz über parent_id ist der semantisch sauberste Weg, um Unterkategorie-Bäume abzufragen.

Ein typischer Anwendungsfall: Alle Unterkategorien einer bestimmten Elternkategorie finden — um z.B. alle Kategorie-IDs für eine Navigationsabfrage zu sammeln oder um festzustellen, welche Produkte über beliebig tiefe Unterkategorien erreichbar sind. Ohne rekursiven CTE müsste man entweder die path-Spalte mit LIKE-Operatoren abfragen (ungenau bei bestimmten ID-Konstellationen) oder in der Anwendungslogik iterieren.

-- Find all subcategories of category ID 10, including all nested levels
WITH RECURSIVE category_tree AS (
  -- Anchor: start from the specified root category
  SELECT
    entity_id,
    parent_id,
    path,
    level,
    position,
    0 AS depth
  FROM catalog_category_entity
  WHERE entity_id = 10

  UNION ALL

  -- Recursive: fetch children by matching parent_id = entity_id of found rows
  SELECT
    cce.entity_id,
    cce.parent_id,
    cce.path,
    cce.level,
    cce.position,
    ct.depth + 1
  FROM catalog_category_entity AS cce
  JOIN category_tree           AS ct ON ct.entity_id = cce.parent_id
)
SELECT entity_id, parent_id, level, depth, path
FROM category_tree
ORDER BY path;

-- Get all category IDs as a list (for use in JOINs or IN clauses)
WITH RECURSIVE category_tree AS (
  SELECT entity_id FROM catalog_category_entity WHERE entity_id = 10
  UNION ALL
  SELECT cce.entity_id
  FROM catalog_category_entity AS cce
  JOIN category_tree AS ct ON ct.entity_id = cce.parent_id
)
SELECT GROUP_CONCAT(entity_id ORDER BY entity_id) AS all_category_ids
FROM category_tree;
  

MySQL-Materialisierung: CTE als Optimizer-Fence

In MySQL 8.0 kann der Optimizer einen CTE materialisieren — er führt die CTE-Abfrage einmalig aus und speichert das Ergebnis in einer temporären Tabelle, bevor die Hauptabfrage darauf zugreift. Dieses Verhalten ist ein "Optimizer-Fence": Der Optimizer sieht den CTE als eigenständige Einheit und kann keine Prädikate aus der Hauptabfrage in den CTE "pushen".

Materialisierung ist ein zweischneidiges Schwert. Einerseits verhindert sie schlechte Ausführungsstrategien für den CTE selbst. Andererseits kann sie Optimierungen blockieren, die bei direktem Inlining möglich wären. MySQL entscheidet selbst anhand von Kostenmodell-Schätzungen, ob es einen CTE materialisiert oder inlined. In MySQL 8.0.22+ kann dieses Verhalten mit den Hints NO_MERGE() und MERGE() beeinflusst werden.

-- Inspect whether MySQL materializes or inlines a CTE
EXPLAIN FORMAT=JSON
WITH order_base AS (
  SELECT entity_id, customer_id, grand_total, status
  FROM sales_order
  WHERE store_id   = 1
    AND created_at >= '2025-01-01'
)
SELECT customer_id, COUNT(*), SUM(grand_total)
FROM order_base
WHERE status = 'complete'
GROUP BY customer_id;

-- In the JSON output, look for "materialized_from_subquery":
-- Present  -> MySQL created a temp table (optimizer fence active)
-- Absent   -> MySQL inlined the CTE (predicates pushed into CTE query)

-- Force materialization with NO_MERGE hint (useful when inlining produces bad plans)
EXPLAIN FORMAT=JSON
WITH order_base AS (
  SELECT /*+ NO_MERGE() */ entity_id, customer_id, grand_total, status
  FROM sales_order
  WHERE store_id = 1
)
SELECT customer_id, COUNT(*)
FROM order_base
WHERE status = 'complete'
GROUP BY customer_id;
  

CTE für Deduplizierung mit ROW_NUMBER

Eine sehr praktische Anwendung von CTEs ist die Deduplizierung von Datensätzen. Das Muster verwendet ROW_NUMBER() innerhalb des CTEs, um doppelte Zeilen zu nummerieren (PARTITION BY die Identitätsspalten, ORDER BY die Sortierung zur Bestimmung des "Gewinnerdatensatzes"). In der Hauptabfrage werden dann nur die Zeilen mit rn = 1 behalten.

Dieses Muster ist in Magento-Kontexten häufig relevant — etwa wenn EAV-Abfragen mehrere Attributeinträge für dasselbe Produkt liefern, wenn Importprozesse Duplikate erzeugen oder wenn Bereinigungsabfragen für alte Integrations-Stubs benötigt werden. In MySQL ist dieses Muster die sauberste Alternative zu den in anderen Systemen verfügbaren DISTINCT ON-Konstrukten.

-- Deduplicate customer records: keep the newest account per email + website
WITH ranked_customers AS (
  SELECT
    entity_id,
    email,
    website_id,
    created_at,
    is_active,
    -- Partition by email+website, take the most recently created record
    ROW_NUMBER() OVER (
      PARTITION BY email, website_id
      ORDER BY created_at DESC
    ) AS rn
  FROM customer_entity
)
-- Keep only the "winner" per group
SELECT entity_id, email, website_id, created_at
FROM ranked_customers
WHERE rn = 1
ORDER BY email;

-- Identify the actual duplicate rows (rn > 1 = extra copies to review/delete)
WITH ranked_customers AS (
  SELECT
    entity_id,
    email,
    website_id,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email, website_id
      ORDER BY created_at DESC
    ) AS rn
  FROM customer_entity
)
SELECT entity_id, email, website_id, created_at, rn
FROM ranked_customers
WHERE rn > 1
ORDER BY email, created_at;
  

CTEs zum schrittweisen Debugging von Abfragen

CTEs sind außerordentlich nützlich beim Debugging komplexer Abfragen. Die Strategie: Die Abfrage wird schrittweise aufgebaut, indem zunächst nur der erste CTE mit einer einfachen SELECT * FROM cte_name-Hauptabfrage ausgeführt und das Zwischenergebnis überprüft wird. Stimmt das Ergebnis, wird der nächste CTE hinzugefügt, und so weiter.

Bei einer äquivalenten verschachtelten Subquery-Struktur ist dieses schrittweise Testen nicht möglich — man müsste die Subquery aus dem Kontext herausnehmen und separat ausführen, was bei tief verschachtelten Abfragen fehleranfällig ist. CTEs machen den Debugging-Workflow systematischer. Wenn ein CTE-Schritt unerwartete Ergebnisse liefert, ist der Fehler auf diesen einen, klar abgegrenzten Schritt eingegrenzt.

Produkte aller Unterkategorien finden: komplettes Beispiel

Das folgende Beispiel kombiniert einen rekursiven CTE für den Kategoriebaum mit einer anschließenden Produktabfrage. Das Ergebnis sind alle Produkte, die in einer Kategorie oder einer ihrer Unterkategorien assigned sind — ein Muster, das in Magento-Abfragen häufig gebraucht wird, aber ohne rekursiven CTE nur umständlich formulierbar ist.

-- Complete example: find all products in category 10 and all its nested subcategories
WITH RECURSIVE subcategories AS (
  -- Anchor: start category
  SELECT entity_id AS category_id, path, level
  FROM catalog_category_entity
  WHERE entity_id = 10

  UNION ALL

  -- Recursive: all nested child categories
  SELECT cce.entity_id, cce.path, cce.level
  FROM catalog_category_entity AS cce
  JOIN subcategories            AS sc ON sc.category_id = cce.parent_id
),
-- Collect distinct product IDs across all found categories
category_products AS (
  SELECT DISTINCT ccp.product_id
  FROM catalog_category_product AS ccp
  JOIN subcategories            AS sc ON sc.category_id = ccp.category_id
)
-- Final: fetch product details with name and price
SELECT
  cpe.entity_id,
  cpe.sku,
  cpev.value           AS product_name,
  cpep.final_price
FROM category_products           AS cp
JOIN catalog_product_entity      AS cpe  ON cpe.entity_id = cp.product_id
-- Product name (attribute_id varies; 73 is typical for standard Magento installs)
LEFT JOIN catalog_product_entity_varchar AS cpev
  ON cpev.entity_id     = cpe.entity_id
 AND cpev.attribute_id  = 73
 AND cpev.store_id      = 0
-- Price from the price index for website 1, guest customer group
LEFT JOIN catalog_product_index_price AS cpep
  ON cpep.entity_id          = cpe.entity_id
 AND cpep.website_id         = 1
 AND cpep.customer_group_id  = 0
ORDER BY cpe.sku;
  

Zusammenfassung

CTEs mit der WITH-Klausel sind eines der nützlichsten Features in modernem SQL. Sie lösen das Lesbarkeitsproblem verschachtelter Subqueries, indem sie jeden Schritt einer komplexen Abfrage in eine benannte, eigenständige Einheit zerlegen. Rekursive CTEs sind das standardisierte SQL-Werkzeug für Hierarchie-Traversierung — unverzichtbar für Magento-Kategoriebäume. Materialisierungsverhalten und Optimizer-Fence-Effekte müssen beim Performance-Tuning verstanden werden. CTEs machen komplexe Abfragen nicht automatisch schneller, aber deutlich besser wartbar und debuggbar.

FAQ: CTEs und WITH in MySQL