MySQL + SQL · Lesson 78

Recursive CTE in MySQL

Recursive CTE

A recursive CTE repeats itself until a condition stops it — perfect for hierarchies (org charts, categories) or number sequences.

Example: Numbers 1 to 5

WITH RECURSIVE nums AS (
  SELECT 1 AS n               -- anchor (start)
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5   -- recursive step
)
SELECT * FROM nums;

Produces 1,2,3,4,5 by repeatedly adding 1 until n reaches 5.

Summary

WITH RECURSIVE has an anchor query + a recursive query joined by UNION ALL.
Used for hierarchies and generated sequences.