Subqueries and CTEs

CTE

Naming an Intermediate Result

A common table expression gives a query result a temporary name.

Program

Play the query to build totals, then filter it.

cte_totals.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (item TEXT, qty INTEGER);
INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);
WITH totals AS (SELECT item, SUM(qty) AS sold FROM sales GROUP BY item) SELECT item, sold FROM totals WHERE sold >= 5 ORDER BY item;

  1. sales ← 0 rows

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);
    values this step0 rowssales

  2. sales ← 3 rows

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);3WITH totals AS (SELECT item, SUM(qty) AS sold FROM sales GROUP BY item) SELECT item, sold FROM totals WHERE sold >= 5 ORDER BY item;
    values this step3 rowssales

  3. result ← 2 rows

    2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3);3WITH totals AS (SELECT item, SUM(qty) AS sold FROM sales GROUP BY item) SELECT item, sold FROM totals WHERE sold >= 5 ORDER BY item;
    values this step2 rowsresult

Follow the CTE

  1. The sales rows are book 2, pen 5, and book 3.
  2. The CTE groups matching item names together.
  3. book totals 2 + 3, which is 5.
  4. pen totals 5.
  5. WHERE sold >= 5 keeps both grouped rows. | item | rows added | sold | kept? | | --- | --- | --- | --- | | book | 2 + 3 | 5 | yes | | pen | 5 | 5 | yes |
WITH `WITH totals AS (...)` names an intermediate query.
temporary table The CTE behaves like a temporary table for the following `SELECT`.
readability CTEs make multi-step queries easier to read.

Exercise: cte_totals.sql

Reproduce the grouped totals book=5 and pen=5, then predict which rows pass the sold >= 5 filter.