Subqueries and CTEs

Overview

• Subquery: A SELECT nested inside another statement — in WHERE, FROM, SELECT, or HAVING
• CTE (Common Table Expression): A named subquery defined with WITH, readable top-to-bottom
• Recursive CTE: WITH RECURSIVE — walks trees and graphs (org charts, category trees, bill of materials)
• Postgres 12+: CTEs are inlined by default; control with MATERIALIZED / NOT MATERIALIZED
• Sample Data: E-commerce Practice Queries

Subquery Types

Type	Returns	Where Used	Example
Scalar	One value (1 row × 1 col)	SELECT, WHERE	price > (SELECT avg(price) FROM products)
Row	One row, many columns	Row comparison	(a, b) = (SELECT x, y …)
Table / Derived	A relation	FROM	FROM (SELECT …) t
Correlated	Re-evaluated per outer row	WHERE, SELECT	References outer alias
Existence	Boolean	EXISTS, IN, ANY, ALL	Semi / anti joins

Scalar Subquery

-- Products priced above the average active product
SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products WHERE is_active)
ORDER BY price DESC;
-- A scalar subquery returning >1 row raises: ERROR: more than one row returned by a subquery used as an expression

Derived Table (Subquery in FROM)

SELECT status, round(avg(total), 2) AS avg_order_value
FROM (
    SELECT o.id, o.status, sum(oi.quantity * oi.unit_price) AS total
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id
) per_order                                  -- alias required
GROUP BY status
ORDER BY avg_order_value DESC;

Correlated Subquery

• What: The inner query references a column of the outer query, so it is logically run once per outer row
• Performance: Postgres often decorrelates EXISTS/IN into joins; correlated scalar subqueries in SELECT usually run as a SubPlan per row — fine for small outputs, slow for millions
• Rewrite Options: Join to a pre-aggregated derived table, or LATERAL

-- Each product with how many times it was ordered (correlated scalar in SELECT)
SELECT p.name,
       (SELECT count(*) FROM order_items oi WHERE oi.product_id = p.id) AS times_ordered
FROM products p
ORDER BY times_ordered DESC, p.name
LIMIT 5;
 
-- Products priced above their category's average (correlated in WHERE)
SELECT p.name, p.category_id, p.price
FROM products p
WHERE p.price > (SELECT avg(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id;

EXISTS, IN, ANY, ALL

Comparison

Operator	Meaning	NULL Behaviour
EXISTS (sub)	Sub returns ≥ 1 row	Never NULL — true/false only
x IN (sub)	x equals some value	NULL if no match and sub has a NULL
x NOT IN (sub)	x equals no value	Never true if sub has a NULL — see Joins
x = ANY (sub / array)	Same as IN	Works with arrays: id = ANY ($1::int[])
x > ALL (sub)	Greater than every value	True for an empty sub
x > ANY (sub)	Greater than at least one	False for an empty sub

-- EXISTS: categories that have at least one out-of-stock product
SELECT c.name FROM categories c
WHERE EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.id AND p.stock = 0);
 
-- IN with a list from a subquery
SELECT full_name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'cancelled');
 
-- = ANY with an array (what drivers send for "WHERE id IN (…)" lists)
SELECT id, name FROM products WHERE id = ANY (ARRAY[1, 3, 5]);
 
-- > ALL: products more expensive than every book
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 8)
  AND category_id <> 8
ORDER BY price
LIMIT 3;

CTEs (WITH)

Why Use CTEs

• Readability: Name each step; read the query top-down like a pipeline
• Reuse: Reference the same CTE several times in the main query
• Recursion: Only CTEs can recurse
• Data-Modifying CTEs: INSERT/UPDATE/DELETE … RETURNING inside WITH — chain writes in one statement (see Inserts Updates Upserts and MERGE)

