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
),
cte2 AS (
    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:

  1. Starting with a “base case” (anchor member) query that returns initial row(s)
  2. Running a “recursive case” that references the CTE itself
  3. Combining results with UNION ALL (typically)
  4. 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,
    manager_id INTEGER NULL
);

-- 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

  1. Iteration 0 (Base Case):
    • Returns VP Sales (id=2)
  2. Iteration 1:
    • Joins employees who have manager_id=2
    • Adds Sales Manager East and West (ids 4 and 5)
  3. Iteration 2:
    • Joins employees who have manager_id=4 or 5
    • Adds Sales Rep 1 and 2 (ids 6 and 7)
  4. 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, or EXCEPT
  • 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.