Session 8 — Common table expressions (WITH)

A CTE lifts a subquery to the top of the query and names it: WITH recent AS (SELECT …) SELECT … FROM recent. It flattens deeply nested queries into readable pipelines, lets stages build on earlier stages, and — uniquely — lets the main query reference the same CTE more than once. You will also see WITH RECURSIVE for walking hierarchies, and how CTEs compare with subqueries and views.

Plan: Core ideas → worked pipelines → Quiz.

Time: about 60–70 minutes if you run every CTE separately before reading the main query.

Session 8 — Common table expressions (WITH)

The one-paragraph story

A common table expression — a CTE — is a subquery pulled out to the top of the query and given a name: WITH recent AS (SELECT ...) SELECT ... FROM recent. It turns a deeply nested query into a flat, readable sequence of steps, and it lets you reuse the same intermediate result in several places (including, crucially, in another CTE). This session shows why CTEs exist, how they compose, and the one genuinely new power they add over subqueries: a query can reference a CTE more than once, and CTEs can build on each other.

---

The problem CTEs solve

Complex queries nest subqueries like Russian dolls, and the innermost expression ends up far from where it is used:

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
  AND book_id IN (
      SELECT book_id FROM sales
      WHERE qty = (SELECT MAX(qty) FROM sales)
  );

Readable? Barely. Now the CTE version — same logic, named steps:

WITH
    avg_price AS (SELECT AVG(price) AS a FROM books),
    top_sale  AS (SELECT book_id FROM sales WHERE qty = (SELECT MAX(qty) FROM sales))
SELECT b.title, b.price
FROM books b, avg_price, top_sale
WHERE b.price > avg_price.a
  AND b.book_id IN (SELECT book_id FROM top_sale);

The WITH block runs first, names each piece, and the main query reads like a sentence: "from books, using the average price and the top sale, keep the pricier-than-average books that were in the top sale."

---

CTE mechanics

• A CTE is a SELECT (or VALUES/INSERT/… in some engines) with a name and an optional column list.
• It is visible only to the query that declares it — CTEs do not persist; they are a rewrite, not a stored object.
• CTEs can reference earlier CTEs in the same WITH block, so you can build a pipeline:

WITH
    sold AS (
        SELECT book_id, SUM(qty) AS total
        FROM sales GROUP BY book_id
    ),
    popular AS (
        SELECT book_id FROM sold WHERE total >= 2
    )
SELECT b.title
FROM books b
JOIN popular p ON p.book_id = b.book_id;

sold computes per-book totals; popular filters them; the main query joins the survivors to titles. Each stage is small, named, and checkable on its own — that is the whole point.

---

Reusing a CTE twice

Because a CTE is a named, reusable piece, the main query can reference it more than once — something a plain inline subquery cannot do without repeating itself. Example: find pairs of books that share a genre:

WITH all_books AS (SELECT book_id, title, genre FROM books)
SELECT a.title AS first_book, b.title AS second_book
FROM all_books a
JOIN all_books b ON a.genre = b.genre AND a.book_id < b.book_id;

all_books appears on both sides of the join — a self-join (Session 6) built from a CTE instead of the raw table. The a.book_id < b.book_id keeps each pair once rather than twice.

---

CTE vs subquery vs view

• A subquery lives inside a clause and cannot be named or reused.
• A CTE lives at the top, is named, can chain, and can be referenced several times in one query.
• A view (Session 12) is a stored query that persists across sessions — a CTE is a per-query view.

When a subquery gets deep, hoist it into a CTE. When a CTE becomes generally useful, promote it to a view.

---

Recursive CTEs: a glimpse

Some engines let a CTE reference itself, enabling walking hierarchical data (an org chart, a comment thread):

WITH RECURSIVE chain AS (
    SELECT staff_id, name, manager_id, 0 AS depth FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.name, s.manager_id, c.depth + 1
    FROM staff s JOIN chain c ON s.manager_id = c.staff_id
)
SELECT * FROM chain ORDER BY depth;

