Limited Time Offer: 40% off

SQLite CTE: WITH and recursive queries

A SQLite CTE names a subquery with WITH. Add RECURSIVE and the same syntax walks trees, generates series, and stops cleanly with LIMIT.

Quick answer

A SQLite CTE (common table expression) gives a subquery a name with WITH, then lets the main query read from it like a table:

SQL
WITH big_orders AS (
  SELECT customer_id, amount FROM orders WHERE amount >= 100
)
SELECT c.name, count(*) AS big_orders, sum(b.amount) AS total
FROM big_orders b
JOIN customers c ON c.id = b.customer_id
GROUP BY c.name
ORDER BY total DESC;

The CTE exists only for that one statement. SQLite has supported WITH since 3.8.3, and the MATERIALIZED hints since 3.35.0. Every example on this page was run on SQLite 3.43.2, and the interactive editors run SQLite 3.45 in your browser.

Basic CTE syntax

SQL
WITH name AS (
  select-statement
)
SELECT ... FROM name;

Run it against a small orders table:

⚡
Loading SQL environment...
name|big_orders|total
Globex|3|1360.0
Acme Ltd|2|330.0

You can name the output columns in the header instead of aliasing each expression:

SQL
WITH totals(country, revenue) AS (
  SELECT c.country, sum(o.amount)
  FROM orders o JOIN customers c ON c.id = o.customer_id
  GROUP BY c.country
)
SELECT * FROM totals ORDER BY revenue DESC;
country|revenue
US|1395.0
UK|410.5
DE|99.99

Multiple CTEs

Write WITH once and separate each CTE with a comma. A later CTE can read from an earlier one, which lets you build a query as a pipeline of named steps.

This finds the top customer by revenue in each month:

⚡
Loading SQL environment...
month|name|total
2026-10|Globex|450.0
2026-11|Globex|910.0

SQLite is more relaxed about order than PostgreSQL. A CTE can reference a sibling defined after it, so this runs and returns 1:

SQL
WITH a AS (SELECT * FROM b), b AS (SELECT 1 AS x)
SELECT * FROM a;

Top-to-bottom order is still easier to read, so write them that way anyway.

CTEs with INSERT, UPDATE, and DELETE

A WITH clause can sit in front of INSERT, UPDATE, or DELETE as well as SELECT:

SQL
CREATE TABLE vip (customer_id INTEGER PRIMARY KEY);

WITH spend AS (
  SELECT customer_id, sum(amount) AS total FROM orders GROUP BY customer_id
)
INSERT INTO vip SELECT customer_id FROM spend WHERE total > 400;

What SQLite does not allow is a data-modifying statement inside the CTE. The PostgreSQL pattern of DELETE ... RETURNING in a CTE fails:

SQL
WITH gone AS (DELETE FROM orders WHERE id = 1 RETURNING *)
SELECT * FROM gone;
Parse error near line 1: near "DELETE": syntax error

Run the DELETE ... RETURNING on its own statement instead. The other restriction worth knowing: the SQLite docs state that a WITH clause cannot be used inside CREATE TRIGGER.

MATERIALIZED and NOT MATERIALIZED

By default SQLite decides for itself whether to inline a CTE into the outer query or compute it once into a temporary table. Since 3.35.0 you can nudge that choice with the PostgreSQL-style hints AS MATERIALIZED and AS NOT MATERIALIZED.

Inlining usually wins because filters from the outer query reach the underlying table. With no hint, the WHERE id = 3 uses the primary key:

