CTEs & Recursive Queries

A Common Table Expression is a named temporary result set defined with WITH, available from MySQL 8.0.

Basic CTE

WITH dept_stats AS (
  SELECT department, COUNT(*) AS total, AVG(cgpa) AS avg_cgpa
  FROM students
  GROUP BY department
)
SELECT * FROM dept_stats WHERE avg_cgpa > 3.0 ORDER BY total DESC;

Multiple CTEs

Separate them with commas; a later CTE can reference an earlier one.

WITH
active_depts AS (
  SELECT code, name_en FROM departments WHERE active = 1
),
dept_stats AS (
  SELECT s.department, COUNT(*) AS total, AVG(s.cgpa) AS avg_cgpa
  FROM students s
  JOIN active_depts a ON s.department = a.code
  GROUP BY s.department
)
SELECT a.name_en, d.total, ROUND(d.avg_cgpa, 2) AS avg_cgpa
FROM dept_stats d
JOIN active_depts a ON a.code = d.department;

CTE vs derived table vs view

	CTE	Derived table	View
Defined	In the query, at the top	Inline in FROM	Stored in the schema
Reusable in the same query	Yes, many times	No	Yes
Recursion	Yes	No	No
Readability	Best	Poor when nested	Good

Recursive CTE

A recursive CTE has two parts joined by UNION ALL: an anchor that seeds the result, and a recursive member that refers back to the CTE.

WITH RECURSIVE numbers AS (
  SELECT 1 AS n                      -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10   -- recursive member
)
SELECT * FROM numbers;

Traversing a hierarchy

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level, CAST(name AS CHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.level + 1,
         CONCAT(oc.path, ' > ', e.name)
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, path FROM org_chart ORDER BY path;

The same pattern walks category trees, threaded comments, and bill-of-materials structures.

Generating a date series

WITH RECURSIVE dates AS (
  SELECT DATE('2026-01-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2026-01-31'
)
SELECT dates.d, COALESCE(COUNT(s.id), 0) AS signups
FROM dates
LEFT JOIN students s ON DATE(s.created_at) = dates.d
GROUP BY dates.d;

This is the standard way to produce a report with no gaps for days that had no activity.

Recursion limits

MySQL stops runaway recursion with cte_max_recursion_depth (default 1000):

SET SESSION cte_max_recursion_depth = 5000;

Always include a terminating condition in the recursive member — without one you will hit the limit and get an error.