The base row (the top of the tree) seeds the CTE, and the recursive UNION ALL extends it one level at a time. This is powerful and engine-specific; treat it as a known existence, not a default tool — the non-recursive CTEs of this session cover most real needs.

---

Pitfalls to watch for

• CTEs are scoped to one query — you cannot reference sold in a later, separate statement.
• CTE names must be unique within a WITH block — a duplicate name is an error.
• Order matters for chaining — a CTE can only use CTEs declared before it.
• WITH RECURSIVE needs a base case — without one, the recursion never terminates.

Session 8 — Worked examples

The bookshop data:

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2) — (sale_id, book_id, qty)

staff: (1, Mira Chen, NULL), (2, Leo Park, 1), (3, Ava Stone, 2), (4, Tom Hale, 2)

Example A — A single named CTE

WITH fiction_books AS (
    SELECT book_id, title, price
    FROM books
    WHERE genre = 'fiction'
)
SELECT title, price FROM fiction_books ORDER BY price DESC;

CTE first: fiction_books = the two fiction books (10, 11). Main query then reads from that named set:

| title | price | |-------|-------| | The Glass Harbour | 24.99 | | Static Echoes | 19.50 |

Trace discipline: run the CTE first and write its result table; the main query is then a normal query against that table.

---

Example B — Chained CTEs: a pipeline

WITH
    per_book AS (
        SELECT book_id, SUM(qty) AS total
        FROM sales
        GROUP BY book_id
    ),
    sellers AS (
        SELECT book_id FROM per_book WHERE total >= 2
    )
SELECT b.title, pb.total
FROM books b
JOIN per_book pb ON pb.book_id = b.book_id
JOIN sellers s  ON s.book_id = b.book_id
ORDER BY pb.total DESC;

Stage 1 per_book — totals per book: book 10 → 5, book 12 → 1, book 14 → 1, book 13 → 2. Stage 2 sellers — books with ≥ 2: book 10, book 13. Main query joins both:

| title | total | |-------|-------| | The Glass Harbour | 5 | | Salt Roads | 2 |

---

Example C — Reusing one CTE twice

WITH all_books AS (SELECT book_id, title, genre FROM books)
SELECT a.title AS first_book, b.title AS second_book
FROM all_books a
JOIN all_books b
  ON a.genre = b.genre AND a.book_id < b.book_id
ORDER BY a.title;

Same genre, id ascending — pairs: (10, 11) fiction; (12, 15) mystery; (13, 14) history.

| first_book | second_book | |------------|-------------| | The Glass Harbour | Static Echoes | | River Clock | Prairie Circuit | | Salt Roads | North of Noon |

The book_id < book_id guard makes each unordered pair appear once.

---

Example D — CTE replacing a subquery

The nested query from the concept, rewritten as CTEs:

WITH
    avg_price AS (SELECT AVG(price) AS a FROM books),
    top_sale  AS (SELECT book_id FROM sales WHERE qty = (SELECT MAX(qty) FROM sales))
SELECT b.title, b.price
FROM books b, avg_price, top_sale
WHERE b.price > avg_price.a
  AND b.book_id IN (SELECT book_id FROM top_sale);

avg_price.a = 23.915; top_sale = {10} (the qty-3 sale). Books above average: 13, 14. Among those, book 13 is in top_sale. Result: Salt Roads, 31.00.

---

Example E — Recursive CTE on staff

WITH RECURSIVE chain AS (
    SELECT staff_id, name, manager_id, 0 AS depth
    FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.name, s.manager_id, c.depth + 1
    FROM staff s JOIN chain c ON s.manager_id = c.staff_id
)
SELECT name, depth FROM chain ORDER BY depth;

Base row: Mira Chen (depth 0). Recursion adds Leo Park (manager 1, depth 1), then Ava Stone and Tom Hale (manager 2, depth 2).

| name | depth | |------|-------| | Mira Chen | 0 | | Leo Park | 1 | | Ava Stone | 2 | | Tom Hale | 2 |

Read the UNION ALL as "keep adding rows until nothing new appears". Without the base case (WHERE manager_id IS NULL), the recursion could loop forever.