Subqueries & CTEs

Subqueries and Common Table Expressions (CTEs) let you break complex queries into readable, reusable pieces. PostgreSQL adds important extensions: recursive CTEs, materialized hints, and data-modifying CTEs inside WITH.

Subqueries

A subquery is a query nested inside another query.

Scalar Subquery

Returns a single value used in SELECT or WHERE:

-- Compare each employee's salary to the company average
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
ORDER BY diff_from_avg DESC;

Subquery in WHERE with IN

-- Employees in departments with budget over $400k
SELECT name, dept_id
FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE budget > 400000
);

Subquery with EXISTS

EXISTS is often faster than IN for large datasets because it short-circuits on the first match:

-- Departments that have at least one employee
SELECT d.name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

-- Departments with NO employees (NOT EXISTS)
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

Derived Table (Subquery in FROM)

A subquery in FROM acts like a temporary table:

-- Average salary per department, filtered to those above company average
SELECT dept_stats.dept_name, dept_stats.avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE dept_stats.avg_salary > (SELECT AVG(salary) FROM employees);

CTEs — Common Table Expressions

CTEs use the WITH keyword to define named result sets that can be referenced later in the query. They are more readable than deeply nested subqueries.

WITH dept_averages AS (
    SELECT
        dept_id,
        AVG(salary) AS avg_salary,
        COUNT(*)    AS headcount
    FROM employees
    GROUP BY dept_id
)
SELECT
    e.name,
    e.salary,
    da.avg_salary,
    e.salary - da.avg_salary AS diff
FROM employees e
JOIN dept_averages da ON e.dept_id = da.dept_id
ORDER BY diff DESC;

Multiple CTEs

Chain multiple CTEs using commas:

WITH
high_earners AS (
    SELECT * FROM employees WHERE salary > 80000
),
their_departments AS (
    SELECT DISTINCT d.*
    FROM departments d
    JOIN high_earners he ON he.dept_id = d.id
)
SELECT td.name AS department, COUNT(he.id) AS high_earner_count
FROM their_departments td
JOIN high_earners he ON he.dept_id = td.id
GROUP BY td.name;

Recursive CTEs

Recursive CTEs are one of PostgreSQL's most powerful features for hierarchical and graph data.

Structure of a Recursive CTE

WITH RECURSIVE cte_name AS (
    -- Base case (non-recursive part — runs once)
    SELECT ...

    UNION ALL

    -- Recursive case (references cte_name itself — runs repeatedly until empty)
    SELECT ...
    FROM cte_name
    JOIN other_table ON ...
)
SELECT * FROM cte_name;

Example 1: Employee Hierarchy

-- Table with manager_id self-reference
-- Alice(1) → no manager
-- Bob(2)   → reports to Alice(1)
-- Carol(3) → reports to Alice(1)
-- Dave(4)  → reports to Bob(2)

WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 0 AS depth, name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: add direct reports
    SELECT e.id, e.name, e.manager_id,
           oc.depth + 1,
           oc.path || ' -> ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
    REPEAT('  ', depth) || name AS org_tree,
    depth,
    path
FROM org_chart
ORDER BY path;

Example 2: Generating a Series of Dates

-- Generate every Monday in Q1 2024
WITH RECURSIVE mondays AS (
    SELECT '2024-01-01'::DATE AS d
    UNION ALL
    SELECT d + 7
    FROM mondays
    WHERE d < '2024-03-31'
)
SELECT d AS monday FROM mondays WHERE EXTRACT(DOW FROM d) = 1;

Example 3: Graph Traversal — Connected Nodes

CREATE TABLE graph_edges (from_node INT, to_node INT);
INSERT INTO graph_edges VALUES (1,2),(2,3),(3,4),(1,5),(5,6);

-- Find all nodes reachable from node 1
WITH RECURSIVE reachable AS (
    SELECT to_node AS node FROM graph_edges WHERE from_node = 1
    UNION
    SELECT ge.to_node
    FROM graph_edges ge
    JOIN reachable r ON ge.from_node = r.node
)
SELECT node FROM reachable ORDER BY node;

MATERIALIZED vs NOT MATERIALIZED (PostgreSQL 12+)

By default, PostgreSQL decides whether to materialize (compute once and cache) a CTE or inline it. You can force the behavior:

-- Force materialization — CTE runs once, result cached
WITH expensive_query AS MATERIALIZED (
    SELECT * FROM orders WHERE compute_heavy_condition()
)
SELECT * FROM expensive_query WHERE customer = 'Alice'
UNION ALL
SELECT * FROM expensive_query WHERE customer = 'Bob';
-- expensive_query runs ONCE, cached, then filtered twice

-- Force inlining — CTE is expanded into the outer query
WITH simple_filter AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE region = 'North'
)
SELECT * FROM simple_filter WHERE product = 'Widget';
-- Optimizer can push the product filter into the table scan

Data-Modifying CTEs

PostgreSQL allows INSERT, UPDATE, and DELETE inside WITH clauses. The modified rows can be used in subsequent parts of the query via RETURNING.

Move Rows Between Tables

-- Archive old orders and return what was archived
WITH archived AS (
    DELETE FROM orders
    WHERE order_date < '2023-01-01'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

Chained Modifications

-- Insert a new customer, then create their first order in one statement
WITH new_customer AS (
    INSERT INTO customers (name, email)
    VALUES ('Frank', 'frank@example.com')
    RETURNING id
),
first_order AS (
    INSERT INTO orders (customer_id, product, quantity)
    SELECT id, 'Starter Kit', 1
    FROM new_customer
    RETURNING *
)
SELECT * FROM first_order;