Topic 23 of 52 · Advanced
CTEs & Recursive Queries
Readable step-by-step queries and hierarchy walks with WITH.
Concept
A Common Table Expression (WITH name AS (...)) names a subquery so you can build a query in readable steps and reuse results.
WITH RECURSIVE has an anchor query and a recursive part joined by UNION ALL. It's used for org charts, category trees and generating number/date series.
Always include a stop condition so the recursion terminates.
Code (SQL · MySQL 8)
WITH monthly AS (
SELECT DATE_FORMAT(order_date,'%Y-%m') m, SUM(amount) revenue
FROM orders GROUP BY m
)
SELECT m, revenue FROM monthly WHERE revenue > 50000;
-- Org chart depth
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, c.lvl + 1
FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY lvl;Remember
- CTEs improve readability; they're not always faster.
- Recursive CTE = anchor UNION ALL recursive step.
- Use recursive CTEs to generate missing dates.
Interview questions
CTE vs subquery vs temp table?
A CTE is a named subquery valid for one statement. A temp table is physically stored for the session and can be indexed and reused across statements.
How do you list all reports under a manager?
A recursive CTE anchored on the manager, then joining employees whose manager_id is in the previous level.
Lesson test
Take an 8-question test on “CTEs & Recursive Queries” — concepts, output prediction, bug spotting and interview questions.