Q14: What is a Common Table Expression (CTE) and when should you use it?
A Common Table Expression (CTE) is a temporary, named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement using the WITH clause.
CTEs improve code readability compared to deeply nested subqueries and support Recursive CTEs, which enable hierarchical tree traversals (e.g., organizational charts or bill-of-materials structures).
Recursive CTE for Organizational Hierarchy Retrieval
WITH RECURSIVE OrgChart AS (
-- Anchor Member: Top level CEO
SELECT employee_id, manager_id, first_name, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive Member: Join subordinates
SELECT e.employee_id, e.manager_id, e.first_name, o.depth + 1
FROM employees e
INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart ORDER BY depth;💡 The anchor query establishes the tree root node; the recursive part iteratively joins child records until no further matches are found.
Key Takeaways
- CTEs split complex queries into clean, maintainable logic blocks.
- Recursive CTEs allow efficient processing of graph and tree structures natively in SQL.