Module 4: Subqueries and Common Table Expressions ⏱ 20 min

Recursive CTEs

By the end of this lesson you will be able to:
  • Write a recursive CTE with an anchor member and a recursive member
  • Use UNION ALL to combine anchor and recursive results
  • Apply a recursive CTE to generate sequences and analyse data in tiers

Most queries look at data that already exists. But some problems need you to build data row by row: generate a sequence of numbers, walk an organisational chart from manager to reports, or explore a graph of connections.

A recursive CTE does exactly that. It starts with an anchor member — one or more fixed rows — and then repeatedly runs a recursive member that feeds on its own previous output. Each pass produces new rows, and the process stops when the recursive member returns nothing. The database gathers every row from every pass and presents them as a single result set.

Recursive CTEs are one of SQL's most powerful features, and they are the cleanest way to handle hierarchies and sequences without leaving the database.

flowchart TD
  A["Anchor: n = 1"] --> U["UNION ALL"]
  U --> R["Recursive: n = previous + 1"]
  R -->|"n < 5?"| U
  R -->|"no new rows"| D["Done"]
  U -->|"collected result"| O["Output rows"]
  style A fill:#0e7490,color:#fff
  style R fill:#0e7490,color:#fff
  style O fill:#1e293b,color:#fff
A recursive CTE alternates between an anchor and a recursive step until no new rows are produced.

The syntax: WITH RECURSIVE

A recursive CTE adds the keyword RECURSIVE after WITH and contains two SELECT statements joined by UNION ALL.

WITH RECURSIVE nums(n) AS (
  SELECT 1               -- anchor member
  UNION ALL
  SELECT n + 1           -- recursive member
  FROM nums
  WHERE n < 5           -- termination guard
)
SELECT n FROM nums;

The anchor produces the starting row: n = 1. The recursive member reads the rows produced so far, increments each one, and feeds the results back into the same CTE. The WHERE n < 5 clause is critical — without it the recursion would never end, and the database would hit its recursion limit.

Every recursive CTE must have a termination condition in the recursive member. If the WHERE clause eventually fails for every row, the recursion stops and the accumulated result is returned.

Generate the numbers 1 through 5 using a recursive CTE.
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;

Applying recursion to real data

Number generators are the classic teaching example, but recursive CTEs also shine when you need to create tiers or thresholds and then analyse your data against them.

The query below builds a ladder of salary thresholds starting at 400000 and stepping by 100000 up to 900000. It then joins those thresholds against the employees table to count how many people earn at least each threshold. Without a recursive CTE, you would have to hard-code the thresholds as a VALUES list or a temporary table. The CTE lets the database build the ladder for you.

Notice how the recursive member references the CTE itself: FROM thresholds. That self-reference is what makes the CTE recursive — it reads its own previous output to build the next rung.

Generate salary thresholds and count employees at each tier using a recursive CTE.
WITH RECURSIVE thresholds(sal) AS (
  SELECT 400000
  UNION ALL
  SELECT sal + 100000 FROM thresholds WHERE sal < 900000
)
SELECT t.sal, COUNT(e.id) AS employee_count
FROM thresholds t
LEFT JOIN employees e ON e.salary >= t.sal
GROUP BY t.sal;
flowchart LR
  T1["sal = 400000"] -->|"+100000"| T2["sal = 500000"]
  T2 -->|"+100000"| T3["sal = 600000"]
  T3 -->|"..."| T4["sal = 900000"]
  T4 -->|"+100000 > 900000? no"| STOP["Stop"]
  style T1 fill:#0e7490,color:#fff
  style T2 fill:#0e7490,color:#fff
  style T4 fill:#0e7490,color:#fff
  style STOP fill:#b45309,color:#fff
The recursive member reads the previous output and produces new rows until the guard fails.

Recursion on dates and ranges

Recursive CTEs are not limited to numbers. You can step through dates, string prefixes, or any data that follows a predictable pattern. The anchor sets the starting point, the recursive member advances it, and the guard decides when to stop.

When you join a recursive CTE to real tables, you turn a static report into a dynamic tiered analysis. The CTE builds the framework, and the join fills it with data. This pattern — recursive framework plus analytical join — is how you answer questions like 'how many hires did we have in each year up to today?' without hard-coding the years.

Exercise

Use a recursive CTE named nums to generate the numbers 1 through 5. The anchor should start at 1, and the recursive member should add 1 until n reaches 5.

WITH RECURSIVE nums(n) AS (
  -- anchor
  UNION ALL
  -- recursive member
)
SELECT n FROM nums;
Exercise

Use a recursive CTE named thresholds(sal) to generate salary thresholds starting at 400000 and increasing by 100000 up to and including 900000. Then LEFT JOIN employees and count how many employees earn at least each threshold. Return sal and employee_count.

WITH RECURSIVE thresholds(sal) AS (
  SELECT 400000
  UNION ALL
  SELECT sal + 100000 FROM thresholds WHERE sal < 900000
)
SELECT t.sal, COUNT(e.id) AS employee_count
FROM thresholds t
LEFT JOIN employees e ON e.salary >= t.sal
GROUP BY t.sal;
Exercise

This recursive CTE is meant to generate numbers 1 to 5, but it lacks a termination condition and will recurse forever. Add the missing WHERE guard.

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums
)
SELECT n FROM nums LIMIT 5;
Exercise

Use a recursive CTE named years(y) to generate the years 2019 through 2023. Then, for each year, count how many employees were hired in that year or earlier. Return the year and the cumulative count as hired_by_year. You can use strftime('%Y', hired_on) to extract the year from a date.

WITH RECURSIVE years(y) AS (
  SELECT 2019
  UNION ALL
  SELECT y + 1 FROM years WHERE y < 2023
)
SELECT y.y, COUNT(e.id) AS hired_by_year
FROM years y
LEFT JOIN employees e ON strftime('%Y', e.hired_on) <= CAST(y.y AS TEXT)
GROUP BY y.y;
Exercise

What are the two required parts of a recursive CTE?

Recap

  • A recursive CTE is defined with WITH RECURSIVE and contains an anchor member and a recursive member joined by UNION ALL.
  • The recursive member references the CTE itself, producing new rows from previous ones until no more are generated.
  • A termination condition in the recursive member's WHERE clause is mandatory — without it the recursion runs forever.
  • Recursive CTEs can generate sequences, tiers, and ranges, which you then join to real data for dynamic analysis.

Next you will chain multiple CTEs together in a single query, building layered analytical pipelines where each stage feeds the next.

Checkpoint quiz

What keyword must follow WITH to make a CTE recursive?

Why is a WHERE clause essential in the recursive member?

Go deeper — technical resources