WITH CTEs and Recursive Queries

Learning outcomes

A common table expression (CTE) gives a query step a name. Ordinary CTEs improve structure; recursive CTEs let one query repeatedly feed prior results into another step. SQLite also exposes materialization hints, but they are planner guidance—not a reason to micromanage every query.

Ordinary CTEs name intermediate results, not stored tables

A WITH clause defines one or more named result sets visible to the statement that follows. They are conceptually similar to temporary views scoped to one statement, though the planner may flatten, inline, or materialize them as it chooses.

WITH raw(device_code,status,severity) AS (  VALUES ('PUMP-007',' open ',5),         ('PUMP-007','closed',2),         ('FAN-014','OPEN',4)), normalized AS (  SELECT device_code,         upper(trim(status)) AS status_norm,         severity  FROM raw), high_open AS (  SELECT * FROM normalized  WHERE status_norm='OPEN' AND severity >= 4)SELECT device_code, count(*) AS high_open_notesFROM high_openGROUP BY device_codeORDER BY device_code;

The value of the CTE is readability: each stage states one idea. It is not persisted in sqlite_schema and disappears when the statement finishes.

Recursive CTE anatomy

A recursive CTE has an anchor member that seeds the result and a recursive member that references the CTE itself to produce the next rows. A termination predicate—or a finite graph with duplicate prevention—must eventually stop generating new rows.

WITH RECURSIVE seq(n) AS (  VALUES(1)                 -- anchor  UNION ALL  SELECT n+1                -- recursive member  FROM seq  WHERE n < 5               -- termination)SELECT n FROM seq;

Expected output is 1, 2, 3, 4, 5. If you remove WHERE n < 5, the query no longer expresses a finite job. Treat termination as part of correctness, not as an optional performance tweak.

Generate a date sequence without a calendar table

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

Sequence CTEs are useful for short reporting gaps and reproducible labs. For very large reusable calendars, a real calendar table can be clearer, indexable, and easier to enrich with business-day attributes.

Hierarchy lab: adjacency list to depth and path

Use a small FieldNotes location hierarchy. Each row optionally points to its parent. The anchor starts at root sites; the recursive member joins children to the previous level.

DROP TABLE IF EXISTS site_tree;CREATE TABLE site_tree(  site_id INTEGER PRIMARY KEY,  parent_site_id INTEGER REFERENCES site_tree(site_id),  name TEXT NOT NULL UNIQUE);INSERT INTO site_tree(site_id,parent_site_id,name) VALUES(1,NULL,'Plant'),(2,1,'North Hall'),(3,1,'South Hall'),(4,2,'Pump Bay'),(5,2,'Sensor Rack'),(6,4,'Pump P-007');WITH RECURSIVE tree(site_id,name,depth,path) AS (  SELECT site_id,name,0,printf('/%s',name)  FROM site_tree  WHERE parent_site_id IS NULL  UNION ALL  SELECT c.site_id,c.name,p.depth+1,p.path || '/' || c.name  FROM site_tree AS c  JOIN tree AS p ON c.parent_site_id=p.site_id)SELECT site_id,name,depth,pathFROM treeORDER BY path;

Pump P-007 appears at depth 3 with path /Plant/North Hall/Pump Bay/Pump P-007. Carrying depth/path in the recursive state makes the traversal explainable and debuggable.

UNION ALL versus UNION

UNION ALL preserves every generated row and avoids duplicate-elimination work. UNION removes duplicates before rows re-enter the recursive queue, which can be useful for graph traversal—but only if “duplicate” is defined by the columns in the recursive result.

If a graph can contain cycles, encode a visited-node rule or use a data model that prevents cycles where that is a domain invariant. Do not assume swapping UNION ALL for UNION universally solves recursion safety.

Cycle guard: path membership as a teaching technique

For a small integer-key graph, a delimited visited path can prevent revisiting a node. This is pedagogical and useful for modest traversals; specialized graph workloads may need a different data structure.

WITH RECURSIVE walk(site_id,name,depth,visited) AS (  SELECT site_id,name,0,printf(',%d,',site_id)  FROM site_tree WHERE site_id=1  UNION ALL  SELECT c.site_id,c.name,w.depth+1,         w.visited || printf('%d,',c.site_id)  FROM site_tree AS c  JOIN walk AS w ON c.parent_site_id=w.site_id  WHERE instr(w.visited, printf(',%d,',c.site_id))=0    AND w.depth < 20)SELECT site_id,name,depth FROM walk ORDER BY depth,site_id;

The depth limit is a second safety boundary. It should reflect the domain's plausible hierarchy depth; do not silently truncate legitimate data in production.

MATERIALIZED and NOT MATERIALIZED are hints, not promises

SQLite supports PostgreSQL-inspired AS MATERIALIZED and AS NOT MATERIALIZED from 3.35.0. They are non-binding planner hints. MATERIALIZED acts as an optimization fence by evaluating into an ephemeral table; NOT MATERIALIZED means “treat like an ordinary view/subquery,” but it still does not prohibit materialization.

WITH recent AS MATERIALIZED (  SELECT * FROM site_tree WHERE site_id >= 3)SELECT count(*) FROM recent;WITH recent AS NOT MATERIALIZED (  SELECT * FROM site_tree WHERE site_id >= 3)SELECT count(*) FROM recent;

The recommended default is to omit both hints and let SQLite choose. Reach for them only when measurement and plan inspection justify an optimization fence or inlining preference. Chapter 10 will connect this to EXPLAIN QUERY PLAN.

Recursive hierarchy verification

WITH RECURSIVE tree(site_id,name,depth,path) AS (  SELECT site_id,name,0,name FROM site_tree WHERE parent_site_id IS NULL  UNION ALL  SELECT c.site_id,c.name,t.depth+1,t.path || ' > ' || c.name  FROM site_tree c JOIN tree t ON c.parent_site_id=t.site_id)SELECT count(*) AS reached_rows,       max(depth) AS max_depthFROM tree;-- expected: reached_rows=6, max_depth=3

A recursive query is not correct merely because it returns plausible paths. Verify expected node count, root count, maximum reasonable depth, and—where relevant—whether cycles or disconnected nodes exist.

Summary and bridge

CTEs let you separate a query into named reasoning steps; recursion turns those steps into controlled iteration over sequences and hierarchies. Keep termination and cycle behavior explicit, and leave planner hints alone until measurement gives you a reason. Lesson 5 now adds window functions, which perform analytics across related rows without collapsing the detail rows.

Authoritative references

• SQLite — The WITH Clause.
• SQLite — Recursive Query Examples.
• SQLite — Materialization Hints.