SQLite Common Table Expressions: WITH Clause Explained

A CTE Is a Named Subquery

A Common Table Expression (CTE) is a subquery you've pulled out and given a name. Instead of nesting a SELECT inside another SELECT, you define it at the top with WITH, name it, and then use that name in the main query like it were a table.

The shape is always the same:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Read it top to bottom: first build a named result called customer_totals, then query that result. The CTE behaves like a temporary view that exists only for the duration of this one statement.

The Same Query Without a CTE

Here's the same logic written as a subquery, so you can see what the CTE is replacing:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Same answer. But notice the reading order: your eye has to dive into the parentheses, figure out what's being computed, then come back out. The CTE version reads in the order the work happens — define the intermediate result, then use it. On a small query this is a wash. On a query with three or four steps, it's the difference between code you can skim and code you have to decode.

Multiple CTEs in One Query

You can chain several CTEs together, separated by commas. Each one can reference the ones defined before it, so you build a pipeline of named steps:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

One WITH, then comma-separated CTE definitions. The second CTE (big_spenders) reads from the first (customer_totals) just like reading from a table. The main SELECT follows the last CTE definition.

A common slip: writing WITH again in front of the second CTE. Don't — it's a syntax error. One WITH covers all of them.

Referencing a CTE More Than Once

This is where CTEs really pull ahead of subqueries. If you need the same intermediate result in two places, a CTE lets you compute it once and reference it twice:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

The CTE is referenced twice: once to compute the average, once as the main source. Without the CTE, you'd duplicate the GROUP BY query, and any change to it would have to be made in two places.

CTEs With INSERT, UPDATE, and DELETE

CTEs aren't only for SELECT. You can put a WITH clause in front of INSERT, UPDATE, or DELETE to use a named subquery in a write:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

The CTE describes which rows to flag. The INSERT ... SELECT uses it as its source. Same trick works with DELETE FROM ... WHERE id IN (SELECT id FROM cte) for staged deletions where the targeting logic is intricate.

When To Reach For a CTE

A few rules of thumb:

• The query has more than one logical step. Aggregating, then filtering on the aggregate, then joining the result — that's a pipeline, and a CTE per step makes it readable.
• You'd otherwise repeat the same subquery. Define it once, reference it twice.
• The subquery deserves a name. If you'd put a comment above the subquery to explain what it represents, the CTE name is that comment, and it's enforced by the syntax.
• You're about to write a recursive query. That's only possible with WITH RECURSIVE — covered in the next page.

When not to bother:

• A single simple subquery used in one place. WHERE id IN (SELECT id FROM ...) is fine as is.
• Performance-critical queries where you've already verified that inlining the logic helps. SQLite usually treats a CTE as a query optimization fence less aggressively than some other databases, but for hot paths it's worth checking with EXPLAIN QUERY PLAN.

A Worked Example

Putting it together — a small report that finds each customer's largest order and how it compares to their average:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Two CTEs, each doing one thing. The main SELECT formats the result. You can read the query top to bottom and understand each step on its own — which is the whole point of CTEs.

Next: Recursive CTEs

Everything so far has been a regular CTE — a named subquery, evaluated once. SQLite also supports WITH RECURSIVE, where a CTE references itself to walk hierarchies, generate sequences, or traverse graphs. That's the next page.