SQLite § loops

CTEs and recursion

A common table expression (CTE) is a named subquery declared at the start of a SELECT, INSERT, UPDATE, or DELETE statement and referenced by name within it. CTEs serve two purposes: they decompose a complex query into named, composable parts, replacing nested subqueries with a sequence of named steps; and they are the only mechanism in SQL for recursion, the iterative computation that arises in graph traversal, hierarchy reporting, and generated sequences. SQLite has supported CTEs since version 3.8.3 (2014) and recursive CTEs since the same release.

This page covers the syntax of WITH clauses, the recursive form (anchor and recursive members joined by UNION ALL), the canonical use cases (graph traversal, generate-series, transitive closure), the MATERIALIZED and NOT MATERIALIZED hints (3.35+), and the recursion-depth limit and how to control it. Recursive CTEs are SQL’s loop construct; the page is filed under loops in the schema for that reason.

Non-recursive CTEs

A WITH clause introduces one or more named subqueries:

WITH active_customer AS (
    SELECT id, name, email FROM customer WHERE deleted_at IS NULL
)
SELECT name FROM active_customer ORDER BY name;

The CTE active_customer is defined once and referenced in the outer query. The form is semantically equivalent to a subquery in FROM, but the named decomposition is more readable, particularly when the query has several independent intermediate steps.

Several CTEs may be declared in one WITH clause, separated by commas. Later CTEs can reference earlier ones:

WITH
    active_customer AS (
        SELECT id, name FROM customer WHERE deleted_at IS NULL
    ),
    recent_order AS (
        SELECT customer_id, COUNT(*) AS n
        FROM bouquet_order
        WHERE placed_at >= date('now', '-90 days')
        GROUP BY customer_id
    )
SELECT a.name, COALESCE(r.n, 0) AS recent_orders
FROM active_customer AS a
LEFT JOIN recent_order AS r ON r.customer_id = a.id
ORDER BY recent_orders DESC;

Each CTE is a named building block. The query reads top-to-bottom as a small program, with each step defined and named before it is used.

CTEs are scoped to the statement in which they are declared. A WITH clause in a SELECT does not persist after the query; the next statement sees no active_customer table.

CTE in INSERT, UPDATE, DELETE

A CTE may precede any data-modification statement:

WITH expired AS (
    SELECT id FROM session WHERE last_seen < date('now', '-30 days')
)
DELETE FROM session WHERE id IN (SELECT id FROM expired);

The form makes the what is to be modified and the modification visually distinct, which is particularly helpful when the query that selects the rows is non-trivial.

Recursive CTEs

A recursive CTE is a CTE that references itself. The shape is fixed: a UNION ALL between an anchor member (a non-recursive query that produces the initial rows) and a recursive member (a query that produces additional rows by referencing the CTE’s name).

WITH RECURSIVE counter(n) AS (
    SELECT 1                        -- anchor: starting value
    UNION ALL
    SELECT n + 1 FROM counter       -- recursive: adds one
    WHERE n < 10                    -- termination predicate
)
SELECT n FROM counter;
-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

The keyword RECURSIVE marks the CTE as recursive; SQLite accepts WITH alone for recursive CTEs as well, but the explicit RECURSIVE is the SQL-standard form and is recommended.

The semantics: SQLite evaluates the anchor first to produce an initial set of rows, then repeatedly evaluates the recursive member, each iteration consuming the most recently added rows and producing new ones. The iteration terminates when the recursive member returns no new rows. The WHERE n < 10 predicate in the example provides the termination — without it, the recursion never stops and the engine eventually hits the recursion-depth limit.

A common-table column list (the (n) after counter) names the columns of the CTE; the names are used both within the CTE (the anchor and recursive members project columns positionally to those names) and in the outer query.

Generated sequences

The simplest recursive CTE generates a sequence of integers, useful for date series, calendar tables, and other denormalised constructions:

WITH RECURSIVE days(d) AS (
    SELECT date('2026-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days
    WHERE d < date('2026-12-31')
)
SELECT d FROM days;
-- 2026-01-01, 2026-01-02, …, 2026-12-31

The sequence is generated entirely within the query; no auxiliary table is required. The pattern recurs in every report query that needs a dense calendar (one row per day, even days with no activity).

Tree and graph traversal

The canonical use of recursion is traversing a tree or graph. The pattern: the anchor selects the root (or starting node), the recursive member finds children of nodes already visited.

CREATE TABLE category (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    parent_id INTEGER REFERENCES category(id)
);

INSERT INTO category VALUES
    (1, 'Plants', NULL),
    (2, 'Bouquets', 1),
    (3, 'Roses', 2),
    (4, 'White roses', 3),
    (5, 'Lilies', 2);

-- All descendants of 'Bouquets' (id 2):
WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM category WHERE id = 2

    UNION ALL

    SELECT c.id, c.name, c.parent_id, d.depth + 1
    FROM category AS c
    JOIN descendants AS d ON c.parent_id = d.id
)
SELECT id, name, depth FROM descendants ORDER BY depth, name;
-- 2|Bouquets|0
-- 3|Roses|1
-- 5|Lilies|1
-- 4|White roses|2

