CTEs and Recursive CTEs

A hardware store records one row per sale, and the manager wants the days whose total sales were above the average day. The question needs the daily totals twice: once to list the days, and once to compute the average of those same totals. Written with subqueries nested inside FROM and WHERE, the query has to write the daily totals two times:

SELECT d.day_no, d.total_cents
FROM (
  SELECT day_no, SUM(amount_cents) AS total_cents
  FROM sales
  GROUP BY day_no
) AS d
WHERE d.total_cents > (
  SELECT AVG(a.total_cents)
  FROM (
    SELECT day_no, SUM(amount_cents) AS total_cents
    FROM sales
    GROUP BY day_no
  ) AS a
)
ORDER BY d.day_no;
Copy

Output:

+--------+-------------+
| day_no | total_cents |
+--------+-------------+
|      1 |       12000 |
|      4 |       15000 |
+--------+-------------+
Copy

The repeated block is required, not careless. A subquery in the FROM clause (a derived table) can be referenced only once in a query, so the MySQL manual says the result must be derived again for each use. The same query can instead name the daily totals once, at the top, and then use that name twice:

WITH daily AS (
  SELECT day_no, SUM(amount_cents) AS total_cents
  FROM sales
  GROUP BY day_no
)
SELECT day_no, total_cents
FROM daily
WHERE total_cents > (SELECT AVG(total_cents) FROM daily)
ORDER BY day_no;
Copy

Output:

+--------+-------------+
| day_no | total_cents |
+--------+-------------+
|      1 |       12000 |
|      4 |       15000 |
+--------+-------------+
Copy

A common table expression (CTE) is a named result set, defined in a WITH clause before the main query, that exists only for that one statement. The lesson Window Functions used one to filter a window result. Here the question is when a CTE is the better way to write a query, and how a CTE that refers to itself can produce rows that exist in no table.

The Tables

The sales table has one row per sale, with the amount in integer cents. No sale happened on days 3 and 6, so those days have no rows at all.

The categories table stores the store's product tree. Each category points to the category above it through parent_id, and a top-level category has a NULL parent. The name column is VARCHAR(30).

Every query below was run on MySQL 8.4, and the output shown is the real output.

When a CTE Beats a Nested Subquery

A CTE helps most when one intermediate result is used more than once. The plan MySQL 8.4 prints for the CTE version shows what happens to the second use:

EXPLAIN FORMAT=TREE
WITH daily AS (
  SELECT day_no, SUM(amount_cents) AS total_cents
  FROM sales
  GROUP BY day_no
)
SELECT day_no, total_cents
FROM daily
WHERE total_cents > (SELECT AVG(total_cents) FROM daily);
Copy

Output:

-> Filter: (daily.total_cents > (select #3))  (cost=0.888..2.96 rows=3.33)
    -> Table scan on daily  (cost=2.5..2.5 rows=0)
        -> Materialize CTE daily if needed  (cost=0..0 rows=0)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Table scan on sales  (cost=1.25 rows=10)
    -> Select #3 (subquery in condition; run only once)
        -> Aggregate: avg(daily.total_cents)  (cost=2.5..2.5 rows=1)
            -> Table scan on daily  (cost=2.5..2.5 rows=0)
                -> Materialize CTE daily if needed (query plan printed elsewhere)  (cost=0..0 rows=0)
Copy

MySQL computes the daily totals and stores them in an internal temporary table, which is called materializing the CTE. The second reference prints no plan of its own ("query plan printed elsewhere"), which matches the manual: a materialized CTE is built once, even when the query uses it several times. The nested version derives the totals twice.

The two forms compare as follows.

• Benefit of a CTE: one definition for a reused result. A change to the daily totals is made in one place, and the steps read in the order they run.
• Cost of a CTE: it can be stored before the outer query reads it. MySQL can merge a simple CTE into the outer query, but an aggregate function such as SUM prevents that merge, so daily becomes a temporary table. In PostgreSQL the reference count decides: a plain SELECT used once as a CTE is folded into the parent query, a CTE used twice is materialized, and MATERIALIZED or NOT MATERIALIZED overrides the choice.
• When a nested subquery is better: a short subquery used once. A filter like WHERE category_id IN (SELECT category_id FROM categories WHERE parent_id = 2) reads clearly where it is, and moving it into a WITH clause adds a name without removing any repetition.

Recursive CTEs

A recursive CTE is a CTE that refers to its own name: it starts from a set of rows and repeatedly adds rows computed from the rows added in the previous round. It is written with WITH RECURSIVE, and its body has two parts joined by UNION ALL. The first part (the nonrecursive part) produces the starting rows and does not mention the CTE. The second part (the recursive part) reads the CTE by name, and each round sees only the rows the previous round produced. The recursion ends when a round produces no new rows.

Filling Missing Days

Grouping sales by day returns five rows, because days 3 and 6 have no sales. That gap changes real answers. The opening query averaged five days, 50,000 / 5 = 10,000 cents. Over all seven days the store took 50,000 / 7, or about 7,143 cents per day, and days 1, 2, 4, and 7 are all above that figure.

The fix is a list of every day in the range (a day spine), produced by a recursive CTE and joined to the totals:

WITH RECURSIVE days (day_no) AS (
  SELECT MIN(day_no) FROM sales
  UNION ALL
  SELECT day_no + 1 FROM days
  WHERE day_no < (SELECT MAX(day_no) FROM sales)
),
daily AS (
  SELECT day_no, SUM(amount_cents) AS total_cents
  FROM sales
  GROUP BY day_no
)
SELECT days.day_no, COALESCE(daily.total_cents, 0) AS total_cents
FROM days
LEFT JOIN daily ON daily.day_no = days.day_no
ORDER BY days.day_no;
Copy

Output:

+--------+-------------+
| day_no | total_cents |
+--------+-------------+
|      1 |       12000 |
|      2 |        8000 |
|      3 |           0 |
|      4 |       15000 |
|      5 |        5000 |
|      6 |           0 |
|      7 |       10000 |
+--------+-------------+
Copy

The nonrecursive part returns day 1, the smallest day_no. Each round adds one to the previous row while it is below 7, so six rounds add days 2 through 7. The next round finds no day below 7, produces nothing, and the recursion stops. The LEFT JOIN keeps every spine day, and COALESCE turns the missing totals for days 3 and 6 into 0.

• Recursive spine: no extra table to maintain. The range always matches the data. The cost is one round per day, and a recursive CTE is always materialized.
• Calendar table: a stored table with one row per day. It is the better choice when many queries need a day range or the range is long. The cost is that someone has to create it and keep extending it.

Walking a Category Tree

The same two parts walk a hierarchy. The nonrecursive part selects the top-level categories, and the recursive part joins each child to the parent found in the previous round, adding 1 to the depth and appending the child's name to the path:

WITH RECURSIVE tree AS (
  SELECT category_id, name, 1 AS depth,
         CAST(name AS CHAR(200)) AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.category_id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
  FROM categories AS c
  JOIN tree AS t ON c.parent_id = t.category_id
)
SELECT category_id, depth, path FROM tree ORDER BY path;
Copy

Output:

+-------------+-------+---------------------------------------+
| category_id | depth | path                                  |
+-------------+-------+---------------------------------------+
|           7 |     1 | Garden                                |
|           8 |     2 | Garden > Hoses                        |
|           1 |     1 | Tools                                 |
|           5 |     2 | Tools > Hand Tools                    |
|           6 |     3 | Tools > Hand Tools > Hammers          |
|           2 |     2 | Tools > Power Tools                   |
|           3 |     3 | Tools > Power Tools > Cordless Drills |
|           4 |     3 | Tools > Power Tools > Saws            |
+-------------+-------+---------------------------------------+
Copy

The nonrecursive part finds Tools and Garden, the first round finds their three children, and the second round finds the three categories at depth 3. The third round finds no children, so the walk ends. Sorting by path places every category directly under its parent.

The CAST is required. MySQL takes the column types of a recursive CTE from the nonrecursive part only, so without it path has the type of name, which is VARCHAR(30). With the first line written as below, the 37-character path for Cordless Drills does not fit:

  SELECT category_id, name, 1 AS depth, name AS path
Copy

Output:

ERROR 1406 (22001) at line 1: Data too long for column 'path' at row 1
Copy

In strict SQL mode, which MySQL 8.4 uses by default, this is an error, and the manual's fix is the same CAST in the nonrecursive part. One more restriction applies to the recursive part: it may not contain aggregate functions, window functions, GROUP BY, ORDER BY, or DISTINCT. A total per branch is therefore computed after the walk, in the outer query.

Recursion Limits

A recursive CTE with a wrong stop condition would run forever, so MySQL stops it after cte_max_recursion_depth rounds, which defaults to 1000. A spine of 1,500 days needs more rounds than that:

WITH RECURSIVE days (day_no) AS (
  SELECT 1
  UNION ALL
  SELECT day_no + 1 FROM days WHERE day_no < 1500
)
SELECT COUNT(*) AS days FROM days;
Copy

Output:

ERROR 3636 (HY000) at line 1: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Copy

The limit can be raised for one session with SET SESSION cte_max_recursion_depth. It can also be raised for one statement: the same query succeeds when its final SELECT carries an optimizer hint:

SELECT /*+ SET_VAR(cte_max_recursion_depth = 1600) */ COUNT(*) AS days FROM days;
Copy

Output:

+------+
| days |
+------+
| 1500 |
+------+
Copy

The manual lists two more guards: max_execution_time, which stops a slow statement, and a LIMIT inside the recursive part, which caps the number of rows. A depth column with a condition such as WHERE t.depth < 10 in the recursive part is a third guard that the query controls directly.

Practice: Daily Sales for the Tools Branch

The sales and categories tables have the columns shown above. Return one row for every day from the smallest day_no to the largest day_no in sales, where both bounds count sales of every category. Return day_no, then sale_count, the number of sales in the category named Tools or in any category below it at any depth, then sales_cents, the total amount of those sales. A day with no such sales shows 0 in both columns. The category named Tools may itself have a parent, and it may have sales of its own. Sort the result by day_no.