Common table expressions with WITH
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A report needs three steps: total per customer, then the average of those totals, then who is above it. Nested in one block, the query becomes hard to read. How can you write it step by step?
WITH: name a step of the query
A CTE (common table expression) is a named, temporary result defined with WITH name AS (SELECT ...) before a query. The query that follows reads name like a table.
A long query is easier to read in steps. Give the first step a name with WITH, then use that name in the main query.
Example 1 · A derived table rewritten as a CTE
WITH order_totals AS ( SELECT order_id, SUM(quantity * unit_price) AS order_total FROM order_items GROUP BY order_id)SELECT COUNT(*) AS orders, ROUND(AVG(order_total), 2) AS avg_order_value, MAX(order_total) AS biggest_orderFROM order_totals;+--------+-----------------+---------------+ | orders | avg_order_value | biggest_order | +--------+-----------------+---------------+ | 15 | 20622.87 | 69900.00 | +--------+-----------------+---------------+
What this code does
order_totalsis defined first, with one row per order. The main query then reads it like a table.- This is the same result as the derived table in the previous lesson, but the steps now read in order: first the totals, then the summary.
CTEs arrived in MySQL 8.0; MySQL 5.7 does not have them. A derived table does the same job, but it sits in the middle of FROM, so you read a long query from the inside out. A CTE moves that step to the top and gives it a name that explains it, such as order_totals. It is easier to read and to check.
A CTE lives only for the one statement it belongs to. A second statement that uses the name fails with error 1146, Table 'shop.order_totals' doesn't exist (with your own database name). To keep a query for reuse, save it as a view, which is the next lesson.
Order of execution
Put the WITH query in order
Order the lines so the step is defined before the main query uses it.
SELECT to SELECT * FROM order_totals; and look at that step's rows on their own.Several CTEs in one query
Several CTEs are written after one WITH, separated by commas: WITH a AS (...), b AS (...) SELECT .... Writing WITH a second time is a syntax error (1064). A later CTE can use any earlier one, so each step builds on the last.
Example 2 · Best month: one CTE used twice
WITH monthly AS ( SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status <> 'CANCELLED' GROUP BY month)SELECT month, revenueFROM monthlyWHERE revenue = (SELECT MAX(revenue) FROM monthly);+---------+----------+ | month | revenue | +---------+----------+ | 2025-03 | 72083.00 | +---------+----------+
What this code does
monthlyhas one row per month. The main query reads it once inFROMand once more inside the scalar subquery.- March 2025 had the highest revenue, mostly from one HP gaming laptop order. If two months tied,
=would show both.
Example 3 · Two CTEs: customers against their city's average
WITH customer_spend AS ( SELECT c.customer_id, c.name, c.city, SUM(oi.quantity * oi.unit_price) AS spent FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status <> 'CANCELLED' GROUP BY c.customer_id, c.name, c.city),city_avg AS ( SELECT city, AVG(spent) AS avg_spent, COUNT(*) AS customers FROM customer_spend -- uses the first CTE GROUP BY city)SELECT cs.name, cs.city, cs.spent, ROUND(ca.avg_spent, 2) AS city_avgFROM customer_spend csJOIN city_avg ca ON ca.city = cs.cityWHERE ca.customers > 1ORDER BY cs.city, cs.spent DESC;+--------------+-------+----------+----------+ | name | city | spent | city_avg | +--------------+-------+----------+----------+ | Kabir Singh | Delhi | 27196.00 | 23247.00 | | Rahul Verma | Delhi | 19298.00 | 23247.00 | | Priya Sharma | Pune | 5196.00 | 3248.00 | | Sneha Patil | Pune | 1300.00 | 3248.00 | +--------------+-------+----------+----------+
What this code does
- One
WITH, two CTEs, separated by a comma.city_avgis built fromcustomer_spend, so the order of the CTEs matters. - The final query joins the two steps and keeps cities with more than one buying customer, Delhi and Pune, where a comparison means something.
- Each customer now sits next to their city's average spend, which is ready for a "top customer in each city" report.
A CTE can also be used more than once in the same query. "The month with the highest revenue" needs the monthly revenue twice: once to find the maximum, once to find the month that has it. With a derived table you would have to write the whole subquery twice.
Give each CTE a name that says what one row is: monthly, customer_spend, city_avg. Anyone reading the final SELECT can then follow the logic without reading every step.
Fix the error
Fix the syntax error
Two CTEs share one WITH. Pick the line that fixes the query.
Define two CTEs, a and b.
Error
ERROR 1064 (42000): You have an error in your SQL syntax
Readable reports with CTEs
A report built from CTEs follows the same plan you would use on paper: collect the raw numbers, summarise them, then put the summary next to a total. Each step is a CTE, and the last SELECT only arranges the columns.
Example 4 · Category sales report for ShopLite
WITH category_sales AS ( -- step 1 and 2: rows, then a summary SELECT cat.name AS category, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id LEFT JOIN categories cat ON cat.category_id = p.category_id WHERE o.status <> 'CANCELLED' GROUP BY cat.name),total AS ( -- step 3: the grand total SELECT SUM(revenue) AS all_revenue FROM category_sales)SELECT COALESCE(category, '(none)') AS category, units, revenue, ROUND(100 * revenue / all_revenue, 1) AS pctFROM category_salesCROSS JOIN totalORDER BY revenue DESC;+-------------+-------+-----------+------+ | category | units | revenue | pct | +-------------+-------+-----------+------+ | Laptops | 2 | 107980.00 | 55.0 | | Mobiles | 3 | 55997.00 | 28.5 | | Accessories | 11 | 16539.00 | 8.4 | | Fashion | 6 | 10194.00 | 5.2 | | Books | 8 | 4145.00 | 2.1 | | (none) | 2 | 1000.00 | 0.5 | | Toys | 2 | 598.00 | 0.3 | +-------------+-------+-----------+------+
What this code does
category_salescollects and summarises;totaladds one number, the grand total.CROSS JOIN totalputs that single row next to every category row.- Laptops bring 55% of the revenue from only 2 units, while Accessories sell the most units (11) for 8.4%.
- The Gift Card has no category, so the
LEFT JOINkeeps it with aNULLname, shown as(none). An inner join would have dropped Rs. 1000 from the report.
A category sales report for ShopLite needs revenue and units per category, the grand total, and each category's share of it. One CTE per idea keeps every step small enough to check on its own. This is also how teams review SQL. A reviewer can read category_sales, agree that it is right, and move to the next step, instead of untangling one long nested query.
- Step 1: gather rows (joins and filters).
- Step 2: summarise (
GROUP BY). - Step 3: add totals or comparisons.
- Final
SELECT: choose, round and order the columns.
Recursive CTEs: an org chart and a category tree
A recursive CTE, written WITH RECURSIVE, refers to itself. It starts from an anchor row and keeps adding rows until a condition stops it, so it can follow a chain of any length.
A recursive CTE (WITH RECURSIVE) refers to itself, so it can follow a chain of rows of any length: employee to manager to manager, or category to parent category.
WITH RECURSIVE counter AS ( SELECT 1 AS n -- anchor: the first row UNION ALL SELECT n + 1 -- recursive member: the next row FROM counter WHERE n < 5 -- stop condition)SELECT n FROM counter;+------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
Example 5 · Org chart: everyone under Vikram Rao
WITH RECURSIVE org AS ( -- anchor: the person at the top SELECT emp_id, name, 1 AS level, CAST(name AS CHAR(200)) AS chain FROM employees WHERE manager_id IS NULL
UNION ALL
-- recursive member: people whose manager is already in org SELECT e.emp_id, e.name, org.level + 1, CONCAT(org.chain, ' > ', e.name) FROM employees e JOIN org ON e.manager_id = org.emp_id)SELECT level, CONCAT(REPEAT(' ', level - 1), name) AS employee, chainFROM orgORDER BY chain;+-------+----------------------+----------------------------------------+ | level | employee | chain | +-------+----------------------+----------------------------------------+ | 1 | Vikram Rao | Vikram Rao | | 2 | Anita Desai | Vikram Rao > Anita Desai | | 3 | Karthik Raj | Vikram Rao > Anita Desai > Karthik Raj | | 3 | Pooja Gupta | Vikram Rao > Anita Desai > Pooja Gupta | | 3 | Suresh Babu | Vikram Rao > Anita Desai > Suresh Babu | | 2 | Gita Reddy | Vikram Rao > Gita Reddy | | 3 | Manoj Pillai | Vikram Rao > Gita Reddy > Manoj Pillai | | 2 | Lakshmi Menon | Vikram Rao > Lakshmi Menon | | 3 | Rohan Das | Vikram Rao > Lakshmi Menon > Rohan Das | | 2 | Neha Joshi | Vikram Rao > Neha Joshi | | 3 | Amit Shah | Vikram Rao > Neha Joshi > Amit Shah | | 3 | Farah Ali | Vikram Rao > Neha Joshi > Farah Ali | | 2 | Tanvi Kulkarni | Vikram Rao > Tanvi Kulkarni | +-------+----------------------+----------------------------------------+
What this code does
- Round 1 (the anchor) finds Vikram Rao, who has no manager. Round 2 finds the five people who report to him. Round 3 finds their reports. Round 4 finds nobody, so the recursion stops.
levelgrows by 1 each round, andchainadds one name each round. Sorting bychainprints the tree in order, andREPEATindents each level.CAST(name AS CHAR(200))makeschainlong enough for the longer text built later; without it a long chain fails with error 1406.
Example 6 · Category tree: full paths
WITH RECURSIVE tree AS ( SELECT category_id, CAST(name AS CHAR(200)) AS path, 0 AS depth FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, CONCAT(t.path, ' > ', c.name), t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.category_id)SELECT category_id, depth, pathFROM treeORDER BY path;+-------------+-------+---------------------------+ | category_id | depth | path | +-------------+-------+---------------------------+ | 5 | 0 | Books | | 1 | 0 | Electronics | | 4 | 1 | Electronics > Accessories | | 3 | 1 | Electronics > Laptops | | 2 | 1 | Electronics > Mobiles | | 6 | 0 | Fashion | | 7 | 0 | Toys | +-------------+-------+---------------------------+
What this code does
- The top-level categories (no parent) form the anchor. Each round adds the children of the categories found so far.
- Mobiles, Laptops and Accessories get the path
Electronics > .... If ShopLite later addsMobiles > Android, the same query shows a third level with no change. - This walks down the tree. To walk up from one category to the top, start the anchor at that category and join on
t.parent_id = c.category_id.
A recursive CTE has two parts joined by UNION ALL: the anchor (the starting rows) and the recursive member (finds the next rows from the rows found so far). MySQL repeats the recursive member until it returns no new rows.
Without recursion you would write one self join per level and still miss deeper levels. The counter below shows the mechanics: the anchor gives n = 1, each round adds 1, and WHERE n < 5 stops it. Without a stop condition MySQL gives up after 1000 rounds with error 3636, Recursive query aborted after 1001 iterations. The column types come from the anchor. If the anchor's text column is too short for the longer text built later, MySQL stops with error 1406, Data too long for column. Start a growing text column with CAST(name AS CHAR(200)) to give it room.
Predict the output
Predict the recursive CTE
The anchor gives n = 1, and each round adds n + 1 while n < 5.
Round 1 of 2
Next step
Check your understanding
12 quick questions