Common Table Expressions: Queries in Named Steps
Break long queries into readable named steps with WITH, chain several CTEs together and meet recursive CTEs.
In this lesson you will
- Write a query with WITH
- Chain several CTEs
- Understand a recursive CTE
- Choose between a subquery, a CTE and a view
About 28 minutes
A query that reads like a story
Maya’s reports now need several steps. First work out the total of each order. Then add up the orders for each customer. Then compare each customer with the average. Written as nested subqueries, this becomes a thicket of brackets that nobody can read, not even Maya a week later. The remedy is the common table expression, or CTE. It lets you name a step of the calculation at the top of the query and use the name below, as if it were a table. A CTE exists only for that one query. Nothing is stored in the database.
The shape of a CTE
A CTE starts with the word WITH, then a name, then AS and a query in brackets. After it comes the main query, which can use the name like any table. Several CTEs can be listed, separated by commas, and a later one may use an earlier one. The result is a query you can read from top to bottom: step one, step two, the answer. Splitting a problem into named steps also makes it far easier to test, because you can run each step alone.
Four queries built from steps
Every query below begins with the same step, called order_totals, which joins orders, order items and products to give one total per order. Query 1 simply shows that step. Query 2 builds on it to give each customer’s shipped orders and spending. Query 3 adds a second CTE, avg_total, which holds the average shipped order, and then lists the orders above that average. Query 4 is different: a recursive CTE that builds a short list of numbers from nothing.
-- 1
WITH order_totals AS (
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, status, total FROM order_totals ORDER BY order_id
-- 2
WITH order_totals AS (
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT c.name, COUNT(*) AS orders, ROUND(SUM(t.total), 2) AS spent
FROM order_totals AS t JOIN customers AS c ON c.id = t.customer_id
WHERE t.status = 'shipped' GROUP BY c.id ORDER BY spent DESC
-- 3
WITH order_totals AS (
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id GROUP BY o.id),
avg_total AS (SELECT AVG(total) AS a FROM order_totals WHERE status = 'shipped')
SELECT order_id, total FROM order_totals, avg_total
WHERE status = 'shipped' AND total > a ORDER BY total DESC
-- 4
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5)
SELECT i, i * i AS square FROM n
-- 1
order_id | status | total
1 | shipped | 42.00
2 | shipped | 23.50
3 | shipped | 33.00
4 | cancelled | 12.50
5 | shipped | 26.50
6 | shipped | 27.50
7 | pending | 24.00
8 | shipped | 41.50
9 | shipped | 63.00
10 | pending | 24.00
-- 2
name | orders | spent
Asha | 3 | 116.50
Dev | 2 | 89.50
Ben | 2 | 51.00
-- 3
order_id | total
9 | 63.00
1 | 42.00
8 | 41.50
-- 4
i | square
1 | 1
2 | 4
3 | 9
4 | 16
5 | 25
Query 1 gave 10 rows, one for each order. The cancelled one, order 4, has a total of 12.50, which later queries must not count as revenue. Query 2 found that Asha spent 116.50 over 3 orders, then Dev with 89.50, then Ben with 51.00. In query 3, the average shipped order is 36.71, and 3 orders beat it: order 9 at 63.00, order 1 at 42.00, order 8 at 41.50. Because the shipped orders total 257.00 across seven orders, the average is not a round number, but that causes no trouble. Query 3 also shows that a CTE can be used in a plain join with another CTE. Without CTEs, it would need a subquery in the WHERE clause or a nested table, and the reader would have to work from the inside out.
Recursive CTEs
Query 4 is a recursive CTE. It has two parts joined by UNION ALL. The first part, the anchor, starts with the number 1. The second part refers to the CTE itself and adds 1 each time, stopping when the number reaches 5. The result is the numbers 1 to 5, with their squares alongside. Recursive CTEs are the standard way to walk through a hierarchy, such as managers and their teams, or to generate a series of dates to fill gaps in a report. The syntax differs between databases: some require the word RECURSIVE and some do not use it at all. Check yours. Always include a stopping condition, as a mistake can otherwise loop for a very long time, and most databases have a safety limit.
CTE, subquery or view?
- Subquery: fine for a short test inside one condition.
- CTE: best for multi-step queries that you want to read top to bottom. It lives only for that query.
- View: a saved query with a name, kept in the database for many queries to use. Lesson 19 covers views.
- Some databases treat a CTE as an optimisation barrier and some do not, so for very large data, test the speed rather than assuming.
Writing one huge query and debugging it all at once. Build it one CTE at a time, running each step alone and checking its rows before adding the next.
The data is made up, in sample money. CTEs are standard SQL and widely supported, but details of the recursive form and some performance behaviour vary by database and version, so check your documentation.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is a common table expression?
- A table stored in the database
- A named step defined with WITH and used by the main query
- A kind of index
- A type of join
It lasts only for that one query.
Why use CTEs?
- They make data permanent
- They remove NULLs
- They let a long query read top to bottom in named steps
- They need no SELECT
Each step can be tested alone.
What does a recursive CTE need?
- An anchor part, a part that refers to itself and a stopping condition
- A view
- An index
- A GROUP BY
The number list started at 1 and stopped at 5.
How is a view different from a CTE?
- A view is faster
- A view has no query
- A view is stored in the database for many queries
- A view cannot be read
A CTE exists only for one query.
What is a good habit when building with CTEs?
- Run and check each step alone before adding the next
- Write all steps at once
- Avoid names
- Skip testing
Small checked steps are easier to debug.
Summary
- A CTE names a step of a query and lasts for that query only.
- Several CTEs can build on each other.
- Recursive CTEs need an anchor, a recursion and a stop.
- Build and test one step at a time.
Questions
Does a CTE store data?
No. It exists only while the query runs. A view is the stored version of a query.
Is RECURSIVE always required?
No. Some databases require the word, some do not use it. Check your own system.
Are CTEs faster than subqueries?
Not by themselves. They mostly make queries clearer. Speed depends on the database, so test with your own data.
