Mastering Common Table Expressions (CTEs) and Recursive CTEs in MySQL 8: Subquery Optimization and Hierarchical Data Handling

Quick Start: Write Your First CTE in 3 Minutes

Anyone who has maintained 300-line SQL scripts with 4–5 levels of nested SUBQUERY blocks knows how dizzying it is to hunt down bugs. Common Table Expressions (CTEs) solve this problem once and for all.

In essence, a CTE defines a temporary, named result set within the execution scope of a query using the WITH clause. Instead of stuffing deeply nested subqueries into the FROM clause, you pull the calculation blocks to the top. The code reading flow becomes strictly top-to-bottom, much like assigning variables in backend code.

Here is a sample dataset of 5 orders you can test right away on MySQL 8.0+:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
);

INSERT INTO orders VALUES 
(1, 101, 500, '2026-01-10'),
(2, 102, 1200, '2026-01-12'),
(3, 101, 700, '2026-02-01'),
(4, 103, 300, '2026-02-05'),
(5, 102, 400, '2026-02-15');

-- Filter customers with total spending over $1,000
WITH customer_spending AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent, total_orders
FROM customer_spending
WHERE total_spent > 1000;

The query logic is encapsulated inside the customer_spending block. The final SELECT statement simply references this block just like a standard physical table.

Technical Foundations of Standard CTEs

1. Basic Syntax and CTE Chaining

The standard syntax begins with WITH, followed by the identifier name and the subquery enclosed in parentheses ():

WITH cte_name (col1, col2) AS (
    SELECT col_a, col_b FROM table_name
)
SELECT * FROM cte_name;

The real power of CTEs lies in chaining. You can define multiple CTEs sequentially separated by commas. Each subsequent CTE can directly reference results from preceding ones:

WITH 
january_orders AS (
    SELECT customer_id, amount 
    FROM orders 
    WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
),
loyal_customers AS (
    SELECT customer_id, SUM(amount) AS jan_total
    FROM january_orders
    GROUP BY customer_id
    HAVING jan_total >= 500
)
SELECT * FROM loyal_customers;

2. Comparing CTEs, Subqueries, and Temporary Tables

Criteria	CTE (WITH)	Derived Table (Subquery)	Temporary Table
Lifecycle	Exists only during a single query	Exists only during a single query	Persists for the entire connection session
Reusability	Can be referenced multiple times in the same query	Must duplicate the subquery	Can be referenced across multiple queries
Memory & I/O	RAM (Merge or In-memory temp table)	Similar to CTE	Incurs allocation overhead and disk I/O
Debuggability	Very high (modular, isolated blocks)	Poor when nested beyond 3 levels	Moderate

Handling Hierarchical Data with Recursive CTEs

Prior to version 8.0, traversing directory trees or parent-child comment threads in MySQL forced developers to rely on Stored Procedures or complex Nested Set models. Recursive CTEs solve this cleanly using mathematical recursion.

Structure of a Recursive Query

A valid WITH RECURSIVE query always consists of 3 components:

1. Anchor Member: The initial query that retrieves the base level of the tree (typically the root node) without referencing the CTE.
2. UNION ALL Operator: Combines the rows between parent and child levels.
3. Recursive Member: The query block that joins back onto the CTE itself to retrieve the next level. The recursion terminates when no new rows satisfy the condition.

Real-World Example: Traversing a 6-Level Org Chart

Create a sample employee table with parent-child relationships via the manager_id column:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO Nguyen Van A', NULL),
(2, 'CTO Tran Thi B', 1),
(3, 'CFO Le Van C', 1),
(4, 'Dev Lead Pham Van D', 2),
(5, 'Senior Dev Hoang Thi E', 4),
(6, 'Junior Dev Dang Van F', 5);

Requirement: Print the management hierarchy from the CEO down to Junior Devs, explicitly displaying the depth (level) and hierarchy path (path).

WITH RECURSIVE org_tree AS (
    -- 1. Anchor: Retrieve top-level executive (no manager)
    SELECT 
        employee_id, 
        name, 
        manager_id, 
        1 AS level,
        CAST(name AS CHAR(255)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 2. Recursive: Join the next level
    SELECT 
        e.employee_id, 
        e.name, 
        e.manager_id, 
        ot.level + 1 AS level,
        CONCAT(ot.path, ' -> ', e.name) AS path
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, level, path 
FROM org_tree
ORDER BY level, employee_id;

Technical note: Using CAST(name AS CHAR(255)) in the Anchor member is mandatory. If omitted, MySQL fixes the column length of path to the exact character length of the first row (16 characters for the CEO), causing a Data truncation: Data too long for column error on deeper child levels.

Generating Data Sequences (Data Generator) to Fill Reporting Gaps

When plotting a revenue chart for the last 30 days, dates without orders often cause gaps on the chart. You can use a recursive CTE to generate a contiguous date range without creating helper tables:

WITH RECURSIVE date_range AS (
    SELECT CAST('2026-03-01' AS DATE) AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < '2026-03-07'
)
SELECT report_date FROM date_range;

Production Performance Optimization Best Practices

1. Defending Against Infinite Loops

Circular references are the ultimate nightmare in recursion: A manages B, B manages C, but C points back to A. The query loops infinitely, spiking CPU utilization to 100%.

MySQL provides the cte_max_recursion_depth variable (defaulting to 1,000 iterations). You should proactively tighten this threshold directly inside the query using the SET_VAR optimizer hint:

-- Hard-limit to a maximum recursion depth of 20
WITH RECURSIVE org_tree AS (
    SELECT /*+ SET_VAR(cte_max_recursion_depth = 20) */
        employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree;

2. CTEs Do Not Automatically Speed Up Queries

CTEs are primarily syntactic sugar. They are not a replacement for proper index design.

The MySQL Optimizer handles CTEs using two execution strategies:

• Merge: Inlines the CTE logic directly into the outer query. This preserves index scans on the underlying tables.
• Materialize: Precomputes the entire CTE result set and buffers it in RAM (or spills to disk if it exceeds tmp_table_size, which defaults to 16MB). This temporary table has no indexes, forcing the outer query to perform a full table scan against it.

If the CTE contains GROUP BY, DISTINCT, or UNION, MySQL is forced to materialize it. When source tables reach 1–2 million rows or more, this can introduce severe performance bottlenecks.

Pre-deployment checklist:

1. Always run EXPLAIN ANALYZE to verify whether MySQL uses Materialize or Merge.
2. Create composite indexes on foreign key pairs used for recursion (e.g., CREATE INDEX idx_manager ON employees(manager_id, employee_id);).
3. Explicitly override the optimizer using hints when necessary: /*+ MERGE(cte_name) */ or /*+ NO_MERGE(cte_name) */ in MySQL 8.0.21+.