CTEs Common Table Expressions (WITH)

Write readable, reusable query fragments using the WITH clause.

Overview

A Common Table Expression (CTE), introduced with the WITH keyword, defines a named temporary result set that can be referenced multiple times within the main query. CTEs improve readability for complex queries by breaking them into named steps. In PostgreSQL 12+, CTEs are inlined by default (treated like subqueries). Use WITH name AS MATERIALIZED (...) to force materialisation (compute once, use many times). CTEs also enable recursive queries (WITH RECURSIVE) for hierarchical data traversal.

Key Points

•WITH name AS (SELECT ...) SELECT ... FROM name
•Can define multiple CTEs: WITH a AS (...), b AS (...)
•CTEs can reference each other sequentially
•PostgreSQL 12+ inlines CTEs by default (like subqueries)
•WITH name AS MATERIALIZED (...) forces one-time computation
•WITH RECURSIVE for hierarchical/recursive queries

Examples

Using CTEs for readable multi-step queries

WITH
  active_customers AS (
    SELECT id, name FROM customers WHERE is_active = TRUE
  ),
  customer_totals AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders GROUP BY customer_id
  )
SELECT c.name, ct.total_spent
FROM active_customers c
JOIN customer_totals ct ON ct.customer_id = c.id
WHERE ct.total_spent > 500
ORDER BY ct.total_spent DESC;

CTE with DML (DELETE, INSERT, UPDATE)

-- Delete old rows and return count
WITH deleted AS (
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days'
  RETURNING id
)
SELECT COUNT(*) AS deleted_count FROM deleted;

CTEs Common Table Expressions (WITH)

Overview

Key Points

Examples

Exercises

Knowledge Check

What keyword introduces a CTE?