Real-world SQL queries often need intermediate results — "find orders whose amount is above the average" or "show employees whose department has more than 10 people." Subqueries and CTEs let you compose complex queries from simpler building blocks.

Scalar Subquery

A subquery that returns a single value (one row, one column). It can appear in SELECT, WHERE, or HAVING:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query computes the average salary once, and the outer query uses it as a threshold. The database evaluates the scalar subquery once and substitutes the result.

Correlated Subquery

A subquery that references columns from the outer query. Unlike a scalar subquery, it is conceptually re-executed for every row of the outer query:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department
);

This finds employees earning above their department's average. The inner query depends on e.department from the outer row, so it produces a different result per outer row. Correlated subqueries can be expensive — O(n * cost_of_inner) — but the optimizer often rewrites them.

EXISTS vs IN

Both test membership, but behave differently:

• IN: WHERE id IN (SELECT customer_id FROM orders) — the subquery returns a list of values, and the outer row matches if its value is in that list. Computes the full subquery result first.
• EXISTS: WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id) — a correlated subquery that returns TRUE as soon as any matching row is found (short-circuits). Often faster than IN when the subquery would return many rows, because EXISTS can stop at the first match.

Subquery Flattening / Unnesting

A naive engine would execute correlated subqueries row by row. Modern optimizers flatten (unnest) subqueries into joins:

WHERE id IN (SELECT customer_id FROM orders)

becomes internally:

JOIN (SELECT DISTINCT customer_id FROM orders) sub ON id = sub.customer_id

This converts an O(n * m) correlated scan into an O(n + m) hash join. PostgreSQL calls this "subquery scan → hash semi join."

CTE (Common Table Expression)

A CTE defines a named temporary result using the WITH clause:

WITH high_value_orders AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 10000
)
SELECT c.name, h.total
FROM customers c
JOIN high_value_orders h ON c.id = h.customer_id;

CTEs improve readability by breaking complex queries into logical steps. Multiple CTEs can be chained, and later CTEs can reference earlier ones.

CTE Materialization vs Inlining

The optimizer must decide: materialize the CTE (compute it once, store the result temporarily) or inline it (substitute the CTE definition into each reference, optimizing as if it were a subquery).

• Materialization: good when the CTE is referenced multiple times or the result set is small.
• Inlining: good when the optimizer can push predicates from the outer query into the CTE for better index usage.

PostgreSQL 12+ defaults to inlining CTEs that are referenced only once, and materializing those referenced multiple times. You can force behavior with AS MATERIALIZED or AS NOT MATERIALIZED.

Recursive CTE

WITH RECURSIVE enables queries over hierarchical or graph data (org charts, category trees, bill of materials):

WITH RECURSIVE org_tree AS (
  -- Base case: the CEO
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive case: employees reporting to someone in org_tree
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

The engine starts with the base case, then repeatedly executes the recursive step until no new rows are produced. This replaces what would otherwise require application-level loops or multiple queries.