WITH RECURSIVE — Generate Sequences and Walk Hierarchies with Recursive Queries

Data used in this article — the employee table

WITH RECURSIVE is a way to write a CTE that calls itself to produce rows one at a time.

This motion of feeding the previous result back as the next input and repeating is called recursion.

The material is the employee table (30 rows; the manager_id column points at the boss's emp_id, a self-referential structure).

Before starting the exercises, take a look at the employee table's column definitions and sample data, and also at the minimal SELECT we'll use as a recursive CTE's anchor.

① Run SELECT 1; and confirm that it returns just the value 1 in a single row (this is the simplest SELECT we'll use as the anchor of a recursive CTE).

② Run PRAGMA table_info(employee); to check the column definitions.

③ Run SELECT * FROM employee LIMIT 5; to preview the first 5 rows. Also notice how NULL appears in the manager_id column (employees who have no boss).

The 3 parts that make up a recursive query — anchor / recursive term / UNION ALL

Using the code below, we'll walk through the 3 parts — ① the anchor (SELECT 1) / ② the recursive term (SELECT n + 1 FROM seq WHERE n < 5) / ③ `UNION ALL` — one at a time.

-- Reference code for this section. We'll cover the 3 parts ① / ② / ③ in order
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (the glue between anchor and recursive term)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② recursive term
)
SELECT n FROM seq;

-- Result (5 rows): n=1, 2, 3, 4, 5

① Anchor — the SELECT 1 part

The anchor is the SELECT that produces the first row.

SELECT 1 in the reference code is the anchor — it's just an ordinary SELECT, written once, that doesn't reference itself.

The value (1) returned here becomes the first content of seq(n), which is then handed to the recursive term as the initial value of n.

② Recursive term — the SELECT n + 1 FROM seq WHERE n < 5 part

-- Reference code (repeated). This section explains the ② recursive-term line
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (the glue)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② recursive term ← what we're explaining ★
)
SELECT n FROM seq;

In WITH RECURSIVE seq(n), the seq(n) part is the declaration of the CTE name (seq) and the column name (n). The content starts as the anchor's `1`, and from then on it holds the result of the recursive term.

SELECT n + 1 means "the previous value + 1" each time.

The initial n is 1, so the next n + 1 is 2, then 3, and so on — this continues until WHERE stops returning rows.

③ UNION ALL — the `UNION ALL` part of the code

-- Reference code (repeated). This section explains the ③ UNION ALL line
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- anchor
  UNION ALL                           -- ★ what we're explaining ★ looks like one line, but…
  SELECT n + 1 FROM seq WHERE n < 5   -- recursive term
)
SELECT n FROM seq;

-- …inside the DBMS this is equivalent to "stack what the recursive term produces, one UNION ALL per iteration":

SELECT 1            -- anchor (runs only once)
UNION ALL           -- ← 1st stack: piling on pass-1's output
SELECT 2
UNION ALL           -- ← 2nd stack
SELECT 3
UNION ALL           -- ← 3rd stack
SELECT 4
UNION ALL           -- ← 4th stack
SELECT 5;

-- Result is [1, 2, 3, 4, 5] either way
-- In other words, the single "UNION ALL" inside WITH RECURSIVE ≒ UNION ALL repeated for each iteration

UNION ALL is an operator that stacks the results of two or more SELECTs vertically.

We use UNION ALL (which keeps all rows) instead of UNION (which removes duplicates) because we want every row produced during the recursion to land in the final result.

Example 1 — Generate a sequence (1 to 5)

Requirement: "I want to generate a sequence from 1 to 10 on the fly as rows, without an existing sequence table." Even without a master sequence table, you can build it with a recursive CTE. (If you run it correctly, the explanation will appear.)

① Define a CTE for the sequence with WITH RECURSIVE. The anchor should be a SELECT that returns the initial value 1.

② In the recursive term, reference the CTE itself and return the previous value plus 1. The termination condition should be "keep going as long as the current value is less than 10."

③ In the main query, return the generated sequence in ascending order.

Example 2 — Salary growth projection (what's the salary N years from now at 5% per year?)

Example 1 was an abstract numeric sequence, but here we'll cover a practical recursion that starts from actual `employee` table data.

Given Sam (emp_id=19)'s current salary, we'll project what it would be in 5 years if it goes up 5% every year.

Just change the recursive term to projected * 1.05 and you get a series that grows compoundingly.

-- Salary growth projection: if Sam (emp_id=19)'s salary goes up 5% per year, what is it N years later?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- anchor: pull the current salary from employee
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- recursive term: 5% raise per year
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- Result (6 rows): a 5-year projection starting from Sam's current salary 4100000
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← anchor (employee.salary as-is)
--    1  | 4305000   ← recursive term (4100000 × 1.05)
--    2  | 4520250   ← recursive term (4305000 × 1.05)
--    3  | 4746263   ← recursive term (4520250 × 1.05, ROUND applied)
--    4  | 4983576   ← recursive term (4746262.5 × 1.05, ROUND applied)
--    5  | 5232754   ← recursive term. Next, year=5 makes WHERE year<5 false → stop

There are two differences from the sequence example.

① The anchor pulls a value from the employee table (SELECT 0, salary FROM employee WHERE emp_id = 19) — you can start from real data.

② It's a 2-column CTE (year and projected), and the recursive term updates both columns at once with year + 1 and projected * 1.05.

Once you can pull a value from a real table in the anchor, you can write simulations that start from that value (compound interest, population projections, steps-to-goal calculations, and so on).

Requirement: "Starting from Bob (emp_id=2)'s current salary, I want to project what it'd be in 5 years if it goes up 10% per year." (If you run it correctly, the explanation will appear.)

① Define the CTE name as growth and the columns as year and projected in WITH RECURSIVE.

② The anchor should pull salary from employee for emp_id = 2, with year starting at 0.

③ The recursive term should return year + 1 and projected * 1.10, with the termination condition year < 5.

④ In the main query, return year and ROUND(projected) AS projected_salary in ascending order.

Example 3 — Use with JOIN (walk the manager_id chain one step at a time)

Next we'll cover the other canonical use of recursive CTEs, walking a self-referential hierarchy.

employee.manager_id holds "the emp_id of your boss", so by following it step by step you can walk upward through the org chart: employee → boss → boss's boss, and so on.

In the sequence example we used n+1 to produce the next value; here we pull the next row (the boss) with a JOIN to advance the chain.

-- Walking the hierarchy: from a starting employee (emp_id=19, Sam) upward through the bosses
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① anchor: the starting row
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② recursive term: pull the joined row
)
SELECT emp_id, name, manager_id FROM chain;

