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
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;
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.