Common Table Expressions

Common Table Expressions, or CTEs, are a way to define temporary named result sets that exist only for the duration of a single query. Think of them as variables in programming — they let you break complex queries into readable, reusable pieces.

Basic CTE Syntax

A CTE starts with the WITH keyword:

WITH student_grades AS (
  SELECT
    s.id,
    s.first_name,
    s.last_name,
    AVG(CASE e.grade
      WHEN 'A' THEN 4 WHEN 'B' THEN 3
      WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0
    END) AS avg_gpa
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  GROUP BY s.id, s.first_name, s.last_name
)
SELECT first_name, last_name, avg_gpa
FROM student_grades
WHERE avg_gpa > 3.5
ORDER BY avg_gpa DESC;

The CTE defines a result set called student_grades. Then the main query uses it just like a regular table. Much cleaner than a subquery in FROM.

Multiple CTEs

You can chain multiple CTEs together:

WITH
active_students AS (
  SELECT id, first_name, last_name
  FROM students
  WHERE status = 'active'
),
course_stats AS (
  SELECT
    course_id,
    COUNT(*) AS enrollment_count,
    AVG(CASE grade
      WHEN 'A' THEN 4 WHEN 'B' THEN 3
      WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0
    END) AS avg_grade
  FROM enrollments
  GROUP BY course_id
)
SELECT
  a.first_name,
  a.last_name,
  c.name AS course_name,
  cs.enrollment_count
FROM active_students a
JOIN enrollments e ON a.id = e.student_id
JOIN courses c ON e.course_id = c.id
JOIN course_stats cs ON e.course_id = cs.course_id;

Each CTE builds on the previous ones. This makes complex queries much easier to understand and maintain.

Recursive CTEs

PostgreSQL supports recursive CTEs, which are perfect for hierarchical data like org charts, file systems, or tree structures:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id INTEGER REFERENCES employees(id)
);

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

  UNION ALL

  -- Recursive case: employees with managers
  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;

The recursive CTE starts with top-level managers and keeps adding their direct reports until there are no more. This walks the entire hierarchy in one query.

CTE vs Subquery

CTEs and subqueries do similar things, but CTEs have advantages:

• Readability — CTEs separate the logic into named blocks. Much easier to follow.
• Reusability — A CTE can be referenced multiple times in the same query.
• Recursion — Only CTEs support recursive queries.
• Maintenance — Easier to modify one CTE without affecting the rest of the query.

Materialized CTEs

By default, PostgreSQL may inline CTEs for optimization. If you want to force materialization (compute once, use multiple times):

WITH expensive_calculation AS MATERIALIZED (
  SELECT ... -- complex query here
)
SELECT * FROM expensive_calculation;

This is useful when the CTE is expensive to compute and referenced multiple times.