SQLite Recursive CTEs: WITH RECURSIVE for Trees and Series

Recursion in SQL Sounds Strange — Until You See It

Most queries return rows from data that already exists. A recursive CTE is different: it builds rows by feeding its own output back as input, one step at a time, until there's nothing new to add. That's how you walk a tree of unknown depth, or generate the numbers 1 to 100 without a numbers table.

The shape is always the same:

WITH RECURSIVE name(columns) AS (
    -- anchor: the starting rows
    SELECT ...
    UNION ALL
    -- recursive: rows derived from the previous step
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Anchor on top, UNION ALL, recursive query underneath. SQLite runs the anchor once, then keeps running the recursive part — each time using the rows produced last round — until it returns no new rows. Then it stops.

Counting From 1 to 10

The simplest recursive CTE generates a series. No tables needed:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

Trace through it:

1. Anchor produces one row: n = 1.
2. Recursive step takes that row, computes n + 1 = 2, and 2 < 10 is true, so it keeps the row.
3. Next iteration takes n = 2 and produces n = 3. And so on.
4. When n reaches 10, 10 < 10 is false, the recursive step returns no rows, and SQLite stops.

The WHERE n < 10 is the stopping condition. Without it, the query runs forever.

Generating a Date Series

Same idea, useful in real reports — fill in every day in a range, even days where nothing happened:

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

You'd typically LEFT JOIN this against an events table to count zero-event days correctly. A plain GROUP BY date skips empty days entirely; the date series gives you a row for every day, regardless.

Walking a Parent-Child Tree

The classic use case. Here's an employee table where each row points at its manager:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

The anchor picks the root (the person with no manager). The recursive step joins the employees table back against the CTE, finding everyone whose manager_id matches an id already in the CTE. Each iteration goes one level deeper. depth is just a counter we add to indent the output.

This works for trees of any depth. Two levels, ten levels — the query doesn't change.

Finding All Ancestors of a Specific Row

Flip the direction. Instead of walking down from the root, walk up from a specific employee to find their entire chain of managers:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

The anchor is the starting employee. Each recursive step jumps to the parent. SQLite stops when it hits the root — manager_id IS NULL, so the join finds nothing.

This pattern is useful for breadcrumbs, threaded comments, category paths, and anywhere you need "walk up to the top."

Stopping Conditions and Infinite Loops

The most common bug is forgetting the stopping condition or writing one that never fires. Compare:

-- Runs forever:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

There's no WHERE clause to ever return zero rows. SQLite will happily try to count to infinity.

Two defensive habits:

1. Always have a WHERE clause in the recursive part that bounds growth.
2. Add LIMIT to the outer SELECT as a safety net while you're developing — if you're wrong about the stopping condition, the query still terminates.

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

The CTE itself is unbounded, but LIMIT 5 stops the outer query early. SQLite is smart enough not to keep recursing past what LIMIT needs. Useful for exploring; not a substitute for a real stopping condition in production code.

Cycles in Graphs

Trees can't have cycles. General graphs can — and a naive recursive CTE will loop forever if the data has one. The fix is to track the path you've walked so far and refuse to revisit nodes:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path is a comma-delimited string of nodes already visited. Before adding a new node, the WHERE clause checks it isn't in there. Without that guard, the cycle 1 → 2 → 3 → 1 would loop forever.

There's no built-in "visited set" in SQL — you build one yourself, usually as a string or by joining against the CTE so far.

Recursive CTE vs Self Join

If you only need one or two levels deep, a self join is simpler and faster:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

That handles "who is each person's direct manager." But if you need "everyone who reports up through Ada, no matter how deep" — the depth is unknown — only a recursive CTE handles it cleanly. Pick the tool to match the depth you need:

• Fixed, small depth: self join, maybe two or three of them.
• Unknown or arbitrary depth: WITH RECURSIVE.

Mental Model

A recursive CTE is a loop, written declaratively:

• The anchor is the loop's initial value.
• The recursive query is the loop body — it produces the next batch of rows from the current ones.
• The stopping condition is the loop's exit test — when it returns zero rows, the loop ends.
• UNION ALL accumulates everything into the final result set.

Once that maps in your head, the syntax stops feeling weird. You're writing a for loop in SQL.

Next: Indexes

Recursive CTEs walk a lot of rows, and the join inside the recursive step runs on every iteration. If the join column isn't indexed, performance falls off a cliff fast. Indexes are the next chapter, and manager_id is exactly the kind of column that benefits from one.