WITH order_totals AS (
    SELECT order_id, sum(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
), customer_value AS (
    SELECT o.user_id, sum(t.total) AS lifetime_value, count(*) AS orders
    FROM orders o
    JOIN order_totals t ON t.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY o.user_id
)
SELECT u.full_name, cv.orders, cv.lifetime_value,
       round(cv.lifetime_value / cv.orders, 2) AS avg_order_value
FROM customer_value cv
JOIN users u ON u.id = cv.user_id
WHERE cv.lifetime_value > (SELECT avg(lifetime_value) FROM customer_value)   -- CTE reused
ORDER BY cv.lifetime_value DESC;

MATERIALIZED vs NOT MATERIALIZED (PG 12+)

• Before PG 12: Every CTE was an optimization fence — computed once, stored, outer filters could not be pushed in
• PG 12+ Default: A non-recursive, side-effect-free CTE referenced once is inlined like a subquery; referenced twice or more it is materialized
• MATERIALIZED: Force compute-once (useful to stop the planner re-running an expensive CTE, or as a deliberate fence)
• NOT MATERIALIZED: Force inlining even when referenced multiple times (lets WHERE id = … use an index inside)

Option	Computed	Outer Filters Pushed Down	Use When
Default (1 reference)	Inlined	Yes	Normal case
Default (2+ references)	Once	No	Expensive CTE reused
MATERIALIZED	Once	No	Expensive volatile work, or planner misestimates
NOT MATERIALIZED	Per reference	Yes	Cheap CTE + selective outer filter

-- Inlined: plan is just "Seq Scan on products, Filter: (id = 3)" — the filter reaches the table,
-- so on a large table it becomes an Index Scan using products_pkey
EXPLAIN (COSTS OFF)
WITH p AS NOT MATERIALIZED (SELECT * FROM products)
SELECT * FROM p WHERE id = 3;
 
-- Fenced: plan shows "CTE Scan on p, Filter: (id = 3)" over a full scan of products — no index possible
EXPLAIN (COSTS OFF)
WITH p AS MATERIALIZED (SELECT * FROM products)
SELECT * FROM p WHERE id = 3;

Recursive CTEs

Structure

WITH RECURSIVE name AS (
    anchor query                -- starting rows (e.g. the root)
  UNION ALL
    recursive query             -- joins the table to "name" (rows from the previous step)
)
SELECT … FROM name;
 
Execution: run anchor → working table; repeat recursive part on the working table
           until it returns no rows; UNION ALL all iterations together

• UNION vs UNION ALL: UNION removes duplicates each iteration (prevents cycles on graphs, but costs a hash); UNION ALL is faster for trees
• Cycle Safety: Track a path array and stop when a node repeats, or use PG 14’s CYCLE clause
• Depth Limit: Add a depth column and WHERE depth < 20 as a safety net

Example: Org Chart (Top-Down)

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS level, name::text AS chain
    FROM employees
    WHERE manager_id IS NULL                         -- anchor: the CEO
  UNION ALL
    SELECT e.id, e.name, e.manager_id, org.level + 1, org.chain || ' > ' || e.name
    FROM employees e
    JOIN org ON e.manager_id = org.id                -- step: direct reports of the previous level
)
SELECT repeat('    ', level - 1) || name AS org_chart, level, chain
FROM org
ORDER BY chain;

Example: Everyone Under a Manager (Subtree) and Total Salary

WITH RECURSIVE reports AS (
    SELECT id, name, salary FROM employees WHERE name = 'Raj'
  UNION ALL
    SELECT e.id, e.name, e.salary
    FROM employees e JOIN reports r ON e.manager_id = r.id
)
SELECT count(*) - 1 AS people_under_raj, sum(salary) AS team_cost_incl_raj
FROM reports;

Example: Path to Root (Bottom-Up) for a Category

WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id, 0 AS up
    FROM categories WHERE name = 'Laptops'
  UNION ALL
    SELECT c.id, c.name, c.parent_id, a.up + 1
    FROM categories c JOIN ancestors a ON c.id = a.parent_id
)
SELECT string_agg(name, ' > ' ORDER BY up DESC) AS breadcrumb FROM ancestors;
-- Electronics > Computers > Laptops

Example: Category Tree with Cycle Protection (PG 14+ SEARCH / CYCLE)

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
  UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
) SEARCH DEPTH FIRST BY name SET ord
  CYCLE id SET is_cycle USING path
SELECT name, parent_id, path FROM tree WHERE NOT is_cycle ORDER BY ord;

Example: Generate a Series Without generate_series

WITH RECURSIVE days AS (
    SELECT date '2025-01-01' AS d
  UNION ALL
    SELECT d + 1 FROM days WHERE d < date '2025-01-07'
)
SELECT d, to_char(d, 'Dy') AS weekday FROM days;

Subquery vs CTE vs Join vs LATERAL

Need	Best Tool
Filter by existence	EXISTS / NOT EXISTS
Readable multi-step logic	CTEs
Reuse an intermediate result	CTE (materialized once if referenced twice)
Per-row top-N / function call	LATERAL (Joins)
Hierarchy / graph traversal	WITH RECURSIVE
Value compared to an aggregate	Scalar subquery or window function (Window Functions)

Interview Questions

• “Correlated vs non-correlated subquery?”: Correlated references the outer row and is logically re-run per row; non-correlated runs once
• “Are CTEs an optimization fence in Postgres?”: Not since PG 12 unless referenced multiple times or marked MATERIALIZED
• “Print an org chart / find all reports of X”: Recursive CTE with anchor + UNION ALL step
• “How do you stop infinite recursion?”: UNION instead of UNION ALL, a visited-path array, the CYCLE clause, or a depth limit
• “IN vs EXISTS?”: Same plan for positive checks in modern Postgres; NOT IN differs dangerously from NOT EXISTS with NULLs

Summary

• Subqueries: Scalar, row, derived table, correlated; EXISTS/IN/ANY/ALL for set membership
• CTEs: Named steps for readability and reuse; inlined by default since PG 12
• MATERIALIZED / NOT MATERIALIZED: Explicit control over fencing vs inlining
• Recursive CTEs: Anchor + recursive step + UNION ALL; guard against cycles and runaway depth
• Choose the Right Tool: Joins for combining, EXISTS for filtering, CTEs for structure, LATERAL for per-row work