Common Table Expressions (WITH)
Give a query building block a name, then read your SQL top to bottom like a recipe
Big Idea: A Common Table Expression (CTE) is a temporary, named result set defined with the
WITH keyword at the top of a statement. Instead of burying a subquery deep inside another query, you name it once and then refer to that name like an ordinary table. One statement can define several CTEs, and each may build on the ones before it, turning a tangled nest of parentheses into a clear, ordered pipeline.
By the end of this topic you will be able to:
- define a single CTE with
WITH name AS (SELECT ...)and query it, - chain several CTEs into a readable multi-step pipeline,
- explain why a CTE is easier to read than the same logic as a nested subquery,
- reference one CTE from another and from the final query,
- state the scope rules: how long a CTE lives and what can see it.
0. Our example table
Every query on this page uses one small table called sales. It records one row per book sold, with the seller, their region, the title and the sale amount in dollars. Here is how it is built and filled:
CREATE TABLE sales (
id INT PRIMARY KEY,
seller VARCHAR(30),
region VARCHAR(20),
book VARCHAR(30),
amount INT
);
INSERT INTO sales (id, seller, region, book, amount) VALUES
(1, 'Ann', 'North', 'SQL Basics', 40),
(2, 'Ann', 'North', 'Joins Deep', 55),
(3, 'Bo', 'North', 'SQL Basics', 40),
(4, 'Cy', 'South', 'Indexes 101', 30),
(5, 'Cy', 'South', 'SQL Basics', 40),
(6, 'Di', 'East', 'Joins Deep', 55),
(7, 'Di', 'East', 'Indexes 101', 30),
(8, 'Ann', 'North', 'Indexes 101', 30);So the whole table looks like this:
| id | seller | region | book | amount |
|---|---|---|---|---|
| 1 | Ann | North | SQL Basics | 40 |
| 2 | Ann | North | Joins Deep | 55 |
| 3 | Bo | North | SQL Basics | 40 |
| 4 | Cy | South | Indexes 101 | 30 |
| 5 | Cy | South | SQL Basics | 40 |
| 6 | Di | East | Joins Deep | 55 |
| 7 | Di | East | Indexes 101 | 30 |
| 8 | Ann | North | Indexes 101 | 30 |
Keep these numbers handy. The three regions add up to North = 165 (rows 1, 2, 3, 8), South = 70 (rows 4, 5) and East = 85 (rows 6, 7). We will compute these totals with a CTE in a moment.