SQL Common Table Expressions (CTEs) with WITH Clause

SQL Common Table Expressions (CTEs) with WITH Clause

A Common Table Expression (CTE) is a named temporary result set defined using the WITH keyword. CTEs improve readability by breaking complex queries into logical, named steps.

Basic Syntax

WITH active_customers AS (
  SELECT id, name, email
  FROM customers
  WHERE status = 'active'
)
SELECT ac.name, COUNT(o.id) AS order_count
FROM active_customers ac
JOIN orders o ON ac.id = o.customer_id
GROUP BY ac.name;

Multiple CTEs

Chain multiple CTEs separated by commas:

WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
  FROM sales
  GROUP BY DATE_TRUNC('month', sale_date)
),
avg_sales AS (
  SELECT AVG(total) AS avg_monthly FROM monthly_sales
)
SELECT ms.month, ms.total,
  CASE WHEN ms.total > a.avg_monthly THEN 'Above' ELSE 'Below' END AS performance
FROM monthly_sales ms
CROSS JOIN avg_sales a;

Recursive CTEs

Recursive CTEs are invaluable for hierarchical or tree-structured data:

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  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, name;

CTE vs Subquery vs Temp Table

• CTE: Best for readability and single-query scope. Optimized inline by most databases
• Subquery: Suitable for simple, one-off nested logic
• Temp Table: Better when the intermediate result is used multiple times across different queries or needs indexing

Performance Notes

• In PostgreSQL 12+, CTEs can be inlined by the optimizer (no longer always a materialization fence)
• In MySQL 8.0+, non-recursive CTEs are merged into the outer query
• Recursive CTEs require a termination condition to avoid infinite loops
• Add a depth limit as a safety measure in recursive CTEs

CTEs are the modern, readable approach to writing complex SQL and are supported by all major database systems.