A recursive CTE (Common Table Expression) in MySQL is a self-referencing query that iteratively builds a result set, enabling elegant solutions to hierarchical or graph-like data problems.
A recursive Common Table Expression (CTE) lets you query hierarchical data—such as organizational charts, folder trees, bill of materials, or graph networks—by repeatedly executing the same query block until a termination condition is met. Introduced in MySQL 8.0, recursive CTEs are the ANSI-SQL-standard alternative to older vendor-specific features like CONNECT BY
in Oracle or the dreaded “adjacency list + while loop” pattern in pre-8.0 MySQL.
Modern applications generate complex, interconnected datasets: think comment threads, category trees, and multi-level referrals. Modeling those relationships is straightforward—querying them efficiently used to be anything but. Before MySQL 8.0 you had to rely on stored procedures, temporary tables, or recursive logic in your application layer. Recursive CTEs pull that heavy lifting back into the database engine, giving you:
Every recursive CTE has two parts:
WHERE
clause or exhausted rows.MySQL repeatedly unions the results (UNION ALL
by default) until the recursive member returns zero rows or until you hit max_execution_time
or cte_max_recursion_depth
(default 1000).
WITH RECURSIVE cte_name (col1, col2, ...) AS (
-- Anchor member
anchor_query
UNION ALL
-- Recursive member
recursive_query referencing cte_name
)
SELECT * FROM cte_name;
Assume a classic employees
table:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
manager_id INT NULL,
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);
We want an ordered list of all direct and indirect reports under a given manager (say, emp_id = 3
):
WITH RECURSIVE org_path (emp_id, emp_name, manager_id, lvl) AS (
-- Anchor: start at the chosen manager
SELECT emp_id, emp_name, manager_id, 0 AS lvl
FROM employees
WHERE emp_id = 3
UNION ALL
-- Recursive: find subordinates of the previous level
SELECT e.emp_id, e.emp_name, e.manager_id, p.lvl + 1
FROM employees e
JOIN org_path p ON e.manager_id = p.emp_id
)
SELECT *
FROM org_path
ORDER BY lvl, emp_id;
The result is the complete managerial tree in depth-first order—no temp tables, no loops.
The recursive member often joins child rows to parent rows. A B-Tree index on the foreign-key column (manager_id
in the example) is critical.
Only select what you actually need. Recursive CTEs materialize intermediate rows; extra columns consume memory.
A mis-inserted row can create a self-loop (manager_id = emp_id
) and lead to infinite recursion. Use application logic, triggers, or a dedicated CHECK
to disallow cyclic references.
cte_max_recursion_depth
SafelyDefault depth 1000 is usually enough, but for very deep graphs you may raise it. Just ensure your data really contains that many levels and you’re not masking a bug.
Recursion multiplies rows quickly. EXPLAIN, monitor Sort_merge_passes
, and set an upper bound with MAXRECURSION
(MariaDB) or by rewriting with LIMIT/OFFSET if feasible.
UNION
forces a costly DISTINCT on every iteration. Stick to UNION ALL
unless duplicates truly break your business logic.SELECT
in a subquery and apply LIMIT
/OFFSET
. Just be mindful that the whole CTE still materializes before limiting.Galaxy’s modern SQL editor recognizes recursive CTE patterns and provides context-aware autocomplete for anchor vs. recursive members, highlights potential cycles, and suggests adding an index on the recursive key. With Galaxy’s AI Copilot, you can even prompt “Generate a recursive CTE to flatten my categories
table” and receive a verified query plus inline documentation—all without leaving the editor.
Migrate from an adjacency list to a materialized path by walking the tree and concatenating keys.
Compute nested components and aggregated costs per level for manufacturing systems.
Combine recursive CTEs with window functions to compute hop counts in an unweighted network.
Recursive CTEs transform once-painful hierarchical queries into concise, readable, ANSI-compliant SQL. Master them and you’ll write faster, safer, and more expressive queries—especially in a tool like Galaxy that supercharges the workflow with AI suggestions and collaboration.
Hierarchical queries power org charts, folder structures, recommendation graphs, and more. Recursive CTEs let data engineers express those queries in pure ANSI SQL, improving maintainability and performance while eliminating brittle application-side loops.
Recursive CTEs were introduced in MySQL 8.0 (specifically 8.0.1 as a DMR and fully GA in 8.0.2). Earlier versions do not support them.
Add a depth column and stop recursion after a sane maximum, enforce foreign-key constraints that disallow cycles, or tune cte_max_recursion_depth
.
Absolutely. Galaxy’s SQL editor autocompletes the WITH RECURSIVE
syntax and its AI Copilot can generate or refactor recursive queries for you, highlighting indexes to add.
Not inherently. For hierarchical relationships they’re often faster because they avoid multiple self-joins and let the optimizer process rows iteratively. Proper indexing is key.