What is a CTE and how does it differ from a subquery?

A CTE (WITH clause) names a temporary result set for readability and reuse; a subquery is anonymous and embedded inline.

SQLPostgreSQL

Overview

Both CTEs and subqueries let you build queries from intermediate result sets. A CTE (Common Table Expression) uses the `WITH` keyword to give the result a name, making complex queries far easier to read and reason about. A subquery is inlined directly inside the main query.

Key Points

•CTE syntax: `WITH name AS (SELECT ...) SELECT ... FROM name`.
•CTEs are defined once and can be referenced multiple times in the same query.
•Subqueries are anonymous and must be repeated if referenced more than once.
•Recursive CTEs (`WITH RECURSIVE`) can traverse tree or graph structures subqueries cannot.
•In PostgreSQL, most CTEs are not materialised by default (the planner optimises through them), but you can force materialisation with `WITH name AS MATERIALIZED (SELECT ...)`.

Examples

Subquery vs CTE for the same logic

sql

-- Subquery (harder to read)
SELECT *
FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
) AS paid_totals
WHERE total > 1000;

-- CTE (same logic, much clearer)
WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT *
FROM paid_totals
WHERE total > 1000;

Recursive CTE: traverse an employee hierarchy

sql

WITH RECURSIVE org_chart AS (
  -- Base case: the CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM   employees
  WHERE  manager_id IS NULL

  UNION ALL

  -- Recursive case: find 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;

Pro Tip

Use CTEs for any query with more than one logical step. They act like well-named functions each CTE should do one thing and have a name that describes it. Your future self (and your colleagues) will thank you.