Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 75 ยท Querying with MySQL

Common Table Expressions and Recursive CTEs

Learn MySQL common table expressions: WITH, multiple CTEs, recursive hierarchies, cte_max_recursion_depth and number or date series generation.

A common table expression (CTE) is a named, temporary result that lives for the duration of one statement. You write it with WITH name AS (...) before the main query and then use name as if it were a table. CTEs make long queries readable: instead of nesting subquery inside subquery, you build the answer in named steps from top to bottom.

MySQL has supported CTEs since version 8.0, including recursive CTEs that can walk a tree (an org chart, a category menu, a folder structure) or generate a series of numbers and dates. This lesson covers both. If CTEs are new to you, the general SQL CTE lesson is a gentle introduction. Here we focus on how MySQL treats them.

A CTE is a named step

Name it WITH totals AS (...) gives a subquery a name.
Read it The main query reads like a story, top to bottom.
Short-lived It exists for one statement only.

A CTE is not stored anywhere. It is a readability tool.

1 / 2

A plain CTE

The following example finds regions whose average order is above the overall average. The first CTE computes one row per region, the second computes the overall figure, and the final query compares them:

SQL Two CTEs and a final query

Press Run (or Ctrl + Enter) to see the result here.

The overall average is 151.25. East (220) and North (133.3 rounded) are tested against it, so only East is returned. Notice the comma between the two CTEs and that WITH is written only once.

Rules for plain CTEs

  • Several CTEs are separated by commas under one WITH.
  • A later CTE can use an earlier one, but not the other way round.
  • You can give column names explicitly: WITH t (a, b) AS (SELECT x, y FROM ...).
  • MySQL accepts WITH in front of SELECT, UPDATE and DELETE, and inside subqueries and derived tables. It also works with INSERT ... SELECT and CREATE TABLE ... SELECT (the WITH goes right before the SELECT).
  • A CTE name hides a real table of the same name for that statement. Pick names that do not clash.

Is a CTE faster than a subquery?

Not by itself. MySQL treats a non-recursive CTE like a derived table: it can merge it into the outer query, or materialize it into a temporary table. If you reference the same CTE twice, the optimizer can materialize it once and reuse the result, which is a real saving for an expensive step. If you reference it once, it is usually merged and behaves exactly like the equivalent subquery. Choose CTEs for clarity, and check the plan with EXPLAIN when speed matters (EXPLAIN and EXPLAIN ANALYZE).

How recursion works

A recursive CTE has two parts joined by UNION ALL (or UNION):

SQL
WITH RECURSIVE name (columns) AS (
  SELECT ...            -- anchor: runs once, produces the first rows
  UNION ALL
  SELECT ... FROM name  -- recursive step: refers to the CTE itself
  WHERE ...             -- stop condition
)
SELECT ... FROM name;

MySQL runs the anchor first. Then it repeatedly runs the recursive part, feeding it only the rows produced by the previous round, until a round returns no rows. All rounds are collected as the result.

The recursive loop

Anchor Run the first SELECT once.
Step Run the second SELECT on the newest rows.
Stop End when a round adds no rows.

Without a stop condition the query runs until MySQL cuts it off.

1 / 2

Generating a number series

MySQL has no built-in generate_series, so a recursive CTE is the standard way to produce a range of numbers:

SQL The numbers 1 to 10 and their squares

Press Run (or Ctrl + Enter) to see the result here.

The anchor produces 1. Each round adds one to the previous value, and WHERE n < 10 stops the loop when n reaches 10. The same pattern builds a calendar: start from a date and add a day each round. The next example lists the days of one week and marks the weekend, then joins the calendar to data so that days without any orders still appear:

SQL A calendar with gaps filled in

Press Run (or Ctrl + Enter) to see the result here.

All seven days appear, and days with no orders show 0. A plain GROUP BY on the orders table would have skipped them. This "calendar table" idea is the reason series generation matters for reports.

In MySQL the only difference is the date arithmetic: you would write SELECT DATE('2026-03-02') as the anchor and DATE_ADD(d, INTERVAL 1 DAY) in the recursive step. See Strings, Dates and Math Functions. Cast the anchor to the right type (DATE), because MySQL fixes the column type from the anchor row.

