Common Table Expressions, Recursive CTEs, and Hierarchical Data

Learning outcomes

A ServiceHub manager wants a report that starts at the Operations team and expands through North, South, North-East and North-West. A flat self-join works for one known depth but becomes brittle when organizational levels change. Recursive common table expressions (CTEs) solve this class of problem by giving the query a named result that can reference itself. The power is real, but so are the failure modes: a missing termination condition can recurse until a safety limit, a cycle can revisit nodes, and a too-narrow anchor expression can constrain the recursive column type.

This lesson treats a CTE as a query-scoped relation, not a stored table. Nonrecursive CTEs improve decomposition and can be referenced by the surrounding statement. Recursive CTEs consist of an anchor member that seeds rows and a recursive member that expands from rows already produced. MariaDB also exposes max_recursive_iterations as a guardrail and supports a CYCLE ... RESTRICT form for cycle suppression in supported syntax.

1. A nonrecursive CTE names a result for one statement

A nonrecursive CTE defined with WITH name AS (...) exists only for the statement that follows. It can make a query easier to review because each stage has a meaningful name. It is not automatically a materialized temporary table; the optimizer may transform the query according to current rules.

WITH expensive_work AS (  SELECT work_order_id, technician_id, labor_cost  FROM work_orders  WHERE labor_cost >= 100)SELECT t.display_name,       COUNT(e.work_order_id) AS expensive_orders,       SUM(e.labor_cost) AS total_costFROM technicians AS tLEFT JOIN expensive_work AS e  ON e.technician_id = t.technician_idGROUP BY t.technician_id, t.display_nameORDER BY t.technician_id;

The CTE gives reviewers a clean boundary: first define “expensive work,” then aggregate it by technician. If the query becomes performance-sensitive, inspect the actual plan; do not assume the word WITH means a physical intermediate table exists.

2. Recursive CTE = anchor + recursive member

The anchor member emits the starting row or rows. The recursive member joins the base data to the CTE result from the previous iteration and emits the next layer. UNION ALL is common because it preserves rows without duplicate-elimination overhead; if duplicates represent a correctness problem, fix the graph/traversal rule rather than expecting UNION DISTINCT to be a universal cycle solution.

WITH RECURSIVE team_tree AS (  SELECT team_id,         parent_team_id,         team_name,         0 AS depth,         CAST(team_name AS CHAR(400)) AS path  FROM teams  WHERE team_id = 1  UNION ALL  SELECT child.team_id,         child.parent_team_id,         child.team_name,         parent.depth + 1,         CONCAT(parent.path, ' > ', child.team_name)  FROM teams AS child  JOIN team_tree AS parent    ON child.parent_team_id = parent.team_id)SELECT team_id, parent_team_id, team_name, depth, pathFROM team_treeORDER BY depth, team_id;

The explicit CAST on path is intentional. MariaDB determines recursive CTE column types from the nonrecursive part. If the anchor produces a narrow string and later recursive concatenation grows beyond that width, results can be truncated or fail depending on context/version behavior. Design the anchor type wide enough for the recursive result you intend to build.

3. Termination is part of correctness

A hierarchy traversal should stop because the recursive join eventually finds no children. A malformed recursive member that keeps reproducing the same row has no natural termination. MariaDB limits recursive work with max_recursive_iterations, which is a safety net—not a substitute for correct graph logic.

SHOW VARIABLES LIKE 'max_recursive_iterations';-- Do not run against a large/prod dataset. Intentionally broken logic:WITH RECURSIVE bad_tree AS (  SELECT team_id, 0 AS depth  FROM teams  WHERE team_id=1  UNION ALL  SELECT b.team_id, b.depth+1  FROM bad_tree AS b  WHERE b.depth < 5)SELECT * FROM bad_tree;

This deliberately bounded broken example repeats the same team rather than discovering children. The explicit depth < 5 makes it safe enough to inspect locally. Without a logical or explicit boundary, the server eventually relies on the configured recursion limit and the query fails or stops according to the documented behavior. A production traversal should have a correctness-based termination rule and an operational guardrail.

4. Cycles turn a tree query into a graph problem

Real organizational or category data can contain a cycle after bad data or a migration mistake—for example A reports to B while B indirectly reports to A. A recursive join that assumes a tree can revisit the same identifiers forever. One portable teaching pattern carries a path of visited IDs and rejects an ID already present. MariaDB documentation also describes CYCLE ... RESTRICT support for cycle suppression; verify the exact syntax/version before adopting it in shared SQL.

