CTEs and Subqueries

A Common Table Expression (CTE), introduced with the WITH keyword, defines a named temporary result set that can be referenced multiple times in the main query. CTEs improve readability by breaking complex queries into named, understandable steps. Recursive CTEs (WITH RECURSIVE) can process hierarchical data such as trees and graphs by repeatedly joining a base case with a recursive case until a termination condition is met. Subqueries in the FROM clause (derived tables), the SELECT list (scalar subqueries), and the WHERE clause (correlated subqueries) are all supported. The EXISTS operator tests whether a subquery returns any rows and is often more efficient than IN for large subquery result sets.

Example

-- Basic CTE for readability
WITH high_value_customers AS (
  SELECT user_id, SUM(total) AS lifetime_value
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
  HAVING SUM(total) > 1000
),
customer_details AS (
  SELECT u.id, u.name, u.email, h.lifetime_value
  FROM users u
  JOIN high_value_customers h ON h.user_id = u.id
)
SELECT * FROM customer_details ORDER BY lifetime_value DESC;

-- RECURSIVE CTE — traverse a category tree
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 AS depth, name::TEXT AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id,
         ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, depth, path FROM category_tree ORDER BY path;

-- EXISTS vs IN
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 500
);

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned