MySQL Common Table Expressions

A MySQL common table expression, or CTE, is a named temporary result set available within one SQL statement. You define it with a WITH clause and reference it like a table in the statement that follows.

CTEs make multi-step queries easier to read, allow one derived result to be referenced more than once, and support recursion for hierarchies and generated series. They do not create permanent tables or store results beyond the statement.

Use a descriptive CTE name that explains the result it produces. A CTE improves structure, but it does not automatically make a query faster.

Create a Nonrecursive CTE

Place the WITH clause before the main SELECT, UPDATE, or DELETE.

Syntax:

-- Name a temporary result for this statement
WITH cte_name AS (
    SELECT column_name
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Example:

-- Calculate paid totals once, then filter the result
WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS paid_total
    FROM orders
    WHERE status = 'PAID'
    GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM customer_totals
WHERE paid_total >= 50000
ORDER BY paid_total DESC;

Specify CTE Column Names

You can place a column list after the CTE name. Its length must equal the number of result columns. Without this list, names come from the first select list.

-- Assign stable names to calculated columns
WITH regional_sales (region_name, order_count, sales_total) AS (
    SELECT region, COUNT(*), SUM(total_amount)
    FROM orders
    GROUP BY region
)
SELECT region_name, sales_total
FROM regional_sales;

Define Several CTEs

Use one WITH clause and separate CTE definitions with commas. A CTE can reference another CTE declared earlier at the same level, but not one declared later. Names must be unique.

-- Build the second result from the first
WITH
paid_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'PAID'
),
customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS paid_total
    FROM paid_orders
    GROUP BY customer_id
)
SELECT c.customer_name, t.paid_total
FROM customer_totals AS t
JOIN customers AS c ON c.customer_id = t.customer_id;

Use a Recursive CTE

A recursive CTE refers to itself. Add RECURSIVE after WITH. The nonrecursive anchor produces starting rows, and the recursive member produces more rows until it returns none.

-- Generate the integers 1 through 10
WITH RECURSIVE numbers (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n
FROM numbers;

The anchor and recursive member must produce compatible columns. MySQL determines recursive CTE column types from the nonrecursive part, so cast anchor values wide enough to hold later results.

Traverse Hierarchical Data

Recursive CTEs work well with adjacency-list tables where each row stores its parent's identifier.

-- Return one employee and every descendant
WITH RECURSIVE employee_tree AS (
    SELECT employee_id, manager_id, employee_name, 0 AS depth
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name,
           tree.depth + 1
    FROM employees AS e
    JOIN employee_tree AS tree
      ON e.manager_id = tree.employee_id
)
SELECT employee_id, employee_name, depth
FROM employee_tree
ORDER BY depth, employee_id;

Ensure hierarchy data cannot create unexpected cycles, or add explicit cycle protection and practical depth limits.

Limit Recursion

MySQL limits recursive iterations with cte_max_recursion_depth, whose default is 1000. You can also stop work with a terminating predicate, a row limit where supported, or the MAX_EXECUTION_TIME optimizer hint.

-- Apply a one-second execution limit to this statement
WITH RECURSIVE sequence (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM sequence WHERE n < 100000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ n
FROM sequence;

If a recursive query runs too long, interrupt it from the client. Never rely only on the server depth limit when a clear logical stopping condition is available.

Use CTEs with Data Modification

A CTE can supply rows to an UPDATE or DELETE. For INSERT ... SELECT, MySQL places the WITH clause immediately before the SELECT portion.

-- Mark customers whose latest order is old
WITH latest_orders AS (
    SELECT customer_id, MAX(ordered_at) AS last_ordered_at
    FROM orders
    GROUP BY customer_id
)
UPDATE customers AS c
JOIN latest_orders AS latest
  ON latest.customer_id = c.customer_id
SET c.is_dormant = 1
WHERE latest.last_ordered_at < CURRENT_DATE - INTERVAL 1 YEAR;

Understand CTE Optimization

The optimizer may merge a nonrecursive CTE into the outer query or materialize it as an internal temporary table. Constructs such as aggregation, DISTINCT, GROUP BY, HAVING, LIMIT, unions, window functions, and literal-only queries can prevent merging.

A materialized CTE referenced several times is materialized once for the query, and MySQL may add internal indexes. Use EXPLAIN or EXPLAIN ANALYZE to inspect the actual plan rather than assuming either strategy.

-- Inspect whether the CTE is merged or materialized
EXPLAIN ANALYZE
WITH paid_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'PAID'
)
SELECT customer_id, SUM(total_amount)
FROM paid_orders
GROUP BY customer_id;

CTE, Derived Table, or View

Best Practices

• Keep each CTE focused on one logical result.
• Declare dependencies before the CTEs that reference them.
• Use UNION ALL unless duplicate removal is required.
• Write an explicit recursive stopping condition.
• Cast anchor values to safe widths for growing strings or numbers.
• Inspect execution plans and index base-table joins and filters.

Conclusion

MySQL common table expressions organize complex statements into named, statement-scoped results and enable recursive queries. Use nonrecursive CTEs for readable transformations, recursive CTEs for bounded hierarchies and series, and execution plans to understand merging or materialization. Clear termination and correct base-table indexes remain essential.