SQLite Recursive CTE: Examples for Counts, Dates, and Trees

Generating counters and date ranges in SQLite is one use for a recursive CTE. A recursive CTE is a named query that refers to itself, using rows from one pass to produce the next.

I’ll show how to count to a fixed value and generate dates in a range.

TL;DR

Use WITH RECURSIVE to combine an anchor SELECT with a recursive SELECT that reads from the CTE name. I keep the anchor and recursive members separate so the starting rows and the continuation rule stay visible.

  • The anchor supplies the first row or rows.
  • The recursive SELECT derives another set from the prior set.
  • Stop when the recursive SELECT returns no rows, and add a LIMIT when you know a safe row ceiling.

What is a SQLite recursive CTE?

A common table expression (CTE) is a named query available to one SQL statement.

The recursive form can refer to its own name, letting each pass use the preceding rows as input. I use the terms anchor and recursive member because they identify which SELECT seeds the rows and which advances them.

The first SELECT is the anchor. The SELECT after UNION ALL is the recursive member.

SQLite processes recursive rows through a queue. Each row removed from the queue is used by the recursive SELECT to produce more rows, which return to the queue. This model explains why the recursive SELECT follows the row from the preceding step rather than rescanning all prior output as a single growing table.

The recursive SELECT must reference the CTE in its FROM clause. SQLite also restricts that SELECT: aggregate and window functions cannot appear there. Do those calculations in the outer SELECT or in a separate ordinary CTE after the recursive walk.

The site’s SQLite CTE guide covers the named-query form. This page focuses on the extra self-reference and its stopping rules.

How to write a SQLite recursive CTE step by step

A counter is a compact way to see how the two SELECT statements work. I ran the following query with SQLite 3.53.1.

Step 1: Count to a fixed value

The anchor creates 1. The recursive SELECT adds 1 while n is less than 5, so it produces no row after 5.

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

SQLite returned these rows:

1
2
3
4
5

The name counter(n) declares the CTE and its output column. The condition belongs in the recursive member because that is where the next value is decided.

Step 2: Generate dates in a range

SQLite’s date() function can advance an ISO date by one day. This query includes both endpoints because it recurses while the current date is before the end date.

WITH RECURSIVE days(d) AS (
  VALUES('2026-11-14')
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-11-16'
)
SELECT d FROM days;

Output:

2026-11-14
2026-11-15
2026-11-16

Replace the two date literals with the start and end dates for your report. You can then left join this CTE to daily totals so dates without matching activity remain in the result.

Step 3: Walk a parent-child table

For a hierarchy, join each row’s parent key to the id from the previous pass. This example starts at Ada and carries a depth column with each employee.

CREATE TABLE employee_data(
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER
);
INSERT INTO employee_data VALUES
  (1, 'Ada', NULL), (2, 'Bo', 1), (3, 'Cy', 2), (4, 'Dee', 3);

WITH RECURSIVE employees(id, name, manager_id, depth) AS (
  VALUES(1, 'Ada', NULL, 0)
  UNION ALL
  SELECT e.id, e.name, e.manager_id, employees.depth + 1
  FROM employee_data AS e
  JOIN employees ON e.manager_id = employees.id
  WHERE employees.depth < 4
)
SELECT name, depth FROM employees;

SQLite returned:

name  depth
----  -----
Ada   0
Bo    1
Cy    2
Dee   3

The join finds rows whose manager_id matches an id from the prior pass. The depth condition is a defensive cap. Leaf employees also end the traversal because they have no children.

This sample assumes id 1 is the root and each employee has one manager. To start from a different employee, change the anchor row.

To walk upward instead, begin with one employee and join the current row’s manager_id to the next employee_data.id. That reversed join returns the chain of managers up to the root.

The depth value starts at zero and increases once per child edge from the start node. It is useful for indentation or filtering, but it does not determine display order by itself. Add an ordering key if sibling order matters, then sort the completed CTE in the outer SELECT.

SQLite recursive CTE limits and cycle checks

SQLite recursive CTE limits include restrictions on the recursive SELECT and a row cap through LIMIT. I use UNION ALL for a sequence and reserve UNION for traversals where duplicate full rows represent revisits.

UNION removes duplicates across all CTE columns and retains earlier rows to compare against. UNION ALL avoids that duplicate check, so it fits a sequence where every generated value is distinct. For graphs with repeated edges, choose the operator based on the columns your walk returns.

I tested a query that omitted its column name. SQLite returned this error:

OperationalError: no such column: x

The CTE column list is a useful guard against naming mistakes, as the error below shows. I declare the output columns in the CTE header in these examples so the recursive SELECT uses the same names consistently.

Use UNION and LIMIT for different safeguards

UNION ALL keeps repeated rows, which is useful for a counter or date series. In a graph walk where the full row is just a node id, UNION discards duplicate nodes and can prevent a cycle from repeating the same node.

For a graph path, UNION only stops a repeat when every selected column matches an earlier row. If the CTE also carries depth or a path string, reaching the same node at another depth creates a different row. Use a depth cap or track visited node ids explicitly in that case.

SQLite permits LIMIT on the recursive SELECT, and its count applies to rows added to the recursive table, including the anchor rows. I checked a CTE with LIMIT 5 and it returned five rows. The guard is useful when you know the expected ceiling.

A row cap does not prove the result is complete. If it stops a query before the intended end, increase the bound only after checking why the recursion continues. Keep a stopping predicate that matches the task, such as n less than the endpoint or a child join that finds no more rows.

SQLite disallows aggregate and window functions in the recursive SELECT. First walk the rows, then group or rank the resulting CTE in the outer query. This keeps each iteration visible in the final rows, where you can check whether the child id advances as intended.

For ordinary query results, the SQLite LIMIT guide explains how row caps work. A recursive LIMIT also bounds how many rows the walk can add.

For a hierarchy, a depth cap can limit how far you descend. A separate visited-node check may be needed for graph paths where rows contain more than the node id, since UNION compares entire rows rather than one selected field.

CaseFix
Missing column nameDeclare the CTE output column or alias the anchor expression.
Counter or date sequenceUse UNION ALL with a condition that reaches a known endpoint.
Graph cycleUse UNION when duplicate full rows identify visited nodes, or carry an explicit visited path.
Unknown hierarchy depthAdd a depth ceiling while validating the parent-child join.

Choose the columns carried between iterations before writing the recursive SELECT. The counter carries one integer, the date example carries one date, and the hierarchy carries an employee id with a depth.

Each recursive row must have the same number of columns as the anchor. These columns describe the state available to the next iteration.

Check the boundary with a small result before increasing the range. For the counter, inspect the first and last values. For the date range, check that both endpoints appear.

For a hierarchy, compare the returned parent and child relationships with the source table. A query that stops can still miss intended rows when its join or stopping condition excludes them.

Conclusion: bound the recursive query

A SQLite recursive CTE combines a seed query with a query that derives the next rows. Give it a stopping predicate, and add a row or depth bound when the expected range is known.

FAQ

These answers cover the two choices that shape a recursive CTE.

What does the anchor SELECT do?

It returns the initial rows that start the recursive query. The recursive SELECT uses those rows to produce later rows.

How do I stop a SQLite recursive CTE?

Make the recursive SELECT eventually return no rows, and use a LIMIT or depth bound when you know a safe maximum.