WITH RECURSIVE team_tree AS (  SELECT team_id,         parent_team_id,         team_name,         0 AS depth,         CAST(team_id AS CHAR(400)) AS visited_ids  FROM teams  WHERE team_id = 1  UNION ALL  SELECT child.team_id,         child.parent_team_id,         child.team_name,         parent.depth + 1,         CONCAT(parent.visited_ids, ',', child.team_id)  FROM teams AS child  JOIN team_tree AS parent    ON child.parent_team_id = parent.team_id  WHERE FIND_IN_SET(child.team_id, parent.visited_ids)=0    AND parent.depth < 20)SELECT * FROM team_treeORDER BY depth, team_id;

This string-path technique is pedagogical and adequate for the tiny lab; it is not a universal high-scale graph engine. For large graphs, evaluate data-model constraints, path representation, dedicated graph processing or application logic. The important design rule is that cycles are a data/graph concern you must address deliberately.

5. Traversal order and display order are separate

Recursive evaluation does not automatically promise the visual order a user expects. Build explicit metadata such as depth, path text or a sortable path key, and use ORDER BY in the outer query to present breadth-like or depth/path-like results. A hierarchy report should not rely on the incidental order rows happened to be generated.

6. Plan evidence and materialization considerations

CTEs participate in optimization. Nonrecursive CTEs and derived-table-like constructs may be merged or materialized depending on query shape and optimizer rules. Recursive CTEs require iterative processing by definition. The practical rule is to inspect the target version with the real query rather than assuming a CTE is always faster or always slower than an equivalent derived table.

EXPLAIN FORMAT=JSONWITH recent_work AS (  SELECT * FROM work_orders WHERE opened_at >= '2026-08-02')SELECT region, COUNT(*)FROM recent_workGROUP BY region;ANALYZE FORMAT=JSONWITH RECURSIVE tree AS (  SELECT team_id, parent_team_id, 0 depth FROM teams WHERE team_id=1  UNION ALL  SELECT c.team_id, c.parent_team_id, p.depth+1  FROM teams c JOIN tree p ON c.parent_team_id=p.team_id)SELECT * FROM tree;

ANALYZE actually executes the SELECT and reports runtime statistics. Keep the lab small and read the plan as evidence: row estimates, actual row counts, loops and materialized/temporary structures are diagnostic signals, not an API contract that future versions must reproduce exactly.

7. Failure drill and verification checklist

1. Run the nonrecursive CTE and confirm its result matches an equivalent inline derived-table query.
2. Traverse the team hierarchy from team 1 and verify five teams appear exactly once.
3. Remove the explicit CAST from the path in a disposable test and reason about why width inference can become a problem as paths grow.
4. Run the bounded broken recursion and explain why it repeats the anchor.
5. Record @@max_recursive_iterations; do not change a production value just to make a bad query finish.
6. Add the visited-ID cycle defense and explain both its usefulness and scalability limits.
7. Change only the outer ORDER BY and show that presentation order changes without changing which hierarchy rows are discovered.

8. Migration and graph-boundary judgment

A recursive query can be syntactically portable while still behaving differently across database products or versions. Before moving a hierarchy from another engine, test the exact recursive column types, recursion guardrail, cycle syntax, NULL behavior, optimizer plan, and ordering assumptions. Do not translate a proprietary hierarchy feature into WITH RECURSIVE and call the migration complete merely because the first sample returns the same names.

Also decide where recursion belongs architecturally. A modest organization tree, bill of materials, category tree, or parent-child routing structure is a natural relational use case. A graph with millions of highly connected edges, repeated shortest-path queries, or complex path constraints may deserve a different representation or processing boundary. MariaDB can traverse recursive relations, but the database should not be forced to become a graph engine simply because recursive syntax exists.

9. Summary and bridge

CTEs improve query decomposition; recursive CTEs add iterative traversal. Safe recursion requires a correct anchor, an expanding recursive member, termination logic, cycle awareness, suitable inferred column types and a depth/resource guardrail. The outer query still controls deterministic presentation. Optimizer behavior is observable but version-sensitive.

The next lesson performs analytics without collapsing rows. Window functions will let ServiceHub rank technicians, compute running totals and select top-N work orders per region—but window frame defaults introduce a new kind of subtle correctness bug when ORDER BY keys have ties.