A Common Table Expression (CTE) gives a subquery a name with the WITH clause, so complex queries read top to bottom like steps. A recursive CTE refers to itself, which is how SQL walks hierarchies such as manager chains and generates sequences such as every date in a month.
Common Table Expressions (CTEs)
A CTE is like giving a temporary name to a complex query.
Instead of writing a giant messy query, you break it into named steps.
First step: "Get all orders from January" → call it jan_orders.
Second step: "Now sum up jan_orders by customer" → much cleaner!
CTEs are like temporary tables that exist only during your query.
-- Basic CTE syntax
WITH dept_stats AS (
SELECT dept, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
)
SELECT * FROM dept_stats WHERE avg_salary > 80000;
-- Multiple CTEs chained together
WITH
high_earners AS (
SELECT emp_id, name, salary FROM employees WHERE salary > 100000
),
their_managers AS (
SELECT DISTINCT m.name AS manager
FROM high_earners h
JOIN employees m ON h.manager_id = m.emp_id
)
SELECT * FROM their_managers;
-- CTE vs Subquery — same result, different readability
-- Subquery version (hard to read)
SELECT name FROM (SELECT name, salary FROM employees WHERE salary > 100000) t;
-- CTE version (clean and readable)
WITH top_earners AS (SELECT name, salary FROM employees WHERE salary > 100000)
SELECT name FROM top_earners;Recursive CTEs
-- Traverse employee hierarchy (manager → team members → sub-team)
WITH RECURSIVE emp_hierarchy AS (
-- Base case: top-level manager (no manager above)
SELECT emp_id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: employees under each manager
SELECT e.emp_id, e.name, e.manager_id, h.level + 1
FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT REPEAT(' ', level) || name AS hierarchy, level
FROM emp_hierarchy
ORDER BY level, name;
-- Generate number series 1 to 10
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;CTE vs Subquery vs Temporary Table
| CTE | Subquery | Temporary table | |
|---|---|---|---|
| Lives for | One statement | One statement | The session (until dropped) |
| Can be referenced more than once | Yes, by name | No, must be repeated | Yes |
| Can be recursive | Yes | No | No |
| Can be indexed | No | No | Yes |
| Best for | Readable multi-step queries, hierarchies | Short one-off filters | Large intermediate results reused by several queries |
In testing, CTEs make data-validation queries easier to review: one CTE per check (expected rows, actual rows, differences) reads like the test steps.
FAQs
What is the difference between a CTE and a subquery?
Both define a derived result, but a CTE is named and declared before the main query, can be referenced several times and can be recursive. A subquery is written inline where it is used.
Does MySQL support CTEs?
Yes, from MySQL 8.0, including recursive CTEs with WITH RECURSIVE. PostgreSQL, SQL Server and Oracle support them too.
How do you stop a recursive CTE from running forever?
The recursive member needs a condition that eventually returns no rows, for example WHERE level < 10 or WHERE dt < end_date. Databases also enforce a maximum recursion depth.
