Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with WITH, that you can reference like a regular table later in the same query. Think of it as naming a subquery so the rest of the SQL reads top-to-bottom instead of nesting deeply.

Basic Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Example

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 60000
)
SELECT department, COUNT(*)
FROM high_earners
GROUP BY department;

Multiple CTEs in One Query

WITH dept_totals AS (
  SELECT department_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department_id
),
top_departments AS (
  SELECT * FROM dept_totals WHERE total_salary > 200000
)
SELECT d.name, t.total_salary
FROM top_departments t
JOIN departments d ON d.id = t.department_id;

Why Use a CTE Instead of a Subquery?

• Readability — a long query becomes a sequence of named steps instead of nested parentheses.
• Reuse — reference the same CTE multiple times later in the query without repeating the logic.
• Functionally, a simple CTE and an equivalent subquery usually behave the same in modern PostgreSQL.

Recursive CTEs — Walking Hierarchical Data

A recursive CTE references itself, which lets you walk a tree structure like an org chart or category hierarchy.

WITH RECURSIVE org_chart AS (
  -- base case: the top-level manager (no manager_id)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case: everyone reporting to someone already in org_chart
  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;

This walks down the reporting chain level by level until no more matches are found.

Common Errors & Troubleshooting

• ERROR: relation "high_earners" does not exist outside the WITH query — a CTE only exists for the duration of the single statement it's attached to; you can't reference it in a later, separate query.
• Recursive CTE running forever (or hitting a depth limit) — a missing or incorrect termination condition in the recursive branch causes infinite recursion; PostgreSQL will eventually error out, but double-check the base case actually stops matching once the recursion should end.
• Multiple CTEs referencing each other in the wrong order — a CTE can only reference CTEs defined before it in the same WITH clause (or itself, with RECURSIVE) — not ones defined later.

Key Takeaways

• WITH names a result set so later parts of the query can treat it like a regular table.
• CTEs are primarily a readability tool — they let you break a complex query into labeled steps.
• WITH RECURSIVE is the standard SQL pattern for traversing hierarchical/tree-shaped data.