How to use SQLite CTE (Common Table Expressions)?

SQLite's CTE (Common Table Expressions) provides more flexible query methods:

1. CTE Concept

   • CTE is a temporary named result set that exists within the execution scope of a single statement
   • Makes complex queries more readable and maintainable
   • Can be used recursively to implement hierarchical queries
   • SQLite 3.8.3+ supports CTE

2. CTE Syntax

   sql
   WITH cte_name AS (
       cte_query
   )
   SELECT * FROM cte_name;

3. Basic CTE Examples

   sql
   -- Simple CTE
   WITH high_salary_employees AS (
       SELECT * FROM employees WHERE salary > 50000
   )
   SELECT * FROM high_salary_employees ORDER BY salary DESC;
   
   -- Multiple CTEs
   WITH 
       dept_avg AS (
           SELECT department, AVG(salary) as avg_salary
           FROM employees
           GROUP BY department
       ),
       high_avg_depts AS (
           SELECT * FROM dept_avg WHERE avg_salary > 60000
       )
   SELECT * FROM high_avg_depts;

4. Recursive CTE

   sql
   -- Recursive CTE syntax
   WITH RECURSIVE cte_name AS (
       -- Initial query (anchor member)
       initial_query
       UNION ALL
       -- Recursive query (recursive member)
       recursive_query
   )
   SELECT * FROM cte_name;
   
   -- Example: Generate number sequence
   WITH RECURSIVE numbers(n) AS (
       SELECT 1
       UNION ALL
       SELECT n + 1 FROM numbers WHERE n < 10
   )
   SELECT * FROM numbers;
   
   -- Example: Hierarchical query (organization structure)
   WITH RECURSIVE org_chart(id, name, manager_id, level) AS (
       SELECT id, name, manager_id, 0
       FROM employees
       WHERE manager_id IS NULL
       UNION ALL
       SELECT e.id, e.name, e.manager_id, oc.level + 1
       FROM employees e
       JOIN org_chart oc ON e.manager_id = oc.id
   )
   SELECT * FROM org_chart ORDER BY level, id;

5. Advantages of CTE

   • Improve Readability: Break complex queries into logical parts
   • Code Reuse: Reference CTE multiple times in the same query
   • Performance Optimization: More efficient than subqueries in some cases
   • Recursive Queries: Support hierarchical structures and graph traversal

6. CTE vs Subquery Comparison

   sql
   -- Using subquery
   SELECT * FROM (
       SELECT department, AVG(salary) as avg_salary
       FROM employees
       GROUP BY department
   ) WHERE avg_salary > 60000;
   
   -- Using CTE (clearer)
   WITH dept_avg AS (
       SELECT department, AVG(salary) as avg_salary
       FROM employees
       GROUP BY department
   )
   SELECT * FROM dept_avg WHERE avg_salary > 60000;

7. Practical Application Scenarios

   sql
   -- Scenario 1: Calculate moving average
   WITH monthly_sales AS (
       SELECT 
           strftime('%Y-%m', order_date) as month,
           SUM(amount) as total
       FROM orders
       GROUP BY month
   )
   SELECT 
       month,
       total,
       AVG(total) OVER (
           ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) as moving_avg
   FROM monthly_sales;
   
   -- Scenario 2: Find duplicate records
   WITH duplicate_emails AS (
       SELECT email, COUNT(*) as cnt
       FROM users
       GROUP BY email
       HAVING cnt > 1
   )
   SELECT u.* FROM users u
   JOIN duplicate_emails d ON u.email = d.email
   ORDER BY u.email;
   
   -- Scenario 3: Path finding (graph traversal)
   WITH RECURSIVE path(start_node, end_node, path, depth) AS (
       SELECT id, id, CAST(id AS TEXT), 0
       FROM nodes
       WHERE id = 1
       UNION ALL
       SELECT p.start_node, e.to_node, p.path || '->' || e.to_node, p.depth + 1
       FROM path p
       JOIN edges e ON p.end_node = e.from_node
       WHERE p.depth < 5 AND INSTR(p.path, e.to_node) = 0
   )
   SELECT * FROM path WHERE end_node = 10;

8. CTE Limitations

   • CTE is only visible within the statement that defines it
   • Cannot mix aggregate functions and window functions in CTE
   • Recursive CTE needs explicit termination conditions

9. Performance Considerations

   • CTE is usually treated as an inline view by the optimizer
   • Recursive CTE can consume significant resources
   • Complex CTE may require manual optimization
   • Consider using temporary tables for large datasets

CTE is an important tool for writing complex queries in SQLite, especially recursive queries.