The depth column accumulates the recursion depth, useful for indented display. The ORDER BY in the outer query sorts the result; the recursive computation itself is not ordered.

The reverse direction — finding all ancestors of a node — is symmetric:

WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id FROM category WHERE id = 4

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM category AS c
    JOIN ancestors AS a ON c.id = a.parent_id
)
SELECT id, name FROM ancestors;
-- 4|White roses
-- 3|Roses
-- 2|Bouquets
-- 1|Plants

Transitive closure

A more involved example: the transitive closure of an edge table — every pair of nodes that are connected by some path.

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

WITH RECURSIVE reachable(a, b) AS (
    SELECT a, b FROM edge

    UNION

    SELECT r.a, e.b
    FROM reachable AS r
    JOIN edge AS e ON e.a = r.b
)
SELECT * FROM reachable ORDER BY a, b;
-- 1|2, 1|3, 1|4, 2|3, 2|4, 3|4, 5|6

Note UNION (not UNION ALL): in graph traversals where the same row may be reached by several paths, UNION deduplicates and prevents the recursion from running forever in cyclic graphs. The cost is that each iteration must check the accumulated result for membership; for cyclic graphs, the deduplication is essential.

For graphs with cycles, an explicit visited-set is the conventional alternative to UNION; the recursion terminates when no new node is added.

MATERIALIZED hints

Version 3.35 introduced two hints that influence whether a CTE is materialised (computed once, stored in a temporary table, and reused) or inlined (re-evaluated at each reference):

WITH expensive AS MATERIALIZED (
    SELECT customer_id, COUNT(*) AS n FROM bouquet_order GROUP BY customer_id
)
SELECT … FROM expensive WHERE n > 10
UNION
SELECT … FROM expensive WHERE n < 3;

WITH cheap AS NOT MATERIALIZED (
    SELECT id, name FROM customer WHERE deleted_at IS NULL
)
SELECT * FROM cheap ORDER BY name;

The default is opportunistic: the planner inlines if the CTE is referenced once and materialises if referenced more than once. The hints override the default. MATERIALIZED is the right choice when the CTE is expensive and would otherwise be evaluated several times; NOT MATERIALIZED is the right choice when inlining permits the optimiser to push predicates down into the CTE.

Recursion limits

The PRAGMA limit command exposes a per-connection limit on recursion depth (default 1000). A query that exceeds the limit fails:

PRAGMA limit;            -- show all limits
PRAGMA limit(SQLITE_LIMIT_TRIGGER_DEPTH, 10000);

The limit exists to prevent runaway recursion from consuming unbounded memory; lifting it for a query that is known to terminate (a known-depth tree, a known-length sequence) is an acceptable use.

The SQLITE_LIMIT_VDBE_OP limit is a related safety: the maximum number of virtual-machine operations a query may execute. A query that produces an unbounded result hits this limit before it exhausts memory. The two limits together provide a safety net against accidental infinite recursion.

CTEs versus subqueries

The choice between a CTE and a subquery is principally about readability. The two forms are typically equivalent in performance — the planner inlines a CTE when it can — but a CTE makes intent visible at the level of the statement.

A subquery is more concise for simple cases:

SELECT name FROM customer
WHERE id IN (SELECT customer_id FROM bouquet_order WHERE amount > 100);

A CTE is preferable when the same intermediate result is used several times, when the intermediate result has a meaningful name, or when the query is complex enough that named decomposition aids reading:

WITH big_order AS (
    SELECT customer_id FROM bouquet_order WHERE amount > 100
)
SELECT name FROM customer WHERE id IN (SELECT customer_id FROM big_order);

The recommendation in production code is to use CTEs liberally for non-trivial queries and to reserve subqueries for short, single-purpose lookups.

Recursive CTE as a loop

A working programmer arriving from an imperative language can usefully think of a recursive CTE as SQL’s loop construct. The shape:

Imperative	Recursive CTE
Initial state	Anchor member
Loop body	Recursive member
Termination test	`WHERE` in the recursive member
Accumulator	Columns of the CTE
Result	Outer `SELECT FROM cte`

The mapping is exact for sequence generation and tree traversal; for more complex iterations (mutating state, branching) the imperative form is awkward in SQL and the work is better done in the application layer. The conventional rule is: if the iteration is naturally expressed as for each row that satisfies a predicate, derive new rows from it, the recursive CTE is the right form.