CTEs and recursive CTEs

A common table expression names a subquery at the top of a statement. MySQL has supported them since 8.0.

-- customers who ordered in at least three different months
WITH monthly AS (
  SELECT customer_id, DATE_FORMAT(placed_at, '%Y-%m') AS month
  FROM orders
  GROUP BY customer_id, month
),
regulars AS (
  SELECT customer_id, COUNT(*) AS active_months
  FROM monthly
  GROUP BY customer_id
  HAVING COUNT(*) >= 3
)
SELECT c.name, r.active_months
FROM regulars r
JOIN customers c ON c.id = r.customer_id;

Each CTE can read the ones before it, and the main query can reference a CTE more than once, which a derived table cannot. Readability is the main win; MySQL's optimizer may merge a CTE into the outer query or materialise it, the same choices it makes for a derived table.

WITH RECURSIVE

A recursive CTE has two parts joined by UNION ALL:

1. the anchor, which runs once and produces the starting rows;
2. the recursive member, which references the CTE itself and runs again on the rows the previous round produced, until a round produces nothing.

That is exactly a walk down a tree stored as parent_id links, here the sections of a document, each with its position among its siblings, turned into a numbered table of contents.

1CREATE TABLE sections (id INT PRIMARY KEY, parent_id INT NULL, position TINYINT NOT NULL, title VARCHAR(30) NOT NULL);2INSERT INTO sections VALUES3  (1, NULL, 1, 'Setup'), (2, NULL, 2, 'Queries'), (3, 1, 1, 'Install'),4  (4, 1, 2, 'Connect'), (5, 2, 1, 'Joins'), (6, 5, 1, 'Outer joins'),5  (7, 5, 2, 'Self joins'), (8, 2, 2, 'CTEs');

1WITH RECURSIVE toc (id, number, title) AS (2  SELECT id, CAST(position AS CHAR(50)), title3  FROM sections4  WHERE parent_id IS NULL5  UNION ALL6  SELECT s.id, CONCAT(t.number, '.', s.position), s.title7  FROM sections s8  JOIN toc t ON s.parent_id = t.id9)10SELECT number, title11FROM toc12ORDER BY number;

The anchor picks the chapters (parent_id IS NULL); each round adds the children of the rows found in the previous round, appending .position to their parent's number. Walking up from a node to the root is the same shape with the join turned round: the anchor selects the starting row, and each round finds the parent of the row found last.

The anchor decides the column types

MySQL determines the type of each recursive CTE column from the anchor member alone. If the anchor's number is just the position turned into text, its width is what a TINYINT needs, and every deeper number such as 2.1.1 must fit in that width too. In strict mode a longer value is an error; with strict mode off it is silently cut. Hence the CAST(... AS CHAR(50)) in the anchor of the example above.

1CREATE TABLE sections (id INT PRIMARY KEY, parent_id INT NULL, position TINYINT NOT NULL, title VARCHAR(30) NOT NULL);2INSERT INTO sections VALUES3  (1, NULL, 1, 'Setup'), (2, NULL, 2, 'Queries'), (3, 1, 1, 'Install'),4  (4, 1, 2, 'Connect'), (5, 2, 1, 'Joins'), (6, 5, 1, 'Outer joins'),5  (7, 5, 2, 'Self joins'), (8, 2, 2, 'CTEs');6-- A helper that runs a statement and records the error instead of stopping.7CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);8CREATE PROCEDURE try_sql(IN stmt TEXT)9BEGIN10  DECLARE EXIT HANDLER FOR SQLEXCEPTION11  BEGIN12    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;13    INSERT INTO attempts (statement, outcome)14    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));15  END;16  SET @sql = stmt;17  PREPARE s FROM @sql;18  EXECUTE s;19  DEALLOCATE PREPARE s;20  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');21END;

1CALL try_sql('WITH RECURSIVE toc (id, number) AS (2    SELECT id, CONCAT(position) FROM sections WHERE parent_id IS NULL3    UNION ALL4    SELECT s.id, CONCAT(t.number, ''.'', s.position)5    FROM sections s JOIN toc t ON s.parent_id = t.id)6  SELECT * FROM toc');7CALL try_sql('WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n) SELECT COUNT(*) FROM n');8CALL try_sql('WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10) SELECT COUNT(*) FROM n');9 10SELECT n, outcome FROM attempts ORDER BY n;

The first attempt is the same walk without the CAST, and fails with error 1406 as soon as a number outgrows the anchor's width. The second shows the other guard rail: a recursive member with no stopping condition is aborted when it exceeds cte_max_recursion_depth (1000 by default) with error 3636. The third adds the WHERE i < 10 that ends it properly.

Common mistakes

• No CAST in the anchor of a string that grows. Error 1406 in strict mode, silent truncation without it.
• No terminating condition. Error 3636 after 1001 rounds, and on a graph with a cycle the same thing happens even with a condition that looks correct; keep a depth counter and stop on it.

What interviewers ask

• How do you fetch every descendant of a node in MySQL? WITH RECURSIVE: anchor selects the node, the recursive member joins children on parent_id.
• Why does a recursive CTE throw "Data too long for column"? Column types come from the anchor; widen it with CAST there.
• What stops infinite recursion? Your own terminating condition, with cte_max_recursion_depth (default 1000) as the safety net.