Common Table Expressions

The previous lesson’s join-based phrasing built its intermediate result as a subquery embedded inside FROM. This reads fine at one layer; it stops reading at two. The intermediate result’s definition is buried in the middle of the query, its name is only visible after the closing parenthesis, and if the same intermediate result is needed in two places, its definition gets written twice.

A common table expression solves all three problems at once: it gives the intermediate result a name at the start of the query, reduces the body to a plain query that uses that name, and allows the same name to be used more than once inside the body.

Naming

A common table expression begins with the WITH keyword and contains a name, an optional column list, and a query in parentheses. After this definition, the name is used like a table in the body of the statement that follows.

The question “average loans per member, per branch” is two layers: first a per-member count, then an average within the branch. The block below asks the same question two ways — once with an embedded subquery, once with a named expression:

sqlite3 -box -header <<'SQL'
CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT, parent_id INT);
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch_id INT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO branch VALUES (4,'Kadikoy',2),(5,'Uskudar',2),(6,'Besiktas',3);
INSERT INTO member VALUES (1,'Alice',4),(2,'Ben',4),(3,'Clara',5),(4,'Derek',5),
                       (5,'Evan',6),(6,'Fiona',6),(7,'Grace',4),(8,'Hannah',5);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

.print -- embedded subquery --
SELECT b.name AS branch, ROUND(AVG(t.count), 2) AS per_member
FROM (SELECT m.branch_id, m.id, COUNT(l.id) AS count
      FROM member m LEFT JOIN loan l ON l.member_id = m.id
      GROUP BY m.branch_id, m.id) AS t
JOIN branch b ON b.id = t.branch_id
GROUP BY b.id, b.name
ORDER BY per_member DESC;

.print -- common table expression --
WITH member_count AS (
  SELECT m.branch_id, m.id AS member_id, COUNT(l.id) AS count
  FROM member m LEFT JOIN loan l ON l.member_id = m.id
  GROUP BY m.branch_id, m.id
)
SELECT b.name AS branch, ROUND(AVG(t.count), 2) AS per_member
FROM member_count t JOIN branch b ON b.id = t.branch_id
GROUP BY b.id, b.name
ORDER BY per_member DESC;
SQL

-- embedded subquery --
┌──────────┬────────────┐
│  branch  │ per_member │
├──────────┼────────────┤
│ Kadikoy  │ 3.67       │
│ Besiktas │ 3.0        │
│ Uskudar  │ 2.67       │
└──────────┴────────────┘
-- common table expression --
┌──────────┬────────────┐
│  branch  │ per_member │
├──────────┼────────────┤
│ Kadikoy  │ 3.67       │
│ Besiktas │ 3.0        │
│ Uskudar  │ 2.67       │
└──────────┴────────────┘

The results are the same; what changes is the order in which they read. In the first phrasing, the reader has to descend into the parenthesized part first to understand what the outer query is doing; what the FROM clause corresponds to only becomes clear once the AS t after the closing parenthesis is reached. In the second, the definition has already finished above, and member_count can be thought of as a table while the body is being read. LEFT JOIN preserves members with no loans as zero in both phrasings.

A common table expression is not a view. A view is a permanent object in the schema that other queries can also use; a common table expression exists only for the duration of the statement it is written in, and its name disappears once the statement ends. This impermanence is not a limitation but a design choice: adding a permanent schema object for a single report’s intermediate step turns that object into something someone has to maintain.

Giving Column Names in the Definition

