CTEs in MySQL

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

A Common Table Expression (CTE) is a named temporary result set defined at the top of a query with the WITH keyword. CTEs are fully supported in MySQL 8.4 and 9.x.

Basic CTE Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

A practical example — find the top 5 customers by total spend, then join their names:

WITH top_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    ORDER BY total_spent DESC
    LIMIT 5
)
SELECT c.name, tc.total_spent
FROM top_customers tc
JOIN customers c ON c.id = tc.customer_id;

You can define multiple CTEs in one WITH clause, and each can reference the ones defined before it:

WITH
monthly_totals AS (
    SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(amount) AS total
    FROM orders
    GROUP BY month
),
avg_monthly AS (
    SELECT AVG(total) AS avg_total FROM monthly_totals
)
SELECT mt.month, mt.total, am.avg_total
FROM monthly_totals mt
CROSS JOIN avg_monthly am;

CTEs vs Subqueries

	CTE	Subquery
Readability	Named, defined once at the top	Inline, can nest deeply
Reuse	Reference the same CTE multiple times	Must repeat the subquery
Debugging	Easy to run the CTE definition in isolation	Have to extract and run the subquery manually
Performance	Generally the same; optimizer often inlines CTEs	Same
MySQL version	8.0+	All versions

Use CTEs when the subquery is complex, when you reference the same result more than once, or when nesting would make the query hard to follow. For a comparison of when inline subqueries are better, see Subqueries vs JOINs in MySQL.

Recursive CTEs

A recursive CTE references itself. This is the standard SQL pattern for querying hierarchical data — org charts, category trees, threaded comments.

WITH RECURSIVE org_chart AS (
    -- Anchor member: start at the top
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find direct reports of rows already in the CTE
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name FROM org_chart ORDER BY depth, name;

The RECURSIVE keyword is required — without it, MySQL doesn’t recognize the self-reference and returns ERROR 1146: Table 'org_chart' doesn't exist. A recursive CTE has two parts joined by UNION ALL:

Anchor member — the base case (employees with no manager)
Recursive member — the step case (reports of employees already in the result)

MySQL stops when the recursive member returns zero rows. If your data has cycles (employee A reports to B, B reports to A), the query runs until it hits cte_max_recursion_depth (default: 1000) and errors. Guard against this with a depth column and a WHERE depth < N condition.

Frequently Asked Questions

Are CTEs faster than subqueries?

Usually the same. The MySQL optimizer often inlines a CTE the same way it would a subquery. CTEs referenced multiple times may be materialized into a temporary table, which can be faster (avoids re-executing the subquery) or slower (extra I/O). If you suspect unwanted materialization, check EXPLAIN for “MATERIALIZED” in the extra column.

Can I use CTEs in INSERT, UPDATE, or DELETE?

Yes. CTEs work with DML statements:

WITH stale_orders AS (
    SELECT id FROM orders WHERE created_at < NOW() - INTERVAL 1 YEAR
)
DELETE FROM orders WHERE id IN (SELECT id FROM stale_orders);

What’s the recursion depth limit?

The default is 1000 iterations (cte_max_recursion_depth). For deeper hierarchies, increase the session variable: SET SESSION cte_max_recursion_depth = 5000. If you hit the limit on normal data, check that your recursive member has a proper termination condition and that the data doesn’t contain cycles.

Troubleshooting

Problem	Solution
`ERROR 1064` on WITH syntax	Running an old MySQL version that predates CTE support — check `SELECT VERSION()`
`ERROR 1146: Table 'cte_name' doesn't exist`	Missing `RECURSIVE` keyword — add `WITH RECURSIVE` when the CTE references itself
`ERROR 3577: Recursive query aborted after N iterations`	Add a depth counter and `WHERE depth < N` guard condition
CTE referenced in WHERE clause loses rows	Window functions and CTEs are evaluated after WHERE — filter in a subquery around the CTE
CTE performing worse than subquery	Check `EXPLAIN` for “MATERIALIZED” — try inlining the CTE as a subquery to compare

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Basic CTE Syntax

CTEs vs Subqueries

Recursive CTEs

Frequently Asked Questions

Are CTEs faster than subqueries?

Can I use CTEs in INSERT, UPDATE, or DELETE?

What’s the recursion depth limit?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Basic CTE Syntax

​CTEs vs Subqueries

​Recursive CTEs

​Frequently Asked Questions

​Are CTEs faster than subqueries?

​Can I use CTEs in INSERT, UPDATE, or DELETE?

​What’s the recursion depth limit?

​Troubleshooting

​See also

Basic CTE Syntax

CTEs vs Subqueries

Recursive CTEs

Frequently Asked Questions

Are CTEs faster than subqueries?

Can I use CTEs in INSERT, UPDATE, or DELETE?

What’s the recursion depth limit?

Troubleshooting

See also