SQL CTEs and Recursive Queries: Walk Hierarchies with WITH RECURSIVE

A bicycle workshop wants to know how many spokes, rims and hubs one finished bike needs, and what the parts cost. A bike contains two wheels, each wheel contains 32 spokes, a rim, a hub and a tyre, and the frame and drivetrain are assemblies of their own parts. How deep the nesting goes is not fixed: tomorrow someone adds a "brake assembly" with a sub-assembly inside it. Plain joins need one JOIN per level, so they cannot express "keep going until there is nothing left". A recursive CTE can.

CTEs: giving a subquery a name

A common table expression (CTE) is a named, temporary result defined with WITH name AS (...) and used like a table in the query that follows. Everything it can do, a subquery in FROM can do too, but the name lets you read a query top to bottom in steps instead of inside out. It also lets you refer to one result several times. The schema below models a bill of materials with two tables: parts, and a bom table listing which part contains which, and how many.

CREATE TABLE parts (
  part_id         INTEGER PRIMARY KEY,
  name            TEXT NOT NULL,
  unit_cost_cents INTEGER          -- NULL for assemblies; their cost comes from their parts
);

CREATE TABLE bom (                 -- bill of materials: which part contains which
  parent_id INTEGER NOT NULL REFERENCES parts(part_id),
  child_id  INTEGER NOT NULL REFERENCES parts(part_id),
  qty       INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (parent_id, child_id)
);

INSERT INTO parts VALUES
  (1, 'Bicycle', NULL), (2, 'Frame', NULL), (3, 'Wheel', NULL), (4, 'Drivetrain', NULL),
  (5, 'Rim', 1800), (6, 'Spoke', 20), (7, 'Tyre', 1200), (8, 'Hub', 2500),
  (9, 'Chain', 1500), (10, 'Crankset', 4200), (11, 'Cassette', 3100),
  (12, 'Fork', 5500), (13, 'Tube set', 9000);

INSERT INTO bom VALUES
  (1, 2, 1), (1, 3, 2), (1, 4, 1),
  (2, 12, 1), (2, 13, 1),
  (3, 5, 1), (3, 6, 32), (3, 7, 1), (3, 8, 1),
  (4, 9, 1), (4, 10, 1), (4, 11, 1);

The first CTE computes each assembly's direct cost, the cost of its immediate costed children only.

WITH direct_cost AS (
  SELECT b.parent_id,
         SUM(b.qty * c.unit_cost_cents) AS cents
  FROM bom AS b
  JOIN parts AS c ON c.part_id = b.child_id
  WHERE c.unit_cost_cents IS NOT NULL
  GROUP BY b.parent_id
)
SELECT p.name, d.cents
FROM direct_cost AS d
JOIN parts AS p ON p.part_id = d.parent_id
ORDER BY d.cents DESC;

name       | cents
-----------+------
Frame      | 14500
Drivetrain |  8800
Wheel      |  6140

The wheel's 6140 cents is a rim (1800), 32 spokes at 20, a tyre (1200) and a hub (2500). It does not include anything inside the frame or drivetrain, and the bicycle itself does not appear because its children are all assemblies with no cost of their own. Answering the real question needs recursion.

Recursion: an anchor and a step

A recursive CTE has two parts joined by UNION ALL. The anchor query runs once and produces the starting rows. The recursive step refers to the CTE's own name, and runs repeatedly: each time it takes the rows produced in the previous round and finds their children. The process stops when a round produces no new rows. Think of it as a queue: the anchor seeds it, and the step keeps replacing each row with its children until the leaves are reached.

The query below carries three extra columns down the tree: a path string for sorting and display, a depth, and qty, the number of that part needed per bicycle, obtained by multiplying quantities along the way.

WITH RECURSIVE explode(part_id, name, path, depth, qty) AS (
  -- anchor: start from the finished bicycle
  SELECT part_id, name, name, 0, 1
  FROM parts
  WHERE part_id = 1

  UNION ALL

  -- recursive step: add the children of every row found so far
  SELECT b.child_id, p.name, e.path || ' > ' || p.name, e.depth + 1, e.qty * b.qty
  FROM explode AS e
  JOIN bom   AS b ON b.parent_id = e.part_id
  JOIN parts AS p ON p.part_id   = b.child_id
)
SELECT substr('        ', 1, depth * 2) || name AS part, depth, qty AS per_bicycle
FROM explode
ORDER BY path;

part         | depth | per_bicycle
-------------+-------+------------
Bicycle      |     0 |           1
  Drivetrain |     1 |           1
    Cassette |     2 |           1
    Chain    |     2 |           1
    Crankset |     2 |           1
  Frame      |     1 |           1
    Fork     |     2 |           1
    Tube set |     2 |           1
  Wheel      |     1 |           2
    Hub      |     2 |           2
    Rim      |     2 |           2
    Spoke    |     2 |          64
    Tyre     |     2 |           2

The indentation shows the hierarchy. The wheel line says qty = 2, since a bike has two wheels, and its spokes show 64, which is 2 wheels times 32 spokes per wheel: the multiplication happens in e.qty * b.qty. Sorting by path lists each assembly directly above its parts. The query never mentions how many levels exist, so an extra layer of sub-assemblies would be picked up automatically.

Costing the whole bike

With the exploded list available as a CTE, the costing question is an ordinary join and aggregate. Only parts with a unit cost are real, physical items.

WITH RECURSIVE explode(part_id, name, path, depth, qty) AS (
  -- anchor: start from the finished bicycle
  SELECT part_id, name, name, 0, 1
  FROM parts
  WHERE part_id = 1

  UNION ALL

  -- recursive step: add the children of every row found so far
  SELECT b.child_id, p.name, e.path || ' > ' || p.name, e.depth + 1, e.qty * b.qty
  FROM explode AS e
  JOIN bom   AS b ON b.parent_id = e.part_id
  JOIN parts AS p ON p.part_id   = b.child_id
)
SELECT p.name, e.qty AS per_bicycle, p.unit_cost_cents AS each_cents,
       e.qty * p.unit_cost_cents AS line_cents
FROM explode AS e
JOIN parts AS p ON p.part_id = e.part_id
WHERE p.unit_cost_cents IS NOT NULL
ORDER BY line_cents DESC;

name     | per_bicycle | each_cents | line_cents
---------+-------------+------------+-----------
Tube set |           1 |       9000 |       9000
Fork     |           1 |       5500 |       5500
Hub      |           2 |       2500 |       5000
Crankset |           1 |       4200 |       4200
Rim      |           2 |       1800 |       3600
Cassette |           1 |       3100 |       3100
Tyre     |           2 |       1200 |       2400
Chain    |           1 |       1500 |       1500
Spoke    |          64 |         20 |       1280

WITH RECURSIVE explode(part_id, name, path, depth, qty) AS (
  -- anchor: start from the finished bicycle
  SELECT part_id, name, name, 0, 1
  FROM parts
  WHERE part_id = 1

  UNION ALL

  -- recursive step: add the children of every row found so far
  SELECT b.child_id, p.name, e.path || ' > ' || p.name, e.depth + 1, e.qty * b.qty
  FROM explode AS e
  JOIN bom   AS b ON b.parent_id = e.part_id
  JOIN parts AS p ON p.part_id   = b.child_id
)
SELECT SUM(e.qty * p.unit_cost_cents) AS bicycle_cents,
       SUM(CASE WHEN p.unit_cost_cents IS NOT NULL THEN e.qty END) AS physical_parts
FROM explode AS e
JOIN parts AS p ON p.part_id = e.part_id;

bicycle_cents | physical_parts
--------------+---------------
        35580 |             75

A bicycle needs 75 physical parts and costs 35,580 cents in components, the sum of the line costs. Assemblies have a NULL cost, so multiplying by their quantity yields NULL and SUM skips those rows, the NULL behaviour covered in SQL Fundamentals.

Generating rows that do not exist

Recursion is also the portable way to create a sequence, for example a calendar to LEFT JOIN against so that days without data still appear. The anchor is the first date; each step adds a day while the date is before the end.

WITH RECURSIVE days(day) AS (
  SELECT '2025-06-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2025-06-07'
)
SELECT day, strftime('%w', day) AS weekday_no
FROM days;

day        | weekday_no
-----------+-----------
2025-06-01 | 0
2025-06-02 | 1
2025-06-03 | 2
2025-06-04 | 3
2025-06-05 | 4
2025-06-06 | 5
2025-06-07 | 6

This ran on SQLite, which has the date() function used here. PostgreSQL offers generate_series for the same purpose, and SQL Server has its own date functions, so the recursion pattern ports but the date arithmetic does not.

Cycles: how a recursive query never ends

Real hierarchies sometimes contain mistakes. Suppose someone records that a Cassette contains a Bicycle. The data now has a cycle: bicycle, drivetrain, cassette, bicycle, and so on. A recursive walk with no guard would keep finding new rows forever, because every round produces rows.

-- A data-entry mistake: someone records that a Cassette contains a Bicycle
INSERT INTO bom VALUES (11, 1, 1);

The simplest protection is a depth limit in the recursive part's WHERE. It does not repair the data, but it guarantees termination, as the first query below shows. A better guard records the path walked so far and refuses to enter a part that is already on it. After that guard, the cycle edge is simply never followed.

WITH RECURSIVE walk(part_id, depth) AS (
  SELECT 1, 0
  UNION ALL
  SELECT b.child_id, w.depth + 1
  FROM walk AS w
  JOIN bom AS b ON b.parent_id = w.part_id
  WHERE w.depth < 8              -- safety net: stop after 8 levels
)
SELECT MAX(depth) AS deepest, COUNT(*) AS rows_produced FROM walk;

deepest | rows_produced
--------+--------------
      8 |            39

WITH RECURSIVE walk(part_id, trail, depth) AS (
  SELECT 1, ',1,', 0
  UNION ALL
  SELECT b.child_id, w.trail || b.child_id || ',', w.depth + 1
  FROM walk AS w
  JOIN bom AS b ON b.parent_id = w.part_id
  WHERE instr(w.trail, ',' || b.child_id || ',') = 0   -- never revisit a part already on this path
)
SELECT COUNT(*) AS rows_produced, MAX(depth) AS deepest FROM walk;

rows_produced | deepest
--------------+--------
           13 |       2

With the depth guard the walk made it eight levels down and returned 39 rows, most of them junk produced by going round the loop. With the visited-path check, it returned the 13 rows the real tree holds, since the cycle edge added nothing new. PostgreSQL 14 and later also offers a CYCLE clause that automates this, but the manual version above works everywhere.

Common mistakes

• No termination guard on cyclic data. Symptom: the query runs until you cancel it or the server runs out of memory. Use a depth limit or a visited-path check.
• Missing the RECURSIVE keyword. PostgreSQL and MySQL require WITH RECURSIVE; leaving it out gives an error such as relation "explode" does not exist. SQL Server and Oracle do not use the keyword at all.
• Anchor and step with different columns. Error: the number of columns must match. The recursive step must return exactly the same list in the same order.
• Treating a CTE as a performance hint. Depending on the database and version, a CTE may be inlined or materialised; the keyword itself does not make a slow query fast. Check the query plan.
• Double counting in a shared-part structure. If a part appears under two parents, the walk returns it twice, which is correct for quantities and wrong for a distinct list. Decide which you need and use DISTINCT deliberately.

How this shows up in real projects and interviews

Recursive CTEs appear wherever data nests: category trees, threaded comments, organisation charts, folder structures, bills of materials and dependency graphs. In interviews they come up as "print all reports under a manager" or "find all ancestors of a node", and the usual follow-up is "what if the data has a cycle?". Non-recursive CTEs matter every day: teams use them to break a long query into named steps that colleagues can review. When a recursive walk is slow, the join column in the recursive step (bom.parent_id here) is the first place to look for a missing index; see Database Indexing.