Chapter 1 · Advanced Queries
CTEs and Recursive Queries
- Page 1 of 22
- 16 min read
The category report at the end of the beginner tutorial worked, but it was hard to read: a subquery inside a LEFT JOIN, another one inside ROUND, and the rule "a sale is a delivered or shipped order" written twice. A common table expression (CTE) fixes this. With WITH name AS (…) you give each step of a query a name, write the steps top to bottom like a small program, and use each name as if it were a table.
Real data work is full of such step-by-step queries: building ML features from raw events, cleaning a table before training, preparing the numbers for an evaluation report. Tools like dbt are, at heart, folders of CTE-based SELECTs. CTEs also have a second power: a recursive CTE can generate rows (a calendar, a number series) and walk hierarchies such as an org chart, a category tree or a thread of chat messages.
What you will learn
- How to write a CTE with
WITH, chain several of them, and reuse one twice. - When to use a CTE, a subquery, a temporary table or a view.
- How a recursive CTE works: anchor, recursive step, stop condition.
- How to generate number series and calendars, and walk an org chart, on SQLite, MySQL and PostgreSQL.
WITH: give a step a name
Define the business rule once, as a CTE called sold, then query it:
WITH sold AS (
SELECT o.order_id, o.customer_id, o.order_date, oi.product_id,
oi.quantity * oi.unit_price AS amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
)
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(DISTINCT order_id) AS orders,
SUM(amount) AS revenue
FROM sold
GROUP BY month
ORDER BY month;+---------+--------+---------+
| month | orders | revenue |
+---------+--------+---------+
| 2026-01 | 2 | 6600 |
| 2026-02 | 2 | 7000 |
| 2026-03 | 3 | 21400 |
| 2026-04 | 2 | 23450 |
| 2026-05 | 2 | 8500 |
| 2026-06 | 1 | 3100 |
+---------+--------+---------+What to notice:
- The CTE is part of one statement:
WITH … AS (…)followed directly by the mainSELECT, with no semicolon in between. - Inside the main query,
soldbehaves like a table with the columns the CTE selected. Nothing is stored; it exists only while this statement runs. - The result is the same as the monthly query in Project: A Sales Analytics Report for BitByte Shop. The CTE changes readability, not meaning.
Several CTEs in a chain
Separate CTEs with commas, under one WITH. Each CTE can use the ones above it. Here is the category report again, now in three readable steps:
WITH sold AS (
SELECT oi.product_id, oi.quantity * oi.unit_price AS amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
),
by_category AS (
SELECT COALESCE(c.name, 'Uncategorised') AS category,
SUM(s.amount) AS revenue
FROM sold AS s
JOIN products AS p ON p.product_id = s.product_id
LEFT JOIN categories AS c ON c.category_id = p.category_id
GROUP BY COALESCE(c.name, 'Uncategorised')
),
total AS (
SELECT SUM(revenue) AS revenue FROM by_category
)
SELECT b.category,
b.revenue,
ROUND(100.0 * b.revenue / t.revenue, 1) AS pct
FROM by_category AS b
CROSS JOIN total AS t
ORDER BY b.revenue DESC, b.category;+-------------+---------+------+
| category | revenue | pct |
+-------------+---------+------+
| Electronics | 31550 | 45.0 |
| Courses | 21000 | 30.0 |
| Accessories | 8900 | 12.7 |
| Books | 8600 | 12.3 |
+-------------+---------+------+Read it top to bottom: sold items, then revenue per category, then the grand total, then the final table. Each step can be tested alone: replace the final SELECT with SELECT * FROM by_category and run it. That is the routine from Reading Errors and Debugging a Query (run the pieces), built into the shape of the query. Furniture is missing because the chain starts from sold items; when every category must appear, start from categories and LEFT JOIN the rest, as the project did.
Using one CTE twice
A subquery has to be written out every time you need it; a CTE can be referenced as often as you like. Which customers spend more than the average customer?
WITH customer_revenue AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY o.customer_id
)
SELECT c.name, cr.revenue,
(SELECT ROUND(AVG(revenue), 2) FROM customer_revenue) AS avg_revenue
FROM customer_revenue AS cr
JOIN customers AS c ON c.customer_id = cr.customer_id
WHERE cr.revenue > (SELECT AVG(revenue) FROM customer_revenue)
ORDER BY cr.revenue DESC, c.customer_id;+--------------+---------+-------------+
| name | revenue | avg_revenue |
+--------------+---------+-------------+
| Nadia Rahman | 23600 | 11675.0 |
| Tanvir Ahmed | 22500 | 11675.0 |
+--------------+---------+-------------+The same query runs unchanged on MySQL 8 and PostgreSQL (MySQL 5.7 and older have no CTEs at all):
-- MySQL
WITH customer_revenue AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY o.customer_id
)
SELECT COUNT(*) AS customers, MAX(revenue) AS top_revenue
FROM customer_revenue;+-----------+-------------+
| customers | top_revenue |
+-----------+-------------+
| 6 | 23600 |
+-----------+-------------+CTE, subquery, temporary table or view?
All four let you name or reuse an intermediate result. They differ in how long the result lives and who can see it:
| Tool | Lives for | Good for |
|---|---|---|
| Subquery | The one place it is written | A small, one-off step (WHERE price > (SELECT AVG(price) …)) |
| CTE | One statement | Multi-step queries, a step used twice, recursion |
| Temporary table | Your connection (dropped when it closes) | A slow step reused by several statements; adding an index to an intermediate result |
| View | Permanent, until dropped | A definition many people and queries share (a "sold items" view for the whole team) |
A temporary table really stores rows, so later statements can use them:
CREATE TEMP TABLE sold_items AS
SELECT oi.order_id, oi.product_id, oi.quantity * oi.unit_price AS amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped');
SELECT COUNT(*) AS item_rows, SUM(amount) AS revenue FROM sold_items;
SELECT COUNT(DISTINCT order_id) AS orders FROM sold_items;+-----------+---------+
| item_rows | revenue |
+-----------+---------+
| 18 | 70050 |
+-----------+---------+
+--------+
| orders |
+--------+
| 12 |
+--------+The catch: a temporary table is a copy. If an order changes status after you create it, the copy is stale. A CTE or a view always reads the current data. (Views get their own page later in this tutorial: Views, Stored Procedures, Functions and Triggers.)
Performance: a CTE is a readability tool, not a cache. PostgreSQL 12+ usually inlines a CTE used once into the main query, exactly like a subquery; you can force it to be computed once with
WITH x AS MATERIALIZED (…). SQLite 3.35+ accepts the sameMATERIALIZED/NOT MATERIALIZEDhints; MySQL decides between merging and materialising on its own. Measure (see EXPLAIN and Query Optimization) before assuming a CTE makes anything faster or slower.
Recursive CTEs
A recursive CTE refers to itself. It has two parts joined by UNION ALL: an anchor that produces the first rows, and a recursive step that produces new rows from the rows made in the previous round. It stops when a round produces no new rows.
WITH RECURSIVE n(x) AS (
SELECT 1 -- anchor: round 1 gives x = 1
UNION ALL
SELECT x + 1 FROM n -- step: each round adds 1 to the last round's rows
WHERE x < 5 -- stop: when this is false, no new rows, done
)
round 1: 1 round 2: 2 round 3: 3 round 4: 4 round 5: 5 round 6: (none)WITH RECURSIVE n(x) AS (
SELECT 1
UNION ALL
SELECT x + 1 FROM n WHERE x < 5
)
SELECT x, x * x AS square FROM n;+---+--------+
| x | square |
+---+--------+
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
| 5 | 25 |
+---+--------+The n(x) form names the CTE's columns in the header instead of with AS inside. RECURSIVE is required by MySQL and PostgreSQL; SQLite accepts the query without it, but always write it. It is written once, straight after WITH, even when only one CTE in the list is recursive; the others may stay ordinary.
A calendar: days with no orders
A GROUP BY on order dates only shows days that have orders. A chart of daily orders would silently skip the empty days and make the shop look busier than it is. Generate every day first, then LEFT JOIN the orders:
WITH RECURSIVE days(day) AS (
SELECT '2026-05-28'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2026-06-10'
)
SELECT d.day, COUNT(o.order_id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;+------------+--------+
| day | orders |
+------------+--------+
| 2026-05-28 | 0 |
| 2026-05-29 | 0 |
| 2026-05-30 | 0 |
| 2026-05-31 | 0 |
| 2026-06-01 | 0 |
| 2026-06-02 | 1 |
| 2026-06-03 | 0 |
| 2026-06-04 | 0 |
| 2026-06-05 | 0 |
| 2026-06-06 | 0 |
| 2026-06-07 | 0 |
| 2026-06-08 | 0 |
| 2026-06-09 | 1 |
| 2026-06-10 | 0 |
+------------+--------+Twelve of these fourteen days had no orders, and now the result says so. This "spine" of dates is the standard way to fill gaps before a time-series chart or a forecasting model, which usually expects one row per day. Date arithmetic is dialect-specific, so this is the part that changes between databases:
-- MySQL
WITH RECURSIVE days AS (
SELECT CAST('2026-06-01' AS DATE) AS day
UNION ALL
SELECT day + INTERVAL 1 DAY FROM days WHERE day < '2026-06-03'
)
SELECT day FROM days ORDER BY day;+------------+
| day |
+------------+
| 2026-06-01 |
| 2026-06-02 |
| 2026-06-03 |
+------------+-- PostgreSQL
SELECT CAST(d AS DATE) AS day
FROM generate_series(DATE '2026-06-01', DATE '2026-06-03', INTERVAL '1 day') AS g(d)
ORDER BY day;+------------+
| day |
+------------+
| 2026-06-01 |
| 2026-06-02 |
| 2026-06-03 |
+------------+PostgreSQL rarely needs recursion for series: generate_series does it directly. The recursive form works there too.
Walking an org chart
Each employee has a manager_id that points to another employee. A self join (see LEFT, RIGHT, FULL, CROSS and Self Joins) climbs one level; a recursive CTE climbs all of them. Start from the person with no manager, then repeatedly add the people whose manager is already in the result:
WITH RECURSIVE chart AS (
SELECT employee_id, name, 1 AS level, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
FROM employees AS e
JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, name, path
FROM chart
ORDER BY path;+-------+-----------------+-----------------------------------------------+
| level | name | path |
+-------+-----------------+-----------------------------------------------+
| 1 | Ayesha Siddiqua | Ayesha Siddiqua |
| 2 | Hasan Mahmud | Ayesha Siddiqua > Hasan Mahmud |
| 3 | Nusrat Jahan | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
| 3 | Zahid Hasan | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan |
| 2 | Lamia Karim | Ayesha Siddiqua > Lamia Karim |
| 3 | Priya Sen | Ayesha Siddiqua > Lamia Karim > Priya Sen |
| 3 | Shuvo Roy | Ayesha Siddiqua > Lamia Karim > Shuvo Roy |
+-------+-----------------+-----------------------------------------------+Sorting by path puts each person directly under their manager, so the result reads like a tree. The same pattern walks a category tree, a bill of materials, or a chat thread where each message has a parent_id (a common way for LLM chat apps to store branching conversations).
On the servers, the type of each column is decided by the anchor. Here the anchor makes path a VARCHAR(100), while || in the recursive step produces text of unlimited length, and PostgreSQL refuses the mismatch:
-- PostgreSQL
WITH RECURSIVE chart AS (
SELECT employee_id, name, 1 AS level, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
FROM employees AS e
JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, name, path FROM chart ORDER BY path;Error: recursive query "chart" column 4 has type character varying(100) in non-recursive term but type character varying overallThe fix is to give the anchor column a type wide enough for every round: CAST(name AS TEXT) in PostgreSQL, CAST(name AS CHAR(500)) in MySQL (where || must also become CONCAT):
-- PostgreSQL
WITH RECURSIVE chart AS (
SELECT employee_id, name, 1 AS level, CAST(name AS TEXT) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, c.level + 1, c.path || ' > ' || e.name
FROM employees AS e
JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, path FROM chart WHERE level = 3 ORDER BY path;+-------+-----------------------------------------------+
| level | path |
+-------+-----------------------------------------------+
| 3 | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
| 3 | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan |
| 3 | Ayesha Siddiqua > Lamia Karim > Priya Sen |
| 3 | Ayesha Siddiqua > Lamia Karim > Shuvo Roy |
+-------+-----------------------------------------------+-- MySQL
WITH RECURSIVE chart AS (
SELECT employee_id, name, 1 AS level, CAST(name AS CHAR(500)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, c.level + 1, CONCAT(c.path, ' > ', e.name)
FROM employees AS e
JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT level, path FROM chart WHERE level = 3 ORDER BY path;+-------+-----------------------------------------------+
| level | path |
+-------+-----------------------------------------------+
| 3 | Ayesha Siddiqua > Hasan Mahmud > Nusrat Jahan |
| 3 | Ayesha Siddiqua > Hasan Mahmud > Zahid Hasan |
| 3 | Ayesha Siddiqua > Lamia Karim > Priya Sen |
| 3 | Ayesha Siddiqua > Lamia Karim > Shuvo Roy |
+-------+-----------------------------------------------+Common mistakes
Writing WITH twice
One WITH, then CTEs separated by commas. A second WITH is a syntax error:
WITH a AS (SELECT 1 AS x),
WITH b AS (SELECT 2 AS y)
SELECT * FROM a, b;Error: near "b": syntax errorFix: WITH a AS (…), b AS (…) SELECT ….
Using a CTE in the next statement
A CTE belongs to one statement. After the semicolon it is gone:
WITH sold AS (SELECT * FROM orders WHERE status = 'delivered')
SELECT COUNT(*) AS delivered FROM sold;
SELECT COUNT(*) FROM sold;+-----------+
| delivered |
+-----------+
| 11 |
+-----------+
Error: no such table: soldFix: repeat the WITH, or use a temporary table or a view when several statements need the result.
A recursion that never stops
Without a stop condition, the recursive step always finds new rows. MySQL stops after 1,000 rounds (the cte_max_recursion_depth setting, which you can raise for a session); PostgreSQL and SQLite have no such limit and keep going until you cancel the query, a timeout stops it, or memory or disk runs out:
-- MySQL
WITH RECURSIVE n AS (
SELECT 1 AS x
UNION ALL
SELECT x + 1 FROM n
)
SELECT COUNT(*) FROM n;Error: ERROR 3636: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.Fix: always put a WHERE in the recursive step that becomes false (WHERE x < 5, WHERE level < 10). Data with a cycle (A manages B, B manages A) loops forever even with a correct join, so a level limit is a cheap safety net.
A text column too narrow for later rounds
MySQL fixes each column's type from the anchor. An empty string is a zero-length text, so building an indent on it fails in the second round:
-- MySQL
WITH RECURSIVE chart AS (
SELECT employee_id, name, '' AS indent
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, CONCAT(c.indent, '-- ')
FROM employees AS e JOIN chart AS c ON e.manager_id = c.employee_id
)
SELECT CONCAT(indent, name) AS org FROM chart;Error: ERROR 1406: Data too long for column 'indent' at row 1Fix: CAST('' AS CHAR(100)) AS indent. Same idea as the PostgreSQL CAST(name AS TEXT) above.
Try it yourself
- Easy: Write a CTE
low_stockwith the products whose stock is below 10 (ignore NULL stock), then list them with their category name. - Medium: With two CTEs, find the month with the highest revenue from sold orders: first revenue per month, then the maximum, then the month that has it.
- Hard: For every employee, count how many people work under them at any depth (direct and indirect reports). Hint: in a recursive CTE, keep the original manager's id in a column while you walk down.
Answers
-- 1. Low stock
WITH low_stock AS (
SELECT product_id, name, category_id, stock
FROM products
WHERE stock < 10
)
SELECT l.name, l.stock, c.name AS category
FROM low_stock AS l
LEFT JOIN categories AS c ON c.category_id = l.category_id
ORDER BY l.stock, l.product_id;
-- 2. Best month
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY month
),
best AS (
SELECT MAX(revenue) AS revenue FROM monthly
)
SELECT m.month, m.revenue
FROM monthly AS m
JOIN best AS b ON b.revenue = m.revenue;
-- 3. Everyone under each manager
WITH RECURSIVE under AS (
SELECT manager_id AS boss_id, employee_id
FROM employees
WHERE manager_id IS NOT NULL
UNION ALL
SELECT u.boss_id, e.employee_id
FROM under AS u
JOIN employees AS e ON e.manager_id = u.employee_id
)
SELECT b.name, COUNT(u.employee_id) AS people_under
FROM employees AS b
LEFT JOIN under AS u ON u.boss_id = b.employee_id
GROUP BY b.employee_id, b.name
ORDER BY people_under DESC, b.employee_id;Summary
WITH name AS (…)names a step of a query; chain steps with commas under oneWITH, and reference each as often as you need.- A CTE lives for one statement. Use a temporary table to reuse stored rows across statements, a view for a shared permanent definition.
- CTEs are about readability; the optimiser decides whether to inline or materialise them.
- A recursive CTE is an anchor
UNION ALLa step that refers to the CTE, with aWHEREthat eventually stops it. - Use recursion for number series, calendars that fill gaps, and hierarchies; on servers, cast anchor columns wide enough and remember MySQL's
CONCATand PostgreSQL'sgenerate_series.
Next: Window Functions I: PARTITION BY, ROW_NUMBER and RANK adds calculations across related rows without collapsing them, often written on top of a CTE like the ones on this page.