Common Table Expressions, Recursive CTEs, and Hierarchical Queries

Learning outcomes

A common table expression (CTE) gives a name to a query result for the duration of one statement. Ordinary CTEs can make multi-stage queries easier to read; recursive CTEs can traverse an adjacency-list hierarchy such as the ServiceHub supervisor tree. Recursion, however, is a controlled loop expressed in relational terms. A missing termination rule, a cycle in the data, or a path column that is too narrow can turn a simple hierarchy query into an error or runaway workload.

Observe the hierarchy contract before recursing

Recursive SQL is only as trustworthy as the base relation it traverses. Confirm the self-referencing foreign key and the current recursion guardrail before the first hierarchy query.

SHOW CREATE TABLE technicians;SELECT @@SESSION.cte_max_recursion_depth AS session_cte_limit;SELECT technician_id, technician_name, supervisor_idFROM techniciansORDER BY technician_id;

The foreign key proves that a non-NULL supervisor ID points at an existing technician. It does not prove acyclicity, maximum depth, or that a root exists. Those are separate invariants that the later failure drill makes observable.

Ordinary CTEs: name the stage, not the storage mechanism

An ordinary CTE is scoped to the statement containing its WITH clause. Its main pedagogical advantage is that it names an intermediate relation, which can make a complex query easier to review. It does not create a persistent table and does not imply a specific physical materialization strategy.

USE servicehub_query_lab;WITH open_by_customer AS (  SELECT customer_id, COUNT(*) AS open_orders  FROM work_orders  WHERE status='open'  GROUP BY customer_id)SELECT c.customer_id,       c.customer_name,       o.open_ordersFROM customers AS cJOIN open_by_customer AS o  ON o.customer_id = c.customer_idORDER BY c.customer_id;

This is semantically similar to the derived-table example in Lesson 3. Choose the form that makes the stages easiest to reason about, then inspect optimizer behavior only when performance work requires it.

Recursive CTE anatomy: anchor, recursive member, stop condition

A recursive CTE references its own name. MySQL requires WITH RECURSIVE when any CTE in the clause is recursive. The nonrecursive part—the anchor—creates the initial rows. The recursive member derives the next rows from those already produced. A termination condition prevents endless generation.

WITH RECURSIVE seq(n) AS (  SELECT 1  UNION ALL  SELECT n + 1  FROM seq  WHERE n < 5)SELECT n FROM seq;-- Expected: 1,2,3,4,5

The predicate n < 5 applies before producing the next value. Without it, the CTE keeps producing rows until a server guardrail stops it.

Traverse the technician hierarchy

The technicians table already stores an adjacency list: each technician can reference a supervisor in the same table. Mina (10) is the root, Arman (11) and Sara (12) report to Mina, and Dariush (13) plus Laleh (14) report to Arman.

WITH RECURSIVE tech_tree AS (  SELECT technician_id,         technician_name,         supervisor_id,         0 AS depth,         CAST(technician_name AS CHAR(400)) AS path  FROM technicians  WHERE supervisor_id IS NULL  UNION ALL  SELECT child.technician_id,         child.technician_name,         child.supervisor_id,         parent.depth + 1,         CONCAT(parent.path, ' > ', child.technician_name)  FROM technicians AS child  JOIN tech_tree AS parent    ON child.supervisor_id = parent.technician_id)SELECT technician_id, technician_name, supervisor_id, depth, pathFROM tech_treeORDER BY path;

The explicit CAST(... AS CHAR(400)) on the anchor path is intentional. In a recursive CTE, result-column types are determined from the nonrecursive part. If the anchor’s string is too narrow for longer recursive paths, later concatenations can fail or truncate depending on context. Choose enough width for the intended hierarchy or use a representation designed for its maximum depth.

UNION ALL versus UNION in recursion

UNION ALL retains every row generated and is the usual choice when the recursive relation is structurally acyclic and duplicates are meaningful or impossible. UNION DISTINCT removes duplicates across iterations and can sometimes stop a recursion that revisits exactly the same row state, but duplicate elimination is not a complete cycle-detection strategy—especially when the row includes a changing depth or path.

Guardrail: cte_max_recursion_depth

MySQL exposes the cte_max_recursion_depth system variable. The default documented value is 1000. It limits the number of recursion levels and causes an overly deep recursive CTE to terminate with an error. You can set a smaller session value for a lab without changing the global server policy.