-- Result (2 rows):
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← anchor (starting point)
--     2   | Bob  |  NULL       ← row produced by the recursive term; manager_id is NULL so the next pass stops

① The anchor (SELECT ... FROM employee WHERE emp_id = 19) pulls the starting row.

Here it puts emp_id=19 (Sam)'s row into chain as its first content.

Note that WITH RECURSIVE chain AS (...) doesn't have a column-name declaration like chain(...). That's because the anchor's SELECT already gives chain natural column names (emp_id / name / manager_id) from employee's columns. When it's already readable, you can omit it.

② The recursive term (JOIN chain c ON e.emp_id = c.manager_id) joins employee with the previous chain and pulls the joined row into chain.

Once the joined row's manager_id becomes NULL, the next iteration can't find a join partner, the result is 0 rows, and the recursion stops naturally.

(There's no need for an explicit termination condition like the sequence example's WHERE n < 5 — the end of the hierarchy is the termination condition.)

Requirement: "Starting from emp_id = 19 (Sam), I want to walk the manager_id chain upward — Sam's boss, then that person's boss, and so on — to list the chain of ranks."

① Define a CTE for the hierarchy with WITH RECURSIVE. The anchor should pull one row (emp_id, name, manager_id) from employee for emp_id = 19.

② In the recursive term, join employee with the CTE on "employee.emp_id = the CTE's manager_id" and pull the boss's row (emp_id, name, manager_id) one step at a time.

③ In the main query, return emp_id, name, manager_id.

Practice 3 walked from below upward through bosses.

Now we'll go the other direction: "Starting from emp_id = 1 (Alice), I want to walk the manager_id chain downward through Alice's reports, then their reports, and so on — to list everyone on Alice's team."

① Define a CTE for reports with WITH RECURSIVE. The anchor should pull one row (emp_id, name, manager_id) from employee for emp_id = 1.

② In the recursive term, join employee with the CTE on "employee.manager_id = the CTE's emp_id" and pull the current row's reports (emp_id, name, manager_id) one step at a time.

③ In the main query, return emp_id, name, manager_id in ascending order of emp_id.

Example 4 — Recursion that produces multiple rows at once (the whole org tree)

Example 3 walked one row at a time (Sam → Bob → stop).

Here we'll cover the case where the anchor or the recursive term produces multiple rows at once.

The scenario: "Start from the 5 executives (employees who have no boss) and pull the entire 30-person org tree at once." You'll see the recursive query walking multiple branches in parallel in a breadth-first way.

-- Start from all executives (employees with no boss) and recursively pull everyone in the org
WITH RECURSIVE org_tree AS (
  -- ① anchor: 5 employees with manager_id = NULL (returns multiple rows at once)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② recursive term: pull the direct reports of the previous rows (also multiple rows)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- Result (30 rows = the whole company):
--   Anchor (5 rows): emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   Recursive term pass 1 (25 rows): Alice's 9 reports + Bob's 8 reports + Carol's 8 reports
--   Recursive term pass 2 (0 rows): the reports have no reports of their own → stop
--   Total: 5 + 25 = 30 rows

There are two differences from Example 3.

① The anchor returns multiple rows (5) at once — every row matching WHERE manager_id IS NULL is used to initialize the CTE.

② The recursive term walks multiple branches in parallel — with 5 people in org_tree, one pass of the recursive term pulls each person's reports at the same time within the same iteration (Alice's 9 + Bob's 8 + Carol's 8 = 25 rows).

In other words, recursive queries can do more than "dive deeper one row at a time" — they can also expand multiple branches in a breadth-first parallel way.

Requirement: "Starting from Bob (emp_id=2) and Carol (emp_id=3) at the same time, I want to pull their reports in one query." (If you run it correctly, the explanation will appear.)

① The CTE name should be team in WITH RECURSIVE; omit the column-name declaration (use employee's natural column names).

② The anchor should pull two rows from employee where emp_id is 2 or 3.

③ The recursive term should join employee with the CTE on "employee.manager_id = the CTE's emp_id" and pull the reports.

④ In the main query, return emp_id, name, manager_id in ascending order of emp_id.

Tips — Why use WITH RECURSIVE?

Three situations where WITH RECURSIVE shines

Recursive queries are for situations where plain SQL either can't do it or makes it really painful. The main use cases are three:

① Walk a hierarchy — org charts, folders, bills of materials — self-referential structures where you don't know how deep they go. Instead of stacking several JOINs by hand, you can write it in a form that stops naturally at the end of the data.

② Generate a lot of rows — sequences, consecutive dates, calendars — instead of hand-writing UNION ALL a hundred times, a single line with WHERE does it.

③ Push an app-side loop into SQL — writing a loop in the app to walk a parent ID hits the N+1 query problem, but a recursive query handles it in a single query.

If plain JOIN or GROUP BY will do, you don't need recursion. But when you're in one of the three situations above, it's the first tool to reach for.