A column list can be written in parentheses after the name. This list names the inner query’s produced columns in order, and removes the need to write AS for each expression individually inside the inner query:

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (4,7,1,'2024-03-11',NULL),(6,2,2,'2024-03-01','2024-03-12'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (13,2,3,'2024-03-25',NULL);

WITH duration(loan_id, member_id, days) AS (
  SELECT id, member_id, julianday(COALESCE(returned, '2024-03-31')) - julianday(pickup)
  FROM loan
)
SELECT member_id, COUNT(*) AS record_count, CAST(AVG(days) AS INT) AS average_days
FROM duration GROUP BY member_id ORDER BY member_id;
SQL

┌───────────┬──────────────┬──────────────┐
│ member_id │ record_count │ average_days │
├───────────┼──────────────┼──────────────┤
│ 1         │ 3            │ 17           │
│ 2         │ 2            │ 10           │
│ 3         │ 2            │ 6            │
└───────────┴──────────────┴──────────────┘

If a column list is given, the number of columns the inner query produces must match the list exactly; a mismatch is a semantic error, not a syntax one, and the engine rejects the statement. Here the days column is an expression that assumes a count-through date for records not yet returned; naming it in the definition means the body never has to see that expression again.

Chaining and Reuse

Several definitions, separated by commas, can be written after WITH. Later definitions can reference earlier ones, so a computation is built step by step. The same name can also be used more than once in the body — something not possible in an embedded subquery:

sqlite3 -box -header <<'SQL'
CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT, parent_id INT);
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch_id INT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO branch VALUES (4,'Kadikoy',2),(5,'Uskudar',2),(6,'Besiktas',3);
INSERT INTO member VALUES (1,'Alice',4),(2,'Ben',4),(3,'Clara',5),(4,'Derek',5),
                       (5,'Evan',6),(6,'Fiona',6),(7,'Grace',4),(8,'Hannah',5);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH member_count AS (
  SELECT m.branch_id, m.id AS member_id, COUNT(l.id) AS count
  FROM member m LEFT JOIN loan l ON l.member_id = m.id
  GROUP BY m.branch_id, m.id
),
branch_total AS (
  SELECT branch_id, SUM(count) AS total FROM member_count GROUP BY branch_id
)
SELECT b.name AS branch, t.total,
       ROUND(100.0 * t.total / (SELECT SUM(total) FROM branch_total), 1) AS percent
FROM branch_total t JOIN branch b ON b.id = t.branch_id
ORDER BY t.total DESC;
SQL

┌──────────┬───────┬─────────┐
│  branch  │ total │ percent │
├──────────┼───────┼─────────┤
│ Kadikoy  │ 11    │ 44.0    │
│ Uskudar  │ 8     │ 32.0    │
│ Besiktas │ 6     │ 24.0    │
└──────────┴───────┴─────────┘

The computation was built in three steps: a per-member count, a per-branch total, and the total’s share of the grand total. branch_total appears in two places — once as a row source in FROM, once as the scalar subquery that supplies the grand total in the SELECT list. In the embedded phrasing, that would mean copying the same definition twice; if one copy got updated and the other got forgotten, the percentages would silently become inconsistent. Because the name is singular, a drift like that is impossible.

The chain is also a debugging tool. The body can be temporarily replaced with SELECT * FROM member_count to see the first step’s output directly, and then branch_total can be tested next. In the embedded phrasing, this requires manually pulling the parenthesized part out to run it and then putting it back.

The order of the definitions is binding: an expression can only reference expressions defined before it. A self-reference is a special case that requires a separate keyword — the subject of the next lesson.

The Materialization Decision

Even with a single name, the computation does not have to happen once. The engine has two options: compute the expression once and hold the result in a temporary structure (materialization), or inline the definition at each place it is used. The first avoids recomputing; the second lets outer conditions be pushed into the expression and lets indexes be used. Neither is always better, so the decision is left to the planner.

The difference can be measured with the same counter as the previous lesson. The three queries below use the same expression twice; the only difference is the materialization directive given to the engine:

cat > cte-measure.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE loan(id INTEGER PRIMARY KEY, member_id INT, pickup TEXT);
  INSERT INTO loan VALUES
    (1,1,'2024-03-01'),(2,1,'2024-03-01'),(3,1,'2024-03-04'),(4,1,'2024-03-11'),
    (5,1,'2024-03-18'),(6,2,'2024-03-01'),(7,2,'2024-03-06'),(8,2,'2024-03-11'),
    (9,2,'2024-03-21'),(10,3,'2024-03-04'),(11,3,'2024-03-06'),(12,3,'2024-03-13'),
    (13,3,'2024-03-25'),(14,4,'2024-03-04'),(15,4,'2024-03-13'),(16,4,'2024-03-20'),
    (17,5,'2024-03-06'),(18,5,'2024-03-11'),(19,5,'2024-03-25'),(20,6,'2024-03-04'),
    (21,6,'2024-03-13'),(22,6,'2024-03-20'),(23,7,'2024-03-11'),(24,7,'2024-03-18'),
    (25,8,'2024-03-13');