SELECT @@SESSION.cte_max_recursion_depth AS session_limit,       @@GLOBAL.cte_max_recursion_depth AS global_limit;SET @saved_cte_depth = @@SESSION.cte_max_recursion_depth;SET SESSION cte_max_recursion_depth = 10;-- Deliberately missing termination; the session guardrail stops it.WITH RECURSIVE runaway(n) AS (  SELECT 1  UNION ALL  SELECT n + 1 FROM runaway)SELECT * FROM runaway;SET SESSION cte_max_recursion_depth = @saved_cte_depth;

The exact error text can vary, but the important evidence is that the server stops recursion that exceeds the configured level. A higher limit is not a repair for a missing stop condition. In production, pair depth limits with query execution-time controls where appropriate.

Cycle-safe hierarchy traversal

Foreign keys ensure a supervisor ID refers to an existing technician; they do not by themselves prove the graph is acyclic. A mistaken update could create a cycle if application/schema controls permit it. One practical query-side guard is to carry a visited-ID path and refuse to revisit an ID already in that path.

WITH RECURSIVE tech_tree AS (  SELECT technician_id,         technician_name,         supervisor_id,         0 AS depth,         CAST(technician_id AS CHAR(400)) AS visited_ids,         CAST(technician_name AS CHAR(400)) AS display_path  FROM technicians  WHERE supervisor_id IS NULL  UNION ALL  SELECT child.technician_id,         child.technician_name,         child.supervisor_id,         parent.depth + 1,         CONCAT(parent.visited_ids, ',', child.technician_id),         CONCAT(parent.display_path, ' > ', child.technician_name)  FROM technicians AS child  JOIN tech_tree AS parent    ON child.supervisor_id = parent.technician_id  WHERE FIND_IN_SET(child.technician_id, parent.visited_ids) = 0)SELECT technician_id, depth, display_pathFROM tech_treeORDER BY display_path;

For large or deeply nested graphs, string paths may not be the best representation; the point of this lab is to make cycle state explicit. Production graph modeling may require additional constraints, closure tables, application validation, or a different data model.

Failure drill: a cycle in disposable data

Because the seed supervisor hierarchy has a root, changing Mina to report to Laleh creates a cycle and removes the root from the “supervisor_id IS NULL” anchor. Use a transaction so the experiment cannot leak into later lessons.

START TRANSACTION;UPDATE techniciansSET supervisor_id = 14WHERE technician_id = 10;SELECT technician_id, technician_name, supervisor_idFROM techniciansORDER BY technician_id;-- A root-anchored CTE now starts with no rows.WITH RECURSIVE tech_tree AS (  SELECT technician_id, supervisor_id  FROM technicians  WHERE supervisor_id IS NULL  UNION ALL  SELECT c.technician_id, c.supervisor_id  FROM technicians AS c  JOIN tech_tree AS p ON c.supervisor_id=p.technician_id)SELECT * FROM tech_tree;ROLLBACK;SELECT technician_id, supervisor_idFROM techniciansWHERE technician_id=10;-- Expected after rollback: supervisor_id is NULL

This failure is subtle: the query may return an empty result rather than an obvious recursion error because the cycle eliminated the anchor. Verification must include base-data invariants, not only “did the query terminate?”

Hands-on lab: hierarchy correctness checklist

1. Record @@SESSION.cte_max_recursion_depth.
2. Run the numeric 1–5 recursion and explain anchor, recursive member, and termination predicate.
3. Run the technician tree and verify all five technicians appear exactly once with depths 0,1,1,2,2.
4. Explain why the anchor path is explicitly cast wider than one technician name.
5. Temporarily set a shallow session recursion limit, run the deliberate runaway CTE, then restore the saved value.
6. Run the visited-ID version and compare its result with the simpler acyclic query.
7. Inject the disposable hierarchy cycle inside a transaction, observe the root-anchored query behavior, and roll back.

Production judgment and next bridge

Use recursive CTEs for bounded hierarchies and sequence-like problems when the relational model and expected depth are understood. In production, validate hierarchy invariants, cap recursion and execution time appropriately, watch temporary-space and CPU behavior for wide/deep traversals, and test worst-case paths—not only the happy root-to-leaf example.

Lesson 5 closes the chapter with set operations. They combine entire query results, so column compatibility, duplicate semantics, precedence, and final ordering become the correctness boundaries.

Authoritative references

• MySQL 8.4 Reference Manual — WITH (Common Table Expressions)
• MySQL 8.4 Reference Manual — cte_max_recursion_depth
• MySQL 8.4 Reference Manual — Optimizing Derived Tables, Views, and CTEs
• MySQL 8.4 Reference Manual — MAX_EXECUTION_TIME Optimizer Hint