Common Table Expressions (WITH)

As your queries grow, subqueries inside subqueries become hard to read. You have to start in the middle, work outwards, and remember what each bracket returns. A Common Table Expression, or CTE, fixes this by letting you name a query at the top and then use that name like a table.

CTEs are written with the WITH keyword and have been part of MySQL since version 8.0. In this lesson you will write simple CTEs, chain several together, and use a recursive CTE to walk through the employee hierarchy, something ordinary queries cannot do.

Your first CTE

A CTE has two parts: WITH name AS ( query ) defines a temporary named result, and the main query after it uses that name. Here the CTE calculates every order's total, and the main query keeps the orders above the average:

WITH order_totals AS (
  SELECT o.order_id, o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id, o.customer_id
)
SELECT order_id, customer_id, order_total
FROM order_totals
WHERE order_total > (SELECT AVG(order_total) FROM order_totals)
ORDER BY order_total DESC, order_id;

order_id | customer_id | order_total
---------+-------------+------------
110      | 8           | 32797
102      | 2           | 15999
104      | 1           | 8999
113      | 3           | 8999
112      | 10          | 8695
(5 rows)

Read it from top to bottom: first "work out the order totals", then "keep the big ones". Notice that order_totals is used twice, once in FROM and once inside the subquery for the average. With derived tables you would have to write the whole grouping query twice.

A CTE exists only while its statement runs. It is not saved in the database; for a reusable named query, create a view.

Chaining several CTEs

One WITH can define several CTEs, separated by commas. Each one can use the CTEs defined before it, so you can build an answer step by step. Which month brought the most revenue?

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY SUBSTR(o.order_date, 1, 7)
),
best AS (
  SELECT MAX(revenue) AS top_revenue
  FROM monthly
)
SELECT m.month, m.revenue
FROM monthly m
JOIN best b ON m.revenue = b.top_revenue;

month   | revenue
--------+--------
2025-04 | 43691
(1 row)

SUBSTR(order_date, 1, 7) turns a date such as 2025-04-18 into the month 2025-04, and works in both MySQL and the browser. The step monthly groups by month, best finds the highest revenue, and the final query joins them. Each step is small enough to test on its own: replace the final SELECT with SELECT * FROM monthly while you build.

CTEs also make window-function filters tidy. The "top product in each category" query from Window Functions reads well as WITH ranked AS (...) SELECT ... FROM ranked WHERE pos = 1.

Recursive CTEs

The employees table stores a tree: each employee's manager_id points to another employee. A join can find someone's manager, and a second join their manager's manager, but how many joins do you need for the whole company? You do not know in advance.

A recursive CTE solves this. It refers to itself and has two parts joined by UNION ALL:

1. The anchor query gives the starting rows.
2. The recursive query joins the table to the rows found so far, finding the next level. It repeats until it finds no new rows.

This builds the full org chart, starting from the chief executive (the only employee with no manager):

WITH RECURSIVE org_chart AS (
  SELECT employee_id, first_name, job_title, manager_id,
         1 AS depth,
         CAST(first_name AS CHAR(200)) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.first_name, e.job_title, e.manager_id,
         oc.depth + 1,
         CONCAT(oc.path, ' > ', e.first_name)
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT depth, first_name, job_title, path
FROM org_chart
ORDER BY path;

depth | first_name | job_title           | path
------+------------+---------------------+------------------------
1     | Rajesh     | Chief Executive     | Rajesh
2     | Deepak     | Support Lead        | Rajesh > Deepak
3     | Tanvi      | Support Executive   | Rajesh > Deepak > Tanvi
2     | Neha       | Engineering Manager | Rajesh > Neha
3     | Amit       | Senior Developer    | Rajesh > Neha > Amit
3     | Karan      | Developer           | Rajesh > Neha > Karan
3     | Pooja      | Developer           | Rajesh > Neha > Pooja
2     | Simran     | Sales Manager       | Rajesh > Simran
3     | Nisha      | Sales Executive     | Rajesh > Simran > Nisha
3     | Rahul      | Sales Executive     | Rajesh > Simran > Rahul
(10 rows)

Round 1 (the anchor) finds Rajesh. Round 2 finds everyone whose manager is Rajesh: Neha, Simran and Deepak. Round 3 finds their teams. Round 4 finds nobody, so the recursion stops. depth counts the levels, and path builds a readable chain of names; sorting by path keeps each team under its manager.

Why CAST(first_name AS CHAR(200))? In MySQL, the anchor query decides each column's type. Without the cast, path would be only as wide as the longest first name, and MySQL would refuse to store the longer chains. Casting to a wide text type in the anchor avoids this.

Walking up the tree

The same idea works in the other direction. Starting from Tanvi, follow manager_id upwards to find her whole chain of command:

WITH RECURSIVE chain AS (
  SELECT employee_id, first_name, manager_id, 0 AS steps_up
  FROM employees
  WHERE employee_id = 10
  UNION ALL
  SELECT e.employee_id, e.first_name, e.manager_id, c.steps_up + 1
  FROM employees e
  JOIN chain c ON e.employee_id = c.manager_id
)
SELECT steps_up, first_name
FROM chain
ORDER BY steps_up;

steps_up | first_name
---------+-----------
0        | Tanvi
1        | Deepak
2        | Rajesh
(3 rows)

The only change is the join direction: before, we looked for employees whose manager was a found row; now we look for the manager of a found row. The same pattern handles product categories with sub-categories, comment replies, and folders inside folders.

Generating a series

A recursive CTE does not need a table at all. It can count, which is useful for filling gaps in reports, such as months with no sales:

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n, n * 549 AS cost_of_n_pen_drives
FROM numbers;

n | cost_of_n_pen_drives
--+---------------------
1 | 549
2 | 1098
3 | 1647
4 | 2196
5 | 2745
(5 rows)

The condition WHERE n < 5 is what stops the recursion. Forget it and the query would run forever. MySQL protects you: it stops a recursive CTE after 1,000 rounds by default (the cte_max_recursion_depth setting) with an error.

Key points

• A CTE names a query with WITH name AS (...) so the main query can use it like a table.
• CTEs read from top to bottom and can be referenced more than once in the same statement.
• Several CTEs are separated by commas, and each can use the ones before it.
• A CTE lasts for one statement; a view is saved in the database.
• WITH RECURSIVE combines an anchor query and a recursive query with UNION ALL to walk trees.
• Always give a recursive CTE a stop condition; MySQL stops at 1,000 rounds by default.

WITH customer_spend AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
),
avg_spend AS (
  SELECT AVG(total_spent) AS avg_total FROM customer_spend
)
SELECT c.first_name, c.city, s.total_spent
FROM customer_spend s
JOIN customers c ON c.customer_id = s.customer_id
JOIN avg_spend a ON s.total_spent > a.avg_total
ORDER BY s.total_spent DESC;