SQL
EXPLAIN QUERY PLAN
WITH c AS (SELECT * FROM orders)
SELECT * FROM c WHERE id = 3;
QUERY PLAN
`--SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)

Add MATERIALIZED and SQLite scans the table into a temporary result first, then filters that:

SQL
EXPLAIN QUERY PLAN
WITH c AS MATERIALIZED (SELECT * FROM orders)
SELECT * FROM c WHERE id = 3;
QUERY PLAN
|--MATERIALIZE c
|  `--SCAN orders
`--SCAN c

Use MATERIALIZED when the CTE is expensive, is referenced several times, and returns a small result. Check the plan before and after, because it can make simple lookups slower, as above.

Two details that differ from PostgreSQL:

  • Both are hints. The SQLite docs say that even with NOT MATERIALIZED, the planner "is still free to implement the subquery using materialization if it feels that is the best solution." A CTE that aggregates, for example, still showed MATERIALIZE in our tests with NOT MATERIALIZED set.
  • Being referenced twice does not force materialization. A plain CTE used twice in a self join was inlined in both references on 3.43.2.

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL (or UNION): a starting query, and a recursive query that reads from the CTE itself.

SQL
WITH RECURSIVE name(columns) AS (
  initial-select          -- runs once
  UNION ALL
  recursive-select        -- runs once per row in the queue
)
SELECT ... FROM name;

SQLite puts the rows from the initial select in a queue. It takes one row off the queue, runs the recursive select against that row, and adds anything it returns to the queue. It stops when the queue is empty.

The RECURSIVE keyword is optional in SQLite (a self-referencing CTE works without it), but include it so readers and other databases know what is going on.

Walk a hierarchy

The classic use is a table where each row points at its parent. Here, every employee has a manager_id:

⚡
Loading SQL environment...
depth|name|title
0|Maya|CEO
1|Priya|CFO
1|Tom|CTO
2|Ana|Accountant
2|Leo|Engineering Manager
3|Ken|Engineer
3|Sara|Engineer

Reverse the join to walk up the tree instead. This finds Sara's reporting line:

SQL
WITH RECURSIVE chain(id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employees WHERE name = 'Sara'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN chain ON e.id = chain.manager_id
)
SELECT group_concat(name, ' > ') AS reports_to FROM chain;
reports_to
Sara > Leo > Tom > Maya

Depth-first order with ORDER BY

SQLite lets you put an ORDER BY on the recursive select, which is unusual. It controls which row comes off the queue next. Ordering by depth descending turns the default breadth-first walk into a depth-first one, which is what an indented tree needs:

⚡
Loading SQL environment...
org_chart
Maya
  Priya
    Ana
  Tom
    Leo
      Ken
      Sara

ORDER BY 3 DESC, 2 means "deepest first, then by name". Without an ORDER BY, the SQLite docs describe the extraction order as undefined, so do not depend on it.

Generate a series

generate_series() comes from SQLite's series extension. The sqlite3 shell includes it, but sql.js and the node:sqlite module in Node.js 22 both return no such table: generate_series. A recursive CTE works everywhere:

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

The most useful version generates dates, so days with no data still appear in a report:

⚡
Loading SQL environment...
day|orders|revenue
2026-11-01|1|300.0
2026-11-02|0|0
2026-11-03|0|0
2026-11-04|1|35.0
2026-11-05|0|0
2026-11-06|0|0
2026-11-07|0|0

Stop recursion with LIMIT

A LIMIT on the recursive select caps the number of rows the CTE will ever produce. Once it is reached, recursion stops, so you can drop the WHERE guard:

⚡
Loading SQL environment...
fibonacci
0, 1, 1, 2, 3, 5, 8, 13, 21, 34

A LIMIT on the outer query works too, because SQLite produces CTE rows on demand. This returns 1, 2, 3 and finishes:

SQL
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n)
SELECT x FROM n LIMIT 3;

Relying on the outer LIMIT is fragile. Add an ORDER BY or an aggregate to that outer query and SQLite needs every row first, so it never stops. Put the limit inside the CTE, or keep a WHERE guard.

Handle cycles

If the data contains a loop (A links to B, B links to A), UNION ALL recurses forever. SQLite has no CYCLE clause like PostgreSQL 14, so you handle it yourself.

UNION discards rows already produced, which stops a loop when the rows repeat exactly:

SQL
CREATE TABLE links (src TEXT, dst TEXT);
INSERT INTO links VALUES ('home','pricing'),('pricing','signup'),('signup','home'),('pricing','docs');

WITH RECURSIVE reach(page) AS (
  SELECT 'home'
  UNION
  SELECT l.dst FROM links l JOIN reach r ON l.src = r.page
)
SELECT page FROM reach;
page
home
pricing
docs
signup

When each row carries extra columns such as a path or a depth, rows never repeat exactly and UNION will not help. Track the path in a string and refuse to revisit a node:

SQL
WITH RECURSIVE walk(page, path) AS (
  SELECT 'home', '/home/'
  UNION ALL
  SELECT l.dst, w.path || l.dst || '/'
  FROM links l
  JOIN walk w ON l.src = w.page
  WHERE instr(w.path, '/' || l.dst || '/') = 0
)
SELECT path FROM walk;
path
/home/
/home/pricing/
/home/pricing/docs/
/home/pricing/signup/

Common errors

These messages come from SQLite 3.43.2. The sqlite3 shell adds its own prefix, such as Error: in prepare,.

circular reference: a. A non-recursive CTE reads from itself, or the recursive part of a recursive CTE comes before the initial select. Put the initial select first, then UNION ALL, then the part that references the CTE.

recursive aggregate queries not supported. The recursive select uses an aggregate such as max() or count(). SQLite does not allow aggregates or window functions there. Aggregate in the outer query instead.

multiple references to recursive table: n. The recursive select names the CTE twice, for example in a self join. Each recursive select can reference the CTE once in its FROM clause.

multiple recursive references: n. The CTE is referenced from a subquery inside the recursive select. Move that logic into a join or into the outer query.

table n has 1 values for 2 columns. The column list in WITH n(x, y) does not match the number of columns the select returns.

no such table: t. A CTE only exists for its own statement. A second statement cannot read it. Repeat the WITH clause or create a view.

The query never finishes. A recursive CTE with no WHERE guard and no LIMIT, or a cycle in the data with UNION ALL. Add a limit inside the CTE.

CTE vs subquery vs view

CTESubqueryView
LifetimeOne statementOne statementUntil dropped
NamedYesNoYes
Reusable within the queryYesNoYes
RecursiveYesNoCan contain a recursive CTE

A single-use CTE and the equivalent subquery usually get the same plan, so choose the one that reads better. If several queries need the same step, create a view. For how PostgreSQL treats the same syntax, including data-modifying CTEs, see the PostgreSQL CTE guide.

Quick reference

TaskSyntax
Basic CTEWITH c AS (SELECT ...) SELECT * FROM c
Name the columnsWITH c(a, b) AS (SELECT ...)
Several CTEsWITH a AS (...), b AS (...) SELECT ...
CTE before a writeWITH c AS (...) INSERT INTO t SELECT ... FROM c
Hint materialization (3.35.0+)WITH c AS MATERIALIZED (...)
Hint inlining (3.35.0+)WITH c AS NOT MATERIALIZED (...)
RecursiveWITH RECURSIVE c AS (initial UNION ALL recursive)
Depth-first orderORDER BY depth DESC inside the recursive select
Cap recursionLIMIT n inside the recursive select
Stop exact-repeat cyclesUNION instead of UNION ALL

To run and inspect queries like these against your own database files, DB Pro opens SQLite databases on macOS, Windows, and Linux.