SQLite / Intermediate: query structure and transactional design / Lesson 7

Subqueries, CTEs, and views

Express existence tests and staged transformations while distinguishing statement-scoped query names, persistent views, and actual stored intermediate data.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Choose EXISTS for presence and absence.
Name query stages with CTEs.
Explain view behavior and materialization limits.

Use nesting to expose a logical stage

A subquery can supply a scalar, a row set, or an existence condition. State its expected cardinality and correlation to the outer row. A customer-specific lookup that accidentally omits customer_id equality asks about the entire orders table instead. Read a correlated predicate aloud as “an order for this customer” rather than merely “an order exists.”

Scalar subqueries in SQLite have specific behavior when several rows are available, so do not use an unconstrained lookup as an arbitrary value selector. Enforce uniqueness or define an explicit ordering and limit for the actual question. Nesting improves correctness when it makes grain and scope visible; deeply nested syntax without named responsibilities can instead conceal a mistaken relationship.

Avoid multiplying parents for a presence question

EXISTS asks whether the subquery finds any row; its selected constant is not the business result. Customer1 in the first script has two orders but appears once in the outer result. NOT EXISTS returns customer2, who has none. This states presence or absence directly without relying on DISTINCT after a multiplying join.

NOT IN can behave unexpectedly when its comparison set includes NULL, because unknown comparisons can prevent an expected absence match. Choose an explicitly correlated absence rule and test missing keys. EXISTS is not automatically a universal performance improvement: indexes, data, and the optimizer still matter. Its main benefit here is matching the semantic question clearly.

sql Presence and absence at customer grain

CREATE TABLE customer(id INTEGER PRIMARY KEY);
CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer_id INTEGER);
INSERT INTO customer VALUES(1),(2);
INSERT INTO purchase VALUES(10,1),(11,1);
SELECT c.id FROM customer AS c WHERE EXISTS
 (SELECT 1 FROM purchase AS p WHERE p.customer_id=c.id) ORDER BY c.id;
SELECT c.id FROM customer AS c WHERE NOT EXISTS
 (SELECT 1 FROM purchase AS p WHERE p.customer_id=c.id) ORDER BY c.id;

Expected output

id
1

id
2

Two child matches still give one existence result for customer1; the complementary query returns customer2.

Separate a named expression from persisted rows

An ordinary CTE names a query expression for one statement, while CREATE VIEW stores a reusable query definition in the schema. Neither name alone means that rows have been permanently materialized. SQLite may inline, co-routine, or materialize work according to its rules and plan. A temporary table is another design with explicit stored intermediate rows and its own writes and lifecycle.

The second script defines a view of open totals, then a CTE filtering it. South totals40 and North30. The final ORDER BY controls presentation. SQLite views are read-only unless suitable INSTEAD OF triggers provide modification behavior; do not import automatic updatable-view assumptions from another database. A view also is not a server-side privilege boundary in an embedded file-access model.

sql A view definition and a statement-scoped stage

CREATE TABLE purchase(region TEXT,status TEXT,amount INTEGER);
INSERT INTO purchase VALUES('North','open',10),('North','open',20),
 ('North','closed',100),('South','open',40);
CREATE VIEW open_total AS SELECT region,SUM(amount) AS total
 FROM purchase WHERE status='open' GROUP BY region;
WITH selected AS (SELECT region,total FROM open_total WHERE total>=30)
SELECT region,total FROM selected ORDER BY total DESC,region;

Expected output

region|total
South|40
North|30

The closed100 is excluded by the view. The CTE retains both qualifying regional totals and the outer query orders them.

Bound recursive work and identify cycles

A recursive CTE combines an anchor with a recursive term, often using UNION ALL, to traverse a hierarchy or generate a bounded sequence. State a termination condition and consider cyclic or malformed data. A hierarchy that should be a tree can contain a cycle if its schema and application do not protect that invariant. Repeated paths can also multiply output.

A small sequence from1 through3 can anchor1 and continue with value+1 while value<3. An unbounded recurrence is not a safe demonstration. Put explicit depth or membership protection into the intended traversal, and explain whether duplicates are meaningful. Resource limits are another defense, not a substitute for a termination argument that matches the data model.

Review clarity and repeated work separately

A readable CTE is not proof that an expensive intermediate runs only once. Inspect EXPLAIN QUERY PLAN and representative measurements when reuse matters. SQLite offers materialization hints in supported releases, but their documented meanings and optimization tradeoffs must be understood rather than copied as universal speed flags. Do not force a strategy before confirming result correctness.

Test missing parents, multiple children, empty input, and conflicting scalar matches. Keep view dependencies visible during schema migration, because renaming or rebuilding objects can affect consumers. A useful staged query has a stated grain after each step, an explicit final ordering, and a reason for storing a definition or intermediate result instead of merely hiding complexity behind another name.

Common errors and fixes

A CTE name is missing in the next statement.

Why it happens: Its scope ended with the statement that defined it.
How to fix it: Define another CTE or use a view/temporary table for the required lifetime.
Prevent it next time: Document query-name scope.

An existence query selects every customer.

Why it happens: The subquery omitted its correlation predicate.
How to fix it: Add the relationship to the outer customer.
Prevent it next time: Test one parent with matches and one without.

A hierarchy traversal does not finish.

Why it happens: The recurrence lacks a valid bound or revisits a cycle.
How to fix it: Use a terminating traversal with depth/path protection appropriate to the model.
Prevent it next time: Test cycles and repeated paths in isolation.

Check your understanding

1. Why can EXISTS avoid repeated customer rows when a join would produce several?

Show worked solution

It tests whether any correlated child exists rather than emitting every matching pair. Two orders still produce one true existence decision for the outer customer. Keep the customer correlation explicit; otherwise the query tests whether any order exists anywhere.

2. Choose between a CTE, view, and temporary table for a costly result reused by several statements.