SQLite Common Table Expressions

A Common Table Expression (CTE) is a named temporary result set defined at the start of a query. It works like a named subquery but is written before the main SELECT, making complex queries much easier to read and maintain. CTEs also enable recursive queries — queries that reference themselves to traverse hierarchies and sequences.

CTE Syntax

WITH cte_name AS (
  SELECT ...   -- the CTE query
)
SELECT ...     -- main query that uses cte_name
FROM cte_name;

CTE vs Subquery — Readability

SUBQUERY (hard to read):
──────────────────────────────────────────────────────────────
SELECT name, salary
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments
  WHERE budget > (SELECT AVG(budget) FROM departments)
);

SAME LOGIC AS CTE (much clearer):
──────────────────────────────────────────────────────────────
WITH avg_budget AS (
  SELECT AVG(budget) AS avg FROM departments
),
rich_depts AS (
  SELECT id FROM departments, avg_budget
  WHERE budget > avg_budget.avg
)
SELECT name, salary
FROM employees
WHERE dept_id IN (SELECT id FROM rich_depts);

Simple CTE Example

-- Find products priced above the category average
WITH cat_avg AS (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
)
SELECT p.name, p.category, p.price, ca.avg_price
FROM products p
JOIN cat_avg ca ON p.category = ca.category
WHERE p.price > ca.avg_price
ORDER BY p.category, p.price DESC;

name         │ category    │ price  │ avg_price
─────────────┼─────────────┼────────┼──────────
Laptop Bag   │ Bags        │ 34.99  │ 24.99
Backpack     │ Bags        │ 24.99  │ 24.99 ← tie
Phone Stand  │ Accessories │ 15.00  │ 11.75

Multiple CTEs in One Query

WITH
  total_sales AS (
    SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep
  ),
  avg_sales AS (
    SELECT AVG(total) AS avg FROM total_sales
  )
SELECT ts.rep, ts.total,
       ROUND(ts.total - av.avg, 2) AS vs_average
FROM total_sales ts, avg_sales av
ORDER BY ts.total DESC;

rep   │ total │ vs_average
──────┼───────┼───────────
Alice │ 2600  │ +616.67
Bob   │ 2100  │ +116.67
Carol │ 1850  │ -133.33
Dan   │ 1000  │ -983.33

Recursive CTE

A recursive CTE references itself. It starts from a base case and repeatedly applies a recursive step until no new rows are generated. Recursive CTEs are the standard way to traverse trees and hierarchies in SQLite.

-- Generate numbers 1 to 10
WITH RECURSIVE counter(n) AS (
  SELECT 1                 -- base case: start at 1
  UNION ALL
  SELECT n + 1             -- recursive step: add 1 each time
  FROM counter
  WHERE n < 10             -- stop condition
)
SELECT n FROM counter;

n
──
1
2
3
...
10

Recursive CTE: Org Chart Traversal

employees
──────────────────────────────────────
id │ name   │ manager_id
───┼────────┼───────────
1  │ CEO    │ NULL
2  │ Alice  │ 1
3  │ Bob    │ 1
4  │ Carol  │ 2
5  │ Dan    │ 2

-- Find all employees under Alice (id=2), any depth
WITH RECURSIVE subordinates(id, name, level) AS (
  SELECT id, name, 0 AS level FROM employees WHERE id = 2   -- start: Alice
  UNION ALL
  SELECT e.id, e.name, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id     -- one level down
)
SELECT level, name FROM subordinates ORDER BY level, name;

level │ name
──────┼───────
0     │ Alice    ← Alice herself (level 0)
1     │ Carol    ← reports to Alice
1     │ Dan      ← reports to Alice

Recursive CTE: Date Series

-- Generate every date in July 2024
WITH RECURSIVE dates(d) AS (
  SELECT date('2024-07-01')
  UNION ALL
  SELECT date(d, '+1 day')
  FROM dates
  WHERE d < date('2024-07-31')
)
SELECT d FROM dates;

d
──────────
2024-07-01
2024-07-02
...
2024-07-31

Key Takeaways

• CTEs use WITH name AS (SELECT ...) and are referenced like tables in the main query
• Multiple CTEs separate by commas inside one WITH block
• CTEs improve readability over deeply nested subqueries
• Recursive CTEs use WITH RECURSIVE and require a base case, a recursive step, and a stop condition
• Common recursive CTE uses: number series, date ranges, hierarchy traversal