Module 04 // SQL Nested

Subqueries & CTEs

> Queries within queries. Use subqueries to filter, derive, and compose multi-step transformations without intermediate tables.

What is a Subquery?

A subquery is a SELECT statement nested inside another query. It can appear in SELECT, FROM, or WHERE clauses. There are three main shapes: scalar (single value), row (single row), and table (full result set).

Scalar Subquery in SELECT

Returns exactly one value per outer row. Useful for adding aggregate context to individual rows.

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Subquery in WHERE

Filter outer rows based on a list, range, or aggregate from another query.

-- Employees earning above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees in departments located in NYC
SELECT name
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments WHERE city = 'NYC'
);

Correlated Subquery

A subquery that references the outer query. It re-executes for each outer row — powerful but potentially slow.

-- Employees earning above their own department's average
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

EXISTS vs IN

Both filter outer rows based on inner results. EXISTS stops at the first match (often faster); IN materializes the full inner set.

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

Subquery in FROM (Derived Tables)

Treat a subquery as a temporary table you can join against.

SELECT d.dept_name, top.max_salary
FROM departments d
JOIN (
  SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY dept_id
) top ON top.dept_id = d.id;

Common Table Expressions (CTEs)

CTEs make complex queries readable by giving subqueries a name. Define once with WITH, reuse multiple times.

WITH dept_stats AS (
  SELECT dept_id,
         AVG(salary) AS avg_salary,
         COUNT(*) AS headcount
  FROM employees
  GROUP BY dept_id
),
high_earners AS (
  SELECT e.*
  FROM employees e
  JOIN dept_stats s ON s.dept_id = e.dept_id
  WHERE e.salary > s.avg_salary * 1.5
)
SELECT * FROM high_earners ORDER BY salary DESC;

Recursive CTE

Walk hierarchies — org charts, category trees, graph traversal.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: each level reports to the previous
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

CTE vs Subquery — Which to Reach For

CTE wins for readability when the same derived set is referenced multiple times, or when logic stacks in clear stages.
Subquery wins when the derived set is used once and inlining keeps the query shorter.
In Postgres 12+, CTEs are inlined by default — same plan as a subquery. Force the old behavior with WITH x AS MATERIALIZED (...) when you want the optimizer to compute the CTE once and reuse the result.

-- Force materialization (compute once, reuse)
WITH heavy AS MATERIALIZED (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, h.total
FROM customers c
JOIN heavy h ON h.customer_id = c.id
WHERE h.total > 1000;

Writable CTEs

Postgres lets you put INSERT, UPDATE, or DELETE inside a CTE and chain them — a common pattern for "archive then delete" in one statement.

WITH moved AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

Next steps

See Window Functions for analytical patterns that pair naturally with CTEs, and Transactions for safely wrapping multi-step writes like the archive pattern above.