CTEs (Common Table Expressions)Intermediate
1Question 1 of 1
sqlWhat is a Common Table Expression (CTE) and how does a recursive CTE work?
Explanation & Concept
A Common Table Expression (CTE) is a temporary, named result set defined using the `WITH` clause that exists only during the execution of a single query. It improves readability and maintainability over nested subqueries.
A Recursive CTE references itself and is ideal for traversing hierarchical data like organizational charts, parent-child trees, and bill of materials.
Code Example
sql
-- Recursive CTE to traverse an employee hierarchy
WITH RECURSIVE OrgChart AS (
-- Anchor member: Start with top CEO (manager_id is NULL)
SELECT employee_id, name, manager_id, 1 AS level
FROM Employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: Find direct reports
SELECT e.employee_id, e.name, e.manager_id, o.level + 1
FROM Employees e
INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT level, name FROM OrgChart ORDER BY level, name;How It Works & Key Takeaways
The anchor query finds the company CEO. The recursive member joins against the previous output to find reports at level 2, then level 3, until no more subordinates are found.
Mock Test · 1 Questions · Optional
Ready for Constraints (PK/FK/Unique)?
Take a quick test to evaluate yourself, or move straight to the next module.
Advanced1 questions