`);

let visits = 0;
db.function('passthrough', (value) => { visits += 1; return value; });

const measure = (label, query) => {
  visits = 0;
  const row = db.prepare(query).get();
  console.log(label, JSON.stringify(row), '| visits:', visits);
};

measure('default          ', `
  WITH count AS (SELECT member_id, COUNT(*) AS count FROM loan
                 WHERE passthrough(member_id) IS NOT NULL GROUP BY member_id)
  SELECT (SELECT MAX(count) FROM count) AS maximum,
         (SELECT MIN(count) FROM count) AS minimum`);

measure('MATERIALIZED     ', `
  WITH count AS MATERIALIZED (SELECT member_id, COUNT(*) AS count FROM loan
                 WHERE passthrough(member_id) IS NOT NULL GROUP BY member_id)
  SELECT (SELECT MAX(count) FROM count) AS maximum,
         (SELECT MIN(count) FROM count) AS minimum`);

measure('NOT MATERIALIZED ', `
  WITH count AS NOT MATERIALIZED (SELECT member_id, COUNT(*) AS count FROM loan
                 WHERE passthrough(member_id) IS NOT NULL GROUP BY member_id)
  SELECT (SELECT MAX(count) FROM count) AS maximum,
         (SELECT MIN(count) FROM count) AS minimum`);
JS
node cte-measure.mjs

default           {"maximum":5,"minimum":1} | visits: 25
MATERIALIZED      {"maximum":5,"minimum":1} | visits: 25
NOT MATERIALIZED  {"maximum":5,"minimum":1} | visits: 50

The three results are the same; the amount of work differs. When materialized, the 25-row table is scanned once; when inlined, the definition is copied to each of the two use sites and the table is scanned twice, for a total of 50 rows. In this measurement, the default behavior matches materialization; if the query’s shape were different, the planner could just as well have made the other decision.

The MATERIALIZED and NOT MATERIALIZED directives used here are engine-specific; standard SQL defines no such control, and they are not present in every engine. What is standard is the common table expression’s meaning: what result it produces is defined, but how many times it is computed is left to the planner. For this reason the sentence “using a common table expression means the result is computed once” is not a portable assumption; some engines always materialize the expression, some never do, and in some the decision depends on the query. If an expression is used in more than one place and its definition is expensive, the target engine’s behavior has to be measured.

In Data-Modifying Statements

WITH can be written not only before a SELECT statement but also before INSERT, UPDATE, and DELETE statements. The logic that determines which rows get changed is thereby separated from the statement and becomes readable on its own:

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (4,7,1,'2024-03-11',NULL),(9,8,2,'2024-03-21',NULL),(13,2,3,'2024-03-25',NULL),
  (16,4,4,'2024-03-20',NULL),(22,3,6,'2024-03-20',NULL),(1,1,1,'2024-03-01','2024-03-15');

WITH overdue AS (
  SELECT id FROM loan WHERE returned IS NULL AND pickup <= '2024-03-20'
)
UPDATE loan SET returned = '2024-03-31' WHERE id IN (SELECT id FROM overdue);
SELECT changes() AS updated;
SELECT id, pickup, returned FROM loan WHERE returned IS NULL ORDER BY id;
SQL

┌─────────┐
│ updated │
├─────────┤
│ 3       │
└─────────┘
┌────┬────────────┬──────────┐
│ id │   pickup   │ returned │
├────┼────────────┼──────────┤
│ 9  │ 2024-03-21 │          │
│ 13 │ 2024-03-25 │          │
└────┴────────────┴──────────┘

Three rows were updated; two records whose pickup date passed March 20 remained open. This phrasing’s practical benefit is that the same expression can be run as a SELECT before the update to see which rows will be affected. Because the selection criterion is defined in exactly one place, no drift is left between the check and the application — writing the condition twice has, as its most expensive consequence, the update hitting a different set than the one checked.

Summary

• A common table expression names an intermediate result at the start of a statement; the name lives only for that statement and does not create a permanent schema object.
• An optional column list can be added to the name; when given, the inner query’s column count must match the list exactly.
• Multiple expressions are chained with commas; each expression can reference only those defined before it, and the same name can be used more than once in the body.
• A single name does not mean a single computation: the materialization decision belongs to the planner, and the same expression can be computed twice; in the measurement this came out as 25 against 50 rows.
• WITH can also be written before data-modifying statements; the selection criterion can then be checked with SELECT before it is applied.

Next Step

It was said that a common table expression can reference only expressions defined before it; the one exception is an expression referencing itself. This exception is not a small syntax detail: it opens SQL up to computations that cannot be expressed with a fixed number of steps — climbing to the root in a branch tree, finding every node reachable from a given node. The next lesson introduces the recursive common table expression, computes depth over a hierarchy, and shows what happens with no cycle guard, in a bounded number of steps.