Common Table Expressions (WITH) & Recursion

Naming a query to make it readable

When a query needs several steps — summarise, then filter that summary, then join it — nesting subqueries quickly becomes a tangle of brackets. A Common Table Expression (CTE) fixes this. Written with the WITH keyword, a CTE lets you give a query a name and then use that name like a temporary table in the main query below it. It is the same idea as a derived table, but pulled out to the top where it reads top-to-bottom like steps in a recipe.

We will use this orders table:

A single CTE

Say you want each user’s total spend, then only the users who spent over 600. With a CTE you compute the totals first, name them user_totals, then query that name:

WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT user_id, spent
FROM user_totals
WHERE spent > 600;

Reading it top to bottom: WITH user_totals AS ( ... ) defines the CTE — everything in the brackets is an ordinary query that totals each user’s spend. After the closing bracket, the main query treats user_totals exactly like a table, selecting from it and filtering with WHERE spent > 600. The CTE exists only for this one statement, then disappears.

Chaining several CTEs

The real power appears when a problem has multiple stages. You can define several CTEs separated by commas, and each later one can use the earlier ones — turning a scary query into clear, named steps. Here we first total each user’s spend, then compute the average of those totals, then keep users who beat that average:

WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
average_spend AS (
  SELECT AVG(spent) AS avg_spent
  FROM user_totals
)
SELECT user_id, spent
FROM user_totals, average_spend
WHERE spent > avg_spent;

Step by step: the first CTE user_totals produces each user’s spend (1400, 400, 300). The second CTE average_spend reads user_totals to get the average of those numbers (700). The main query then compares each user’s spend against that single average and keeps only those above it. Each step has a clear name, so anyone reading the query can follow the logic without untangling nested brackets.

Recursive CTEs — walking a hierarchy

A recursive CTE is a special form that can refer to itself, letting it walk tree-shaped data — a category with sub-categories, an employee’s chain of managers, a folder of sub-folders. It is the one clean way to follow such links to any depth. Suppose a categories table where each row has a parent_id pointing to its parent category (top-level categories have NULL):

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories AS c
  INNER JOIN category_tree AS t ON c.parent_id = t.id
)
SELECT id, name, depth FROM category_tree ORDER BY depth, id;

A recursive CTE has two halves joined by UNION ALL. The first part (the *anchor*) selects the starting rows — here the top-level categories where parent_id IS NULL, marked depth 1. The second part (the *recursive* member) joins the categories table back to the CTE itself (category_tree), finding the children of rows already collected and adding 1 to their depth. MySQL repeats that second part — children, then grandchildren — until no new rows are found, then stops. WITH RECURSIVE is required to allow the self-reference.