34 34. Common Table Expressions (CTE)
34.1 SQL Common Table Expressions (CTE)
WARNING - the following was generated by AI (claude.ai).
I still need to double check it for accuracy.
You can also see the following to learn about this important feature of modern SQL:
https://learnsql.com/blog/what-is-common-table-expression/
Common Table Expressions (CTEs) provide a way to create named temporary result sets that exist only within the scope of a single SQL statement. CTEs make complex queries more readable by breaking them into logical, named components.
CTEs are very similar to using a subquery in the FROM clause but are much more readable.
34.2 Basic Syntax
WITH cte_name AS (
SELECT column1, column2
FROM table
WHERE condition
)SELECT *
FROM cte_name;
CTEs are defined using the WITH
keyword, followed by a name and the query that defines the temporary result set. You can then reference this named result set in your main query.
34.3 Multiple CTEs
You can define multiple CTEs in a single query:
WITH cte1 AS (
SELECT * FROM table1 WHERE condition1
),AS (
cte2 SELECT * FROM table2 WHERE condition2
)SELECT *
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;
34.4 Recursive CTEs
Recursive CTEs are a powerful feature for handling hierarchical or graph-structured data that would be difficult to query using standard SQL. They’re especially useful for:
- Organizational hierarchies (employee → manager relationships)
- File/folder structures
- Network paths
- Family trees
- Any data with parent-child relationships
34.4.1 How Recursive CTEs Work
A recursive CTE works by:
- Starting with a “base case” (anchor member) query that returns initial row(s)
- Running a “recursive case” that references the CTE itself
- Combining results with UNION ALL (typically)
- Repeating steps 2-3 until no new rows are returned
Think of it like a loop that builds the result set iteratively.
34.4.2 Basic Syntax
WITH RECURSIVE cte_name AS (
-- Base case: initial query that doesn't reference the CTE
SELECT columns FROM table WHERE condition
UNION ALL
-- Recursive case: references the CTE itself to build next iteration
SELECT t.columns
FROM table t
JOIN cte_name c ON t.parent_id = c.id
-- Optional: WHERE clause to prevent infinite recursion
)SELECT * FROM cte_name;
34.4.3 Example: Employee Hierarchy
Consider a table of employees where each employee has a manager (who is also an employee):
-- Table structure
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,INTEGER NULL
manager_id
);
-- Sample data: CEO has NULL manager_id
INSERT INTO employees VALUES
1, 'CEO', NULL),
(2, 'VP Sales', 1),
(3, 'VP Marketing', 1),
(4, 'Sales Manager East', 2),
(5, 'Sales Manager West', 2),
(6, 'Sales Rep 1', 4),
(7, 'Sales Rep 2', 4);
(
-- Recursive CTE to find all employees under VP Sales (id=2)
WITH RECURSIVE org_chart AS (
-- Base case: Start with VP Sales
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE id = 2
UNION ALL
-- Recursive case: Find all direct reports of each employee in org_chart
SELECT e.id, e.name, e.manager_id, oc.level + 1 AS level
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)-- Final query using the CTE
SELECT id, name, level,
SUBSTR(' ', 1, level*4) || name AS hierarchical_view
FROM org_chart
ORDER BY level, name;
This query returns:
id | name | level | hierarchical_view
------------------------------------------------------------------------------------
2 | VP Sales | 0 | VP Sales
4 | Sales Manager East | 1 | Sales Manager East
5 | Sales Manager West | 1 | Sales Manager West
6 | Sales Rep 1 | 2 | Sales Rep 1
7 | Sales Rep 2 | 2 | Sales Rep 2
34.4.4 How the Recursion Unfolds
- Iteration 0 (Base Case):
- Returns VP Sales (id=2)
- Iteration 1:
- Joins employees who have manager_id=2
- Adds Sales Manager East and West (ids 4 and 5)
- Iteration 2:
- Joins employees who have manager_id=4 or 5
- Adds Sales Rep 1 and 2 (ids 6 and 7)
- Iteration 3:
- No employees have manager_id=6 or 7
- No new rows, recursion stops
34.4.5 Preventing Infinite Recursion
If your data has cycles (e.g., A manages B, B manages C, C manages A), you need safeguards:
WITH RECURSIVE org_chart AS (
-- Base case
SELECT id, name, manager_id, 0 AS level, id AS path
FROM employees
WHERE id = 2
UNION ALL
-- Recursive case with cycle prevention
SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ',' || e.id
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
WHERE oc.level < 10 -- Depth limit
AND instr(',' || oc.path || ',', ',' || e.id || ',') = 0 -- Path check for cycles
)SELECT * FROM org_chart;
34.4.6 SQLite Implementation Notes
SQLite’s implementation of recursive CTEs follows the ANSI SQL standard closely with some specific behaviors:
- The
RECURSIVE
keyword is required (consistent with ANSI standard) - The recursive part must connect to the non-recursive part with either
UNION
,UNION ALL
,INTERSECT
, orEXCEPT
- SQLite imposes a default recursion limit of 1000, which can be modified using the
PRAGMA recursive_triggers
setting - Each iteration must return at least one row, or recursion stops
CTEs can significantly improve query readability and maintenance, especially for complex queries involving multiple steps or self-joins.