SQL Common Table Expressions (CTEs) — WITH Clause

Common Table Expressions (CTEs)

A CTE creates a named, temporary result set that you can reference within a single SQL statement. CTEs make complex queries more readable and modular.

Basic CTE

WITH high_earners AS (
  SELECT name, salary, department_id
  FROM employees
  WHERE salary > 80000
)
SELECT h.name, h.salary, d.name AS department
FROM high_earners h
JOIN departments d ON h.department_id = d.id;

Multiple CTEs

WITH dept_stats AS (
  SELECT department_id,
    AVG(salary) AS avg_salary,
    COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
),
top_depts AS (
  SELECT * FROM dept_stats WHERE avg_salary > 70000
)
SELECT d.name, td.avg_salary, td.headcount
FROM top_depts td
JOIN departments d ON td.department_id = d.id;

Recursive CTE

Recursive CTEs have two parts: an anchor (base case) and a recursive member.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each person's direct reports
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

Use Cases for Recursive CTEs

• Organization charts / management hierarchies
• Bill of materials (parts that contain sub-parts)
• Category trees / nested menus
• Graph traversal

Database Support

Database	Basic CTE	Recursive CTE
PostgreSQL	Yes (8.4+)	Yes (8.4+)
MySQL	Yes (8.0+)	Yes (8.0+)
SQLite	Yes (3.8.3+)	Yes (3.8.3+)
SQL Server	Yes (2005+)	Yes (2005+)

CTE vs Subquery

CTEs are generally preferred over deeply nested subqueries because they are named, can be referenced multiple times, and read top-to-bottom like procedural code. However, some databases may not optimize CTEs as aggressively as inline subqueries.