Read this recipe step: "pour into the pan the spice paste ground from the onions you sliced". To make sense of it, you have to read it backwards: slice the onions, grind them, then pour. A recipe that is easy to read names the step in the middle: make the spice paste first, then "pour the spice paste into the pan".
A query with nested subqueries (a subquery inside a subquery) has the same problem: it reads from the inside out. A CTE (Common Table Expression) does what the second recipe does. With WITH, you give a query a name at the start, like the spice paste you make first. Then the main query (the last query, the one that gives the answer) uses that name like any other table, like the step "pour the spice paste into the pan". So the query reads from top to bottom.
From subquery to CTE
In the Subquery lesson you found the products priced above the average with WHERE price > (SELECT AVG(price) FROM products). Now the question is the same, but the data is different: the employees table holds 5 employees, with the columns id, name and salary. Their salaries: Budi 5,000, Siti 7,000, Andi 9,000, Dewi 12,000, and Rizky 4,000.
The subquery version, the one you already know:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
The CTE version:
WITH avg_salary AS (
SELECT AVG(salary) AS amount FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT amount FROM avg_salary)
ORDER BY salary DESC;
Both return the same 2 rows: Dewi 12,000, then Andi 9,000. A CTE changes no result. It only moves the subquery to the top and gives it a name.
WITH avg_salary AS ( ... )makes a step calledavg_salary. Inside its parentheses is an ordinary query.AVG(salary) AS amountgives the result column the aliasamount.avg_salaryholds just 1 row and 1 column: 7,400 (the total, 37,000, divided by 5 employees).(SELECT amount FROM avg_salary)fetches that number. To the main query, a CTE is a table, not a variable (a name for one value), so you read it withSELECT.- The main query comes right after the closing parenthesis, with no semicolon in between. A CTE only works inside that one query: the next query can no longer use
avg_salary. ORDER BY salary DESCsits in the main query, at the very end, so Dewi (the biggest salary) comes out in the first row.
Several CTEs in a row
Now there are two new questions: how many people earn above the average, and what is their average salary? With subqueries, the answer becomes parentheses inside parentheses. There is a subquery in FROM, used like a table, so it must be named with AS. Inside that subquery is another subquery:
SELECT COUNT(*) AS headcount, AVG(salary) AS average_salary
FROM (
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
) AS high_earners;
With CTEs, the steps line up from top to bottom:
WITH avg_salary AS (
SELECT AVG(salary) AS amount FROM employees
),
high_earners AS (
SELECT name, salary FROM employees
WHERE salary > (SELECT amount FROM avg_salary)
)
SELECT COUNT(*) AS headcount, AVG(salary) AS average_salary
FROM high_earners;
Both give the same result: headcount 2 and average_salary 10,500 (the average salary of Dewi and Andi).
- One
WITHat the front is enough. The next CTE follows after a comma, with no secondWITH. - A CTE further down may use the ones above it:
high_earnersusesavg_salary. - The subquery version reads from the inside out. The CTE version reads from top to bottom, and you can check each step on its own, for example with
SELECT * FROM high_earnersas the main query. - When one result is used twice, a CTE is written once. For example, the average is used in
WHERE, and again in a column that calculates the difference between each salary and the average. A subquery has to be typed out again in both places.
WITH RECURSIVE: walking a tree
The categories of an online shop are often a tree: one category has children (subcategories under it), and a child can have children too. Each category stores a parent_id, the id of the category above it (NULL for a category at the top). This is the tree, with each category's id in parentheses:
Electronics (1)
Computers (2)
Laptop (4)
Accessories (5)
Mouse (6)
Phones (3)
Fashion (7)
T-shirts (8)
You want every subcategory under Electronics, however deep. One plain JOIN only goes down one level, for example from Electronics to Computers. WITH RECURSIVE repeats the JOIN, round after round, until there are no children left. The query has two parts joined by UNION ALL (which stacks the results of two queries into one):
WITH RECURSIVE category_tree AS (
-- anchor: the starting point, runs once
SELECT id, name, parent_id, 0 AS level
FROM categories
WHERE id = 1
UNION ALL
-- recursive: children of the rows found so far
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
This is SQLite's result. The Round column is not in the real result: it is added here so you can see which round found each row.
| Round | id | name | parent_id | level |
|---|---|---|---|---|
| anchor | 1 | Electronics | NULL | 0 |
| 1 | 2 | Computers | 1 | 1 |
| 1 | 3 | Phones | 1 | 1 |
| 2 | 5 | Accessories | 2 | 2 |
| 2 | 4 | Laptop | 2 | 2 |
| 3 | 6 | Mouse | 5 | 3 |
- The anchor (the starting point) is the query before
UNION ALL. The anchor runs once:WHERE id = 1returns Electronics at level 0. - The recursive part is the query after
UNION ALL. It namescategory_tree, the CTE's own name. In every round, the recursive part looks for categories whoseparent_idpoints at the rows found in the round before, and adds 1 tolevel. UNION ALLstacks the rows from the anchor and from every round into one table.- Round 4 looks for Mouse's children and finds nothing. An empty round stops the recursion (the repeating) by itself. Fashion and T-shirts are not in it, because they are not descendants of Electronics.
- Without
ORDER BY, the order of the rows is up to the database (SQLite puts Accessories before Laptop here, though Laptop has the smaller id). AddORDER BY level, nameto the main query when the order matters.
The word RECURSIVE must be written. SQLite still runs the query when you forget it, but PostgreSQL and MySQL reject it.
CTE or subquery?
| Subquery | CTE | |
|---|---|---|
| Shape | in parentheses, in the middle of the query | named at the top, with WITH |
| How it reads | from the inside out | from top to bottom |
| Used twice | typed out again | written once |
| Recursion | not possible | possible, with WITH RECURSIVE |
| Speed | the same in most databases | the same in most databases |
A CTE fits better when the same result is used two or more times, when the query starts getting hard to read, or when you need recursion. A CTE is not a trick to make a query faster. For one number used once, a short subquery is enough.
📌 SQLite, PostgreSQL and MySQL differ
The exercise and the Lab here use SQLite. CTEs and WITH RECURSIVE are in SQLite (since version 3.8.3), PostgreSQL, and MySQL 8.0 and up. MySQL 5.7 has no CTEs at all.
- Forgetting
RECURSIVE: SQLite still runs it, while PostgreSQL and MySQL answer with an error that the tablecategory_treedoesn't exist. - A recursion with no stop condition: in SQLite and PostgreSQL, the query keeps going until you cancel it. MySQL stops with an error after 1,000 rounds (that limit is set by
cte_max_recursion_depth).
⚠️ Stray semicolons and recursion that never stops
- A semicolon after the CTE's closing parenthesis.
WITH avg_salary AS (...);followed by aSELECTon the next line is refused by SQLite withnear ";": syntax error. That semicolon ends the command before the main query comes. The CTE and its main query are one command, so one semicolon at the very end is enough. - Using a CTE like a variable.
WHERE salary > avg_salarygives the errorno such column: avg_salary(there is no column with that name). Fetch the number with(SELECT amount FROM avg_salary). - A recursion with no stop condition. Look at
WITH RECURSIVE numbers(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < 5). The(n)after the CTE's name gives its column the namen. The anchor isSELECT 1, and every round adds 1.WHERE n < 5is the stop condition, so the counting stops at 5. Remove theWHEREand every round always finds a new number, so the query never finishes. ORDER BYin the wrong place. The order inside a CTE doesn't promise the order of the final result. PutORDER BY salary DESCin the main query, so the first row is Dewi.
TL;DR: A CTE is a query given a name with WITH: WITH name AS (SELECT ...). Right after it comes the main query (the one that gives the answer), which uses name like a table, with no semicolon in between. The result is the same as with a subquery, but the query reads from top to bottom, and a CTE's result can be used many times. Several CTEs are written under one WITH, separated by commas. WITH RECURSIVE repeats a query to walk data shaped like a tree, such as categories and their subcategories. It holds an anchor (the starting point) that runs once, UNION ALL that stacks the results, and a recursive part that uses the rows from the round before. The repeating stops when a round finds no new rows, so make sure it has a stop condition. A CTE doesn't make a query faster: use one when a result is used twice, the query is hard to read, or you need to repeat a query with WITH RECURSIVE.
⚡ Name the step in the middle
Write:
WITH avg_salary AS (
SELECT AVG(salary) AS amount FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT amount FROM avg_salary)
ORDER BY salary DESC;
Meaning: This query looks for the employees in the employees table who earn above the average. WITH gives the name avg_salary to the step in the middle: the query that calculates the average salary. A query given a name with WITH is called a CTE. The main query below it uses avg_salary like a table. The result is the same as with the subquery (SELECT AVG(salary) FROM employees): Dewi, then Andi.
⚡ No semicolon in the middle
Meaning: The CTE and its main query are one command, so there's only one semicolon, at the very end. To the main query, a CTE is a table, not a variable (a name for one value): fetch its value with (SELECT amount FROM avg_salary), where amount is its column's name.
⚡ Several CTEs in a row
Meaning: One WITH is enough, and the next CTEs are separated by commas. A CTE further down may use the ones above it, so the query reads from top to bottom.
⚡ CTE or subquery?
Guess first: a CTE and a subquery give the same result. When is a CTE the better choice?
When the same result is used twice or more, when the query is hard to read, or when you need recursion (a query that repeats itself, written WITH RECURSIVE). A CTE doesn't make a query faster.
Meaning: WITH RECURSIVE can walk a category tree (categories, subcategories and so on) however deep it goes, as long as it has a stop condition.