SQL Subqueries & CTEs | My Portfolio

Scalar, Multi-Row, and Correlated Subqueries

-- Scalar — returns a single value
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

-- Multi-row — use with IN / ANY / ALL
SELECT name, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE level = 'Senior');

-- Correlated — references the outer query, runs conceptually once PER outer row
SELECT e.name, e.salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

A correlated subquery is meaningfully slower than an equivalent JOIN/window-function rewrite on large tables, precisely because of this per-row re-evaluation — see [[SQL Window Functions]] for the one-pass alternative to the "salary vs department average" pattern above.

EXISTS / NOT EXISTS vs IN / NOT IN

SELECT u.name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

EXISTS short-circuits on the first match — faster when the subquery's potential result set is large. IN materializes the whole subquery result first — fine when that result is small. NOT EXISTS is NULL-safe; NOT IN is not (see [[SQL JOINs]]'s anti-join NULL trap) — this asymmetry alone is reason enough to default to NOT EXISTS for anti-join logic.

CTEs — Named, Reusable Subqueries

WITH high_value_users AS (
  SELECT user_id, SUM(amount) AS total_spent FROM orders
  GROUP BY user_id HAVING SUM(amount) > 1000
)
SELECT u.name, h.total_spent FROM users u JOIN high_value_users h ON u.id = h.user_id;

	Subquery	CTE
Readability	Nested, harder to trace	Named, top-level, easier to follow
Reuse	Can't reference twice	Can reference the same CTE name multiple times
Debugging	Hard to isolate	Comment out the main query, `SELECT * FROM cte_name` directly
Performance	Optimizer usually flattens both	Same in most modern DBs — PostgreSQL 12+ inlines CTEs by default (`MATERIALIZED` forces the old always-compute-once behavior)

Rule of thumb: reach for a CTE when logic is complex, reused, or benefits from being named; a plain subquery is fine for a simple one-off inline filter.

Recursive CTEs — Traversing Hierarchies

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL   -- anchor
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e JOIN org_tree ot ON e.manager_id = ot.id                          -- recursive step
)
SELECT * FROM org_tree ORDER BY depth, name;

How it actually executes: the anchor runs once, producing an initial working set; each subsequent pass joins the recursive term against only the previous pass's new rows (not all accumulated rows so far), producing the next layer — this incremental-frontier behavior is exactly why it terminates on a finite tree instead of looping forever.

Cycle prevention (required for genuinely cyclic graphs, not just trees):

WITH RECURSIVE path AS (
  SELECT id, manager_id, ARRAY[id] AS visited FROM employees WHERE id = @start_id
  UNION ALL
  SELECT e.id, e.manager_id, p.visited || e.id
  FROM employees e JOIN path p ON e.manager_id = p.id
  WHERE NOT e.id = ANY(p.visited)      -- stop before revisiting a node already in this path
)
SELECT * FROM path;

Subquery in FROM (Derived Table)

SELECT dept, avg_sal FROM (
  SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department
) dept_stats
WHERE avg_sal > 70000;

Functionally identical to a CTE — the choice is style/reuse, not correctness.

Interview Drills

When would EXISTS clearly outperform IN? — When the subquery's underlying result set is large — EXISTS stops at the first matching row per outer row, while IN must first materialize the entire subquery result before any comparison happens.
A recursive CTE on genuinely cyclic data (A→B→A) never terminates. How do you fix it without changing the underlying data? — Track visited nodes explicitly (e.g., an accumulating array column) and add a WHERE NOT id = ANY(visited) guard in the recursive term — this stops the recursion from re-entering a node already on the current path, regardless of how the underlying graph is shaped.
Does using a CTE instead of a subquery change query performance? — Usually no — most modern optimizers (PostgreSQL 12+, for instance) inline/flatten a CTE the same way they would a subquery; the choice is primarily about readability and reuse, not a performance lever, unless MATERIALIZED is explicitly forced.

Cross-links

[[SQL JOINs]] · [[SQL Window Functions]] · [[SQL Basics & Query Fundamentals]]