Common Table Expressions

A CTE — written with WITH — names a query so you can reference it later in the same statement, like a temporary view that only exists for one query.

A basic CTE

WITH high_value_orders AS (
  SELECT customer_id, amount FROM orders WHERE amount > 100
)
SELECT customers.full_name, high_value_orders.amount
FROM high_value_orders
JOIN customers ON customers.id = high_value_orders.customer_id;

 full_name  | amount 
------------+--------
 Ava Chen   | 150.00
 Sam Diaz   | 120.00
(2 rows)

The same result could be written as a subquery in FROM, but naming it with WITH up front makes a multi-step query easier to read top to bottom — and once defined, high_value_orders can be referenced more than once later in the same statement, which a subquery can't do without repeating it.

Multiple CTEs

WITH order_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
),
big_spenders AS (
  SELECT customer_id FROM order_totals WHERE total > 200
)
SELECT full_name FROM customers WHERE id IN (SELECT customer_id FROM big_spenders);

 full_name 
-----------
 Ava Chen
(1 row)

Chaining CTEs like this — big_spenders building on order_totals — breaks a complex question into named, readable steps, each one easy to test on its own by temporarily changing the final SELECT to just SELECT * FROM order_totals.

A recursive CTE

Adding RECURSIVE lets a CTE reference itself, which is how you walk a hierarchy — an org chart, a category tree, a comment thread — of unknown depth:

WITH RECURSIVE reports_to_ava AS (
  SELECT id, name, manager_id FROM employees WHERE name = 'Ava'
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN reports_to_ava r ON e.manager_id = r.id
)
SELECT name FROM reports_to_ava;

  name  
--------
 Ava
 Priya
 Noah
(3 rows)

The first SELECT (the "base case") finds Ava herself. The part after UNION ALL (the "recursive case") repeatedly joins back against the CTE's own growing result, pulling in anyone who reports to someone already found — Priya reports directly to Ava, Noah reports to Priya, and both get pulled in without knowing the hierarchy's depth in advance.