Intermediate LevelSQL & MySQL
What are Common Table Expressions (CTEs) and Recursive CTEs?
❓ Interview Prompt: Explain Common Table Expressions (`WITH`), readability advantages over nested subqueries, and Recursive CTEs for hierarchy traversal.
A Common Table Expression (CTE) defines a temporary named result set using the 'WITH' clause, improving query readability compared to deeply nested subqueries:
Recursive CTEs:
A Recursive CTE references its own name, enabling tree and graph traversal (e.g. organizational charts, bill of materials, category trees). Consists of an Anchor Member unioned with a Recursive Member.
Code Implementation Example
sql
-- Recursive CTE traversing organizational employee hierarchy
WITH RECURSIVE EmpHierarchy AS (
-- Anchor Member: Top-level executives
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive Member: Join subordinates to parent hierarchy
SELECT e.id, e.name, e.manager_id, h.depth + 1
FROM employees e
JOIN EmpHierarchy h ON e.manager_id = h.id
)
SELECT * FROM EmpHierarchy ORDER BY depth;💡Traversing organizational hierarchies recursively using a Recursive CTE.
🎯 Interview Key Takeaway
Use CTEs to simplify complex multi-step queries, and Recursive CTEs to query tree hierarchies.
#SQL#CTE#WITH#Recursive CTE#Intermediate
Back to all SQL & MySQL Questions→