CTEs: WITH and recursive queries

In this lesson you will learn

Create named intermediate results with WITH and reuse them
Chain several CTEs as consecutive steps
Build a number series and a hierarchy with WITH RECURSIVE and avoid infinite loops

Reading a query with three levels of nested subqueries is like solving a puzzle from the inside out. In programming, at such moments we give an intermediate result a name and store it in a variable. In SQL the equivalent is a CTE (common table expression): we split the query into named steps, and each step can use the earlier ones. What's more, a CTE can even refer to itself — a powerful tool for hierarchies and sequences.

WITH: naming an intermediate result

A CTE is written as WITH name AS (query) before the main query and exists only while that query runs. Its big advantage over a subquery: you can refer to the same CTE several times. Below, customer_totals is used twice — in the join and in the subquery that computes the average amount.

SQL· Edit the code and run it again

WITH customer_totals AS (
  SELECT o.customer_id, SUM(o.quantity * p.price) AS total
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(ct.total, 2) AS total
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals)
ORDER BY ct.total DESC;

▸ Expected output

name | total
Anar Mustafayev | 1723
Emre Yılmaz | 941.99
Zəhra Hüseynli | 935.99

Customers who spent more than the average (563.15).

Chaining CTEs: a query step by step

One WITH can hold several CTEs separated by commas, and each can refer to the ones before it. This creates a “data pipeline”: 1) calculate each order's amount and country, 2) total it by country, 3) find each country's share of total revenue.

SQL· Edit the code and run it again

WITH lines AS (
  SELECT c.country, o.quantity * p.price AS amount
  FROM orders AS o
  JOIN products  AS p ON p.id = o.product_id
  JOIN customers AS c ON c.id = o.customer_id
),
by_country AS (
  SELECT country, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
  FROM lines
  GROUP BY country
)
SELECT country, orders, revenue,
       ROUND(100.0 * revenue / (SELECT SUM(revenue) FROM by_country), 1) AS share_pct
FROM by_country
ORDER BY revenue DESC;

▸ Expected output

country | orders | revenue | share_pct
Azerbaijan | 7 | 2843.49 | 72.1
Türkiye | 3 | 973.59 | 24.7
United Kingdom | 1 | 69.98 | 1.8
Russia | 1 | 55 | 1.4

Recursive CTEs: a query that refers to itself

Definition

Recursive CTE

A CTE written with WITH RECURSIVE that has two parts: the anchor produces the first rows, and the recursive part after UNION ALL refers to the CTE itself and builds new rows from the rows of the previous step. The process stops when the recursive part returns no new rows.

SQL· Edit the code and run it again

WITH RECURSIVE numbers(n) AS (
  SELECT 1                                -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5   -- step + stop
)
SELECT n, n * n AS square
FROM numbers;

▸ Expected output

n | square
1 | 1
2 | 4
3 | 9
4 | 16
5 | 25

What is such a series good for? Showing empty groups in a report as well. Take the distribution of scores in 10-point ranges: GROUP BY only returns the ranges that have data. If we generate the ranges with a recursive CTE and LEFT JOIN the scores, the 40–49 range, where nobody landed, also appears with 0.

SQL· Edit the code and run it again

WITH RECURSIVE buckets(low) AS (
  SELECT 40
  UNION ALL
  SELECT low + 10 FROM buckets WHERE low < 90
)
SELECT b.low || '-' || (b.low + 9) AS score_range,
       COUNT(e.id) AS enrollments
FROM buckets AS b
LEFT JOIN enrollments AS e ON e.score BETWEEN b.low AND b.low + 9
GROUP BY b.low
ORDER BY b.low;

▸ Expected output

score_range | enrollments
40-49 | 0
50-59 | 1
60-69 | 2
70-79 | 5
80-89 | 4
90-99 | 8

Walking a hierarchy

The classic use of recursive CTEs is tree structures: an organisation chart, categories and subcategories, folders. Each employee's row stores the id of their manager (manager_id). The anchor takes the director, who has no manager, and the recursive part goes one level down at each step and extends the path.

SQL· Edit the code and run it again

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, role TEXT,
                    manager_id INTEGER REFERENCES staff(id));
INSERT INTO staff VALUES
  (1, 'Samir',   'Director',            NULL),
  (2, 'Ramin',   'Head of Science',     1),
  (3, 'Nərmin',  'Head of Humanities',  1),
  (4, 'Ülviyyə', 'Physics teacher',     2),
  (5, 'Elnur',   'Informatics teacher', 2),
  (6, 'Sara',    'English teacher',     3);

WITH RECURSIVE chain(id, name, level, path) AS (
  SELECT id, name, 0, name
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, c.level + 1, c.path || ' > ' || s.name
  FROM staff AS s
  JOIN chain AS c ON s.manager_id = c.id
)
SELECT level, path
FROM chain
ORDER BY path;

▸ Expected output

level | path
0 | Samir
1 | Samir > Nərmin
2 | Samir > Nərmin > Sara
1 | Samir > Ramin
2 | Samir > Ramin > Elnur
2 | Samir > Ramin > Ülviyyə

ORDER BY path lays the tree out like folders: every manager followed by their staff.

DBMS	Plain CTE	Recursive CTE
SQLite	WITH	WITH RECURSIVE
PostgreSQL	WITH	WITH RECURSIVE
MySQL 8.0+	WITH	WITH RECURSIVE
SQL Server	WITH	`WITH` (no RECURSIVE keyword)

In SQL Server the statement before a CTE must end with a semicolon, which is why people often write ;WITH.

Exercise

Create a CTE called course_stats: for each course, the course_id, the number of enrollments (students) and the average score (avg_score). Then join it with courses and show the title, number of students and average score rounded to 1 decimal place for the courses with at least 3 students, sorted by average score descending.

Exercise · SQL· Edit the code and run it again

WITH course_stats AS (
  -- course_id, students, avg_score
)
SELECT c.title, cs.students, ROUND(cs.avg_score, 1) AS avg_score
FROM course_stats AS cs
JOIN courses AS c ON c.id = cs.course_id
-- filter and sort

▸ Expected output

title | students | avg_score
Python Basics | 3 | 92.7
Algebra | 4 | 87.3
Geometry | 3 | 81.3
Mechanics | 4 | 80

Exercise

Use a recursive CTE to generate the price brackets 0, 100, 200, 300, 400 (low). Show how many products fall into each bracket (products): a product is in the bracket when low <= price < low + 100. Empty brackets must appear with 0. Sort by low.

Exercise · SQL· Edit the code and run it again

WITH RECURSIVE brackets(low) AS (
  SELECT 0
  UNION ALL
  -- next bracket, stop at 400
)
SELECT b.low, COUNT(p.id) AS products
FROM brackets AS b
-- LEFT JOIN products
GROUP BY b.low
ORDER BY b.low;

▸ Expected output

low | products
0 | 6
100 | 1
200 | 0
300 | 1
400 | 0

Key points

A CTE (WITH name AS (...)) is a named intermediate result that exists for one query only.
The same CTE can be referenced several times; comma-separated CTEs can use the ones before them.
Recursive CTE = anchor + UNION ALL + self-referencing part + stop condition.
Recursive CTEs are used for number series, reports that show empty groups and hierarchies (org charts, categories).
In SQL Server a recursive CTE is written without RECURSIVE and is limited to 100 steps by default.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

What is the main advantage of a CTE over a subquery in FROM?