Walking a hierarchy

Org charts and category trees store a parent id in each row. A recursive CTE follows those links downward from a starting node. The query below lists everyone under the CEO and computes each person's depth and a readable path:

SQL An org chart from a parent column

Press Run (or Ctrl + Enter) to see the result here.

The anchor selects the root (the row with no manager). Each recursive round finds the people whose manager_id matches someone found in the previous round, so the result expands one level at a time. Sorting by path puts every employee right under their manager.

MySQL specifics: string concatenation uses CONCAT(c.path, ' > ', s.name), because || means logical OR unless PIPES_AS_CONCAT is set. Also, MySQL fixes a string column's width from the anchor: if the first row's path is 'Maya' (4 characters), later longer paths can fail with "Data too long" in strict mode. The usual cure is to cast the anchor wide enough: CAST(name AS CHAR(500)) AS path.

To go upward (all ancestors of one person), flip the join: start from that person and join s.id = c.manager_id.

Guarding against runaway recursion

If the data contains a cycle (A's manager is B and B's manager is A), or you forget the stop condition, the recursion never ends. MySQL protects you with the system variable cte_max_recursion_depth, 1000 by default. When a recursive CTE runs that many rounds, the statement fails with an error. Related limits are max_execution_time and the temporary table size.

SQL
SET SESSION cte_max_recursion_depth = 100000;  -- allow longer series
SET SESSION cte_max_recursion_depth = 50;      -- tighten a debugging session

So generating more than 1,000 numbers needs a higher value, and it is a good habit to keep the limit low during development, so a bad join fails fast instead of eating memory. For hierarchy walks, include your own depth counter and a WHERE depth < 20 guard. For graphs that can have cycles, track the visited path and skip nodes already on it (WHERE INSTR(path, name) = 0 is a rough form of that check).

Rules the recursive part must follow in MySQL:

  • It may refer to the CTE only once, and not inside a subquery.
  • It may not use GROUP BY, aggregate functions, window functions or DISTINCT on the recursive reference (LIMIT is allowed since 8.0.19 and is a handy safety net).
  • The anchor comes first. With UNION ALL, duplicates are kept, while UNION removes them, which can also end some cycles.

What ran live here

The plain CTEs, number and date series, and the org chart ran in SQLite. SQLite writes dates with date(d, '+1 day') and strings with ||, while MySQL uses DATE_ADD and CONCAT. The WITH RECURSIVE structure is the same in both. cte_max_recursion_depth, column-width rules for anchors, and MySQL's merge or materialize behavior are server features shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which MySQL version introduced CTEs?

    Common table expressions, including recursive ones, arrived in MySQL 8.0. Older versions need derived tables or temporary tables.
  2. 2. What does a recursive CTE feed to its recursive step in each round?

    Each round works on the newest rows only. The rounds end when one produces no rows.
  3. 3. What is the default value of cte_max_recursion_depth?

    The default of 1000 stops runaway recursion with an error. Raise it with SET SESSION when you need a longer series.
  4. 4. Why can a CTE named orders cause surprises?

    A CTE name takes precedence over a table with the same name for the duration of the statement, so pick distinct names.
  5. 5. In MySQL, why can a recursive path column fail with Data too long?

    The column type is fixed from the anchor SELECT, so a short first value can be too narrow for later rows. Cast the anchor to a wide CHAR.

Summary

  • A CTE (WITH name AS (...)) names a step of a query. Several CTEs are separated by commas, and later ones can use earlier ones.
  • A non-recursive CTE is merged or materialized like a derived table. It improves readability, not automatically speed.
  • A recursive CTE has an anchor, a recursive step joined by UNION ALL, and a stop condition. Each round uses only the previous round's rows.
  • Use recursion for number and date series, org charts and category trees, and always protect against cycles.
  • cte_max_recursion_depth (default 1000) aborts runaway recursion. Cast anchor columns wide enough for later rows.

Next: Window Functions.