Learn

Recursive CTEs let you walk hierarchical or graph data. Common pattern: tree traversal, ancestor walks, generating sequences.

WITH RECURSIVE numbers(n) AS (
    SELECT 1                       -- base case
    UNION ALL
    SELECT n + 1 FROM numbers
    WHERE n < 10                    -- termination
)
SELECT n FROM numbers;

Generates 1 through 10. The recursive CTE has two parts:

1. Anchor — initial rows (SELECT 1)
2. Recursive — joins to itself (SELECT n + 1 FROM numbers WHERE n < 10)

The DB iteratively appends recursive rows until the recursive part returns no new rows.

Tree traversal — find descendants:

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    name TEXT
);
INSERT INTO categories VALUES
    (1, NULL, 'electronics'),
    (2, 1, 'computers'),
    (3, 1, 'phones'),
    (4, 2, 'laptops'),
    (5, 2, 'desktops'),
    (6, 4, 'gaming-laptops');

WITH RECURSIVE descendants(id, name, depth) AS (
    -- Anchor: top-level
    SELECT id, name, 0 FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- Recursive: children of any descendant
    SELECT c.id, c.name, d.depth + 1
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT printf('%s%s', substr('                  ', 1, depth*2), name) FROM descendants;

Output (with indentation):

electronics
  computers
    laptops
      gaming-laptops
    desktops
  phones

Path tracking:

WITH RECURSIVE paths(id, path) AS (
    SELECT id, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, p.path || '/' || c.name
    FROM categories c JOIN paths p ON c.parent_id = p.id
)
SELECT path FROM paths;

Generating series — useful when SQLite lacks a built-in (older versions):

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

Generates every day in January 2026.

Caveats:

• Termination is YOUR responsibility — infinite loops are real
• Performance can be poor for deep recursion (no tail-call optimization)
• Most engines cap depth at 1000 or so — set with PRAGMA recursive_triggers etc.