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:
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
Run it against a small orders table:
You can name the output columns in the header instead of aliasing each expression:
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:
SQLite is more relaxed about order than PostgreSQL. A CTE can reference a sibling defined after it, so this runs and returns 1:
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:
What SQLite does not allow is a data-modifying statement inside the CTE. The PostgreSQL pattern of DELETE ... RETURNING in a CTE fails:
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:
Add MATERIALIZED and SQLite scans the table into a temporary result first, then filters that:
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 showedMATERIALIZEin our tests withNOT MATERIALIZEDset. - 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.
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:
Reverse the join to walk up the tree instead. This finds Sara's reporting line:
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:
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:
The most useful version generates dates, so days with no data still appear in a report:
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:
A LIMIT on the outer query works too, because SQLite produces CTE rows on demand. This returns 1, 2, 3 and finishes:
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:
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:
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
| CTE | Subquery | View | |
|---|---|---|---|
| Lifetime | One statement | One statement | Until dropped |
| Named | Yes | No | Yes |
| Reusable within the query | Yes | No | Yes |
| Recursive | Yes | No | Can 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
| Task | Syntax |
|---|---|
| Basic CTE | WITH c AS (SELECT ...) SELECT * FROM c |
| Name the columns | WITH c(a, b) AS (SELECT ...) |
| Several CTEs | WITH a AS (...), b AS (...) SELECT ... |
| CTE before a write | WITH 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 (...) |
| Recursive | WITH RECURSIVE c AS (initial UNION ALL recursive) |
| Depth-first order | ORDER BY depth DESC inside the recursive select |
| Cap recursion | LIMIT n inside the recursive select |
| Stop exact-repeat cycles | UNION 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.