← SQL roadmap

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “CTEs & Recursive Queries” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.