SQL CTEs (Common Table Expressions) — WITH Clause Guide

CTEs (WITH clauses) give readable names to subqueries and enable recursive queries. They improve readability dramatically and enable hierarchical data traversal that would be impossible with a plain subquery.

Use the SQL Formatter to format CTE queries with proper indentation.

Basic CTE syntax

WITH cte_name AS (
  SELECT column1, column2
  FROM table
  WHERE condition
)
SELECT *
FROM cte_name;

Multiple CTEs

WITH 
high_value_customers AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 10000
),
recent_orders AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '90 days'
  GROUP BY customer_id
)
SELECT 
  c.name,
  hvc.lifetime_value,
  COALESCE(ro.order_count, 0) AS orders_last_90_days
FROM high_value_customers hvc
JOIN customers c ON c.id = hvc.customer_id
LEFT JOIN recent_orders ro ON ro.customer_id = hvc.customer_id
ORDER BY hvc.lifetime_value DESC;

CTE vs subquery — readability comparison

-- Subquery approach (hard to read):
SELECT c.name
FROM customers c
WHERE c.id IN (
  SELECT customer_id 
  FROM orders
  WHERE total > (
    SELECT AVG(total) * 1.5
    FROM orders
  )
);

-- CTE approach (readable, self-documenting):
WITH avg_order AS (
  SELECT AVG(total) * 1.5 AS threshold
  FROM orders
),
high_value_orders AS (
  SELECT customer_id
  FROM orders, avg_order
  WHERE total > threshold
)
SELECT c.name
FROM customers c
JOIN high_value_orders h ON h.customer_id = c.id;

Recursive CTE — hierarchical data

Recursive CTEs traverse tree structures like org charts, categories, or file systems:

-- employees table: id, name, manager_id
WITH RECURSIVE org_chart AS (
  -- Anchor: start with top-level employees (no manager)
  SELECT id, name, manager_id, 0 AS level, ARRAY[id] AS path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: add each employee who reports to current level
  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
)
SELECT 
  REPEAT('  ', level) || name AS employee,
  level AS depth
FROM org_chart
ORDER BY path;

-- Result:
-- CEO                    (level 0)
--   VP Engineering       (level 1)
--     Senior Engineer    (level 2)
--     Junior Engineer    (level 2)
--   VP Marketing         (level 1)

Recursive CTE for category paths

WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id, ARRAY[name] AS path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, cp.path || c.name
  FROM categories c
  JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, array_to_string(path, ' > ') AS full_path
FROM category_path
ORDER BY path;

-- Results like:
-- 'Electronics'
-- 'Electronics > Phones'
-- 'Electronics > Phones > Android'
-- 'Electronics > Computers'

Recursive number sequence

-- Generate a sequence of dates:
WITH RECURSIVE dates AS (
  SELECT '2026-01-01'::date AS dt
  UNION ALL
  SELECT dt + 1 FROM dates WHERE dt < '2026-01-31'
)
SELECT dt AS date FROM dates;

-- Generate Fibonacci sequence:
WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE b < 1000
)
SELECT a AS fibonacci FROM fib;

CTE vs temp table vs subquery

-- Temp table: persists in session, can index it
CREATE TEMP TABLE temp_data AS
SELECT customer_id, SUM(total) AS total
FROM orders GROUP BY customer_id;

CREATE INDEX ON temp_data(customer_id);  -- Can add index!

-- CTE: not persisted, no indexing
-- Use temp table when the CTE result is large and queried multiple times

-- Subquery: inlined, optimizer has more flexibility
-- Use subquery for simple, one-time filters

-- CTE: best for readability, multiple references in same query,
-- recursive queries

Related tools

• SQL Formatter — format SQL queries with proper indentation
• SQL Subqueries Guide — IN, EXISTS, correlated subqueries
• SQL Window Functions — ROW_NUMBER, RANK, LAG/LEAD