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;