Trees and sequences: WITH RECURSIVE

In short: WITH RECURSIVE is a query with a step that reads its own result, so it can walk trees such as categories or generate a sequence of dates.

Problem

The path to the category

Above every product the website shows the path to it: “Food > Dairy”. The categories are a tree: each has a parent_id that points to the category above it. With a self join from the module on joins it goes like this:

SQLdatabase: shop

SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON p.id = c.parent_id;

SELECT c.name, p.name AS parent FROM categories c LEFT JOIN categories p ON p.id = c.parent_id;

▶ RunRestore the queryFresh database

This works because the tree has two levels. If tomorrow “Cheeses” appears under “Dairy”, you need one more join, and for four levels one more again. You do not know in advance how many levels there will be.

The idea

A step that reads itself

WITH RECURSIVE lets a step read its own rows. Here is the smallest example, the numbers from 1 to 5:

SQLdatabase: shop

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;

WITH RECURSIVE n(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5 ) SELECT x FROM n;

▶ RunRestore the queryFresh database

The step has two parts, joined by UNION ALL:

1. The start SELECT 1 gives the first row.
2. The recursive part SELECT x + 1 FROM n WHERE x < 5 takes the rows from the previous round and makes new ones: from 1 it makes 2, from 2 it makes 3.

This repeats until the recursive part returns nothing: at x = 5 the condition is no longer true. Such a step is called a recursive CTE.

Without WHERE x < 5 the query would never stop. Every recursive part needs a condition that sooner or later stops it.

The idea

The tree of categories

Now the path. The start is the main categories (no parent). The recursive part finds the children of everything found so far and adds their name to the path:

SQLdatabase: shop

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree
ORDER BY path;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree ORDER BY path;

▶ RunRestore the queryFresh database

In the first round there are “Food” and “Home” (level 1). In the second there are the six subcategories (level 2), each with the path of its parent, “ > ” and its own name. In the third round there are no children and everything stops. If the tree gets deeper, the same query will reach the new levels too.

Try it

Everything in “Food”

Show the name and price of all the products in “Food”, together with its subcategories. Get the category numbers with a recursive step, not by hand. The field starts with the start of the step.

SQLdatabase: shop

SELECT id FROM categories WHERE name = 'Food';

SELECT id FROM categories WHERE name = 'Food';

Check▶ Run

See it

The days without sales

The manager wants a chart of the sales by day for October 2026. The first attempt:

SQLdatabase: shop

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY o.ordered_at;

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31' GROUP BY o.ordered_at;

▶ RunRestore the queryFresh database

Five rows. October has 31 days, and the chart would join 3 October straight to the 5th and hide that nothing was sold from 7 to 17 October. A day without orders is not in orders, so GROUP BY has nothing to make a row from.

The idea

A sequence of dates

A recursive step can make all the days by itself. The start is 1 October, and every round adds one day with date(day, '+1 day'):

SQLdatabase: shop

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

▶ RunRestore the queryFresh database

31 rows. Now the sales are attached to the days with LEFT JOIN, and for the days without orders COALESCE puts 0:

SQLdatabase: shop

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT d.day, COUNT(DISTINCT o.id) AS orders,
  COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue
FROM days d
LEFT JOIN orders o ON o.ordered_at = d.day
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY d.day
ORDER BY d.day;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT d.day, COUNT(DISTINCT o.id) AS orders, COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue FROM days d LEFT JOIN orders o ON o.ordered_at = d.day LEFT JOIN order_items oi ON oi.order_id = o.id GROUP BY d.day ORDER BY d.day;

▶ RunRestore the queryFresh database

All 31 days are here, including the ones with zero. The order of the LEFT JOIN matters: on the left are the days, which you want all of, on the right are the orders, which may be missing.

More precisely, if you are curious

PostgreSQL has a ready-made function for a sequence: generate_series(DATE '2026-10-01', DATE '2026-10-31', INTERVAL '1 day'). In SQLite and in MySQL (from version 8) such a sequence is made with WITH RECURSIVE, as here.

Exercise

Two tasks

1. On how many days in October 2026 did the shop have no orders at all?

Count the days of October 2026 without a single order, up to the end of the month, including the days after the last order (column quiet_days). The field already has the sequence of days.

SQLdatabase: shop

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

Check▶ Run

2. The website wants the full path for every product: “Food > Dairy > White cheese”.

Show the name of every product that has a category and its full path (column breadcrumb), from the main category to the product name. The field already has the tree.

SQLdatabase: shop

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree;

Check▶ Run

Test

Check yourself

1. What two parts does a recursive step have?

SELECT and WHEREA start, UNION ALL, and a part that reads the step itselfTwo CTEs with the same name

2. What happens if you remove WHERE x < 5 from the recursive part?

Nothing: the database stops by itselfIt returns 1 to 5The query never finishes

3. A report by day shows only 5 days of October. Why?

Because BETWEEN does not include the endBecause the dates are textGROUP BY makes rows only for the dates that exist in orders

4. For the path “Food > Dairy”, why is the recursive step better than a self join?

It is fasterIt works for a tree of any depth, not only two levelsIt does not need parent_id

Check

Start the lesson over

Related lessons

• Builds on: LEFT JOIN: rows without a match too · Many tables, and a table joined to itself · Subqueries · A report in steps: WITH
• Continues in: Graph databases
• Tasks on this topic: Subqueries and windows