Common Table Expressions (CTEs) in MySQL

Common Table Expressions (CTEs) let you define temporary named result sets that exist only for the duration of a single query. Introduced in MySQL 8.0, CTEs make complex queries more readable by breaking them into logical, named steps. They can also be recursive, enabling powerful hierarchical data traversal.

Basic CTE Syntax

A CTE begins with the WITH keyword, followed by the CTE name, an optional column list, and the defining query. The main query references the CTE by name just like a table.

WITH high_earners AS (
  SELECT id, first_name, last_name, salary, department
  FROM employees
  WHERE salary > 90000
)
SELECT department, COUNT(*) AS count
FROM high_earners
GROUP BY department
ORDER BY count DESC;

Multiple CTEs

You can chain multiple CTEs separated by commas. Later CTEs can reference earlier ones, building a pipeline of transformations.

WITH monthly_sales AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
avg_sales AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM monthly_sales
)
SELECT
  ms.month,
  ms.revenue,
  a.avg_revenue,
  ms.revenue - a.avg_revenue AS diff
FROM monthly_sales ms
CROSS JOIN avg_sales a
ORDER BY ms.month;

CTEs vs Subqueries

CTEs and subqueries often produce the same result, but CTEs are more readable and can be referenced multiple times in the main query. A subquery used in two places must be duplicated; a CTE is defined once.

-- CTE referenced twice
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
)
SELECT
  e.first_name,
  e.salary,
  d.avg_sal,
  e.salary - d.avg_sal AS above_avg
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;

Recursive CTEs

Recursive CTEs have an anchor member (base case) and a recursive member joined with UNION ALL. They are the standard way to traverse hierarchical data like organizational charts, category trees, or bill-of-materials structures.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level manager
  SELECT id, first_name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports
  SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  CONCAT(REPEAT('  ', depth), first_name) AS tree,
  depth
FROM org_chart
ORDER BY depth, first_name;

Generating a Date Series

MySQL does not have a built-in generate_series(), but a recursive CTE can produce a sequence of dates for reporting gaps.

WITH RECURSIVE dates AS (
  SELECT DATE('2025-01-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-01-31'
)
SELECT d AS report_date
FROM dates;

Performance Notes

MySQL materializes CTEs when they are referenced more than once, which can be beneficial or detrimental depending on the size of the intermediate result. Always set a termination condition in recursive CTEs to avoid infinite loops. MySQL's default recursion limit is 1000 iterations, configurable withcte_max_recursion_depth.