Level 20: Recursive queries: WITH RECURSIVE

Lesson

A recursive CTE is a CTE that reads itself. It is used for two things ordinary queries cannot do: generate a series of values that exists in no table, and walk through a hierarchy whose depth is unknown.

It has two parts linked by UNION ALL. The starting case is an ordinary SELECT that produces the first rows. The recursive step is a SELECT that reads the CTE itself and builds new rows from those produced in the previous round.

SQL unrolls the calculation round by round. In the example, the start produces x = 1. In round 1, the step reads 1 and produces 2; in round 2, it reads 2 and produces 3, and so on up to 5. In the next round, the condition x < 5 is false for 5: the step produces nothing more and the calculation stops. The result gathers all the rows produced: 1, 2, 3, 4, 5. The WHERE of the recursive step is therefore the stop condition.

Use 1, series. With date(day, '+1 day') or strftime('%Y-%m', month || '-01', '+1 month'), seen at level 6, you generate every day or every month of a period. A LEFT JOIN then attaches the data: months without activity appear with 0.

Use 2, hierarchies. The start selects the root, for example the employee without a manager (Alice, depth 0). Each round joins staff to the CTE to find the subordinates of the people found in the previous round: Bob, Claire and David in round 1, their teams in round 2. The calculation stops when nobody has any subordinate left.

If the data contains a cycle (A manager of B, B manager of A), the query runs forever. There are two safeguards. The safest: a depth limit in the recursive step, for example WHERE depth < 20. The other: link the start and the step with UNION instead of UNION ALL; a row already produced is then not produced again, and the loop stops when the cycle comes back to an identical row. Careful: if the row contains a counter such as depth, it is always new and UNION is no longer enough.

Like any query, a recursive CTE does not guarantee the order of the rows: add ORDER BY in the final query whenever the order matters.

Syntax

WITH RECURSIVE t(x) AS (
  SELECT start
  UNION ALL
  SELECT x + 1
  FROM t
  WHERE x < last
)
SELECT x
FROM t;

Worked example

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

Start: 1. Each round adds x + 1 as long as x < 5. Result: 1, 2, 3, 4, 5.

Example result

Key points

• Start UNION ALL recursive step.
• The recursive step reads the CTE itself.
• The recursive step's WHERE stops the loop.

Common pitfalls

• Forgetting the stop condition: the query runs forever.
• The starting case and the recursive step must have the same number of columns.

The level’s 5 exercises

1. Guided · Show the numbers from 1 to 10 in a column x. (Table: )
2. Practice · Show every day from July 1st to July 7th, 2025 in a column day. (Table: )
3. Practice · Show the name of everyone who works under Bob (id 2), directly or indirectly. (Table: staff)
4. Practice · Show each employee with their depth in the org chart (depth): 0 for the person without a manager, 1 for their direct reports, and so on. (Table: staff)
5. Challenge · Show the number of loans in each month from January to June 2025 (YYYY-MM format), including the months without any loan, in chronological order. (Table: loans)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic