Common Table Expressions (WITH) in MySQL 8.0

For years I've been rewriting reporting queries with nested derived tables because MySQL had no WITH, while PostgreSQL people just smirked. 8.0 finally has it, including WITH RECURSIVE. Reports get readable, and trees (categories, org charts) can be walked in one statement instead of in application code. You need 8.0.11 or later; MariaDB 10.2 has CTEs too, but the recursion limits below are 8.0-specific.

A plain CTE first

A CTE is a named result set that exists for the duration of one statement. Instead of a subquery repeated twice:

WITH monthly AS (
  SELECT customer_id, DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(total) AS revenue
  FROM orders
  GROUP BY customer_id, ym
)
SELECT m1.customer_id, m1.ym, m1.revenue, m1.revenue - m2.revenue AS delta
FROM monthly m1
LEFT JOIN monthly m2
  ON m2.customer_id = m1.customer_id
 AND m2.ym = DATE_FORMAT(STR_TO_DATE(CONCAT(m1.ym,'-01'), '%Y-%m-%d') - INTERVAL 1 MONTH, '%Y-%m')
ORDER BY m1.customer_id, m1.ym;

You can define several CTEs separated by commas, and later ones may reference earlier ones. In 8.0 the optimizer either merges a CTE into the outer query or materializes it once, so referencing monthly twice does not run the aggregation twice – something a derived table rewrite could not guarantee on 5.7.

Recursive: a category tree

CREATE TABLE categories (
  id        INT UNSIGNED NOT NULL PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name      VARCHAR(100) NOT NULL,
  KEY (parent_id)
);
INSERT INTO categories VALUES
 (1,NULL,'Root'),(2,1,'Electronics'),(3,1,'Books'),
 (4,2,'Phones'),(5,2,'Laptops'),(6,4,'Accessories');

To list every descendant of Electronics with its depth and path:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, CAST(name AS CHAR(1000)) AS path
  FROM categories WHERE id = 2
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;

The first SELECT is the anchor; the second is the recursive member that joins back to the CTE name. The CAST(... AS CHAR(1000)) matters: column types are determined by the anchor, and without the cast path would be a VARCHAR(100) and the recursive part would fail with a truncation error.

Walking upward (from a leaf to the root) is the same pattern with the join reversed:

WITH RECURSIVE ancestors AS (
  SELECT id, parent_id, name FROM categories WHERE id = 6
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM categories c JOIN ancestors a ON c.id = a.parent_id
)
SELECT name FROM ancestors;

Generating a series

This is my favourite use: every date in a month, so a report shows zeros for days without sales instead of gaps.

WITH RECURSIVE days AS (
  SELECT DATE('2018-09-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2018-09-30'
)
SELECT days.d, COALESCE(SUM(o.total), 0) AS revenue
FROM days LEFT JOIN orders o ON DATE(o.created_at) = days.d
GROUP BY days.d;

Stopping runaway recursion

A recursive member with no terminating condition, or a cyclic parent_id chain, loops forever. 8.0 stops it with cte_max_recursion_depth, default 1000:

SHOW VARIABLES LIKE 'cte_max_recursion_depth';
SET SESSION cte_max_recursion_depth = 10000;   -- only when you genuinely need deeper

For a deliberately long series raise it for the session rather than globally. max_execution_time (milliseconds, SELECT only) is a second safety net:

SET SESSION max_execution_time = 5000;

Check the plan

Run EXPLAIN on a recursive query; the plan shows the CTE as a derived table with Recursive; Using where in the Extra column and confirms whether the join in the recursive member uses the parent_id index:

EXPLAIN WITH RECURSIVE tree AS (...) SELECT * FROM tree;

If the recursive step shows type: ALL on categories, add the index on parent_id from the category table above; without it each level scans the whole table. I forgot that index the first time and wondered why a 5-level tree was slow.

Errors

• ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value. – Either the data has a cycle or the termination condition is missing. Check for rows where a category is its own ancestor before raising the limit.
• ERROR 1406 (22001): Data too long for column 'path' at row 1 – Anchor column too narrow; cast it wider in the anchor member.
• ERROR 3577 (HY000): In recursive query block of Recursive Common Table Expression 'tree', the recursive table must be referenced only once, and not in any subquery – The recursive member may reference the CTE exactly once in its FROM; no LEFT JOIN to it, no aggregation over it, no DISTINCT in that member.
• ERROR 1064 (42000): You have an error in your SQL syntax ... near 'WITH tree AS' on a 5.7 server – The application is talking to a pre-8.0 instance; WITH is not available there.

CTEs don't make queries faster by themselves. They make them readable, and that's enough for me.