Subqueries & CTEs
queries inside queries

By now you can SELECT columns, WHERE-filter rows (Module 00), JOIN tables together (Module 01), and GROUP BY + aggregate (Module 02). One property has quietly held all of that together: every SQL query returns a table. Tables go in, a table comes out. That is called the closure property, and it is the entire reason subqueries exist. It is also the idea Maya keeps reaching for as the questions her manager sends get harder.

If a query produces a table, and SQL operates on tables, then a query can stand anywhere a table is expected. A query that returns one row and one column behaves like a single value, so it can go where a value goes. A query that returns one column of many rows behaves like a list. A query that returns a full grid behaves like a table you can select from. A subquery (or inner query) is just a SELECT wrapped in parentheses and dropped into a spot in an outer query.

The schema Maya works with is small: a departments table, an employees table that points back at it, and an orders table. Tiny, but every pattern in this module shows up the moment a real request lands on her desk.

The win is composability. Instead of cramming a hard question into one tangled statement, you answer a simpler sub-question first, then build on its result. This module walks every place a subquery can live, the one trap that catches almost everyone (NOT IN with NULLs), and finishes with Common Table Expressions, the readable, named form that makes deep nesting unnecessary.

A scalar subquery returns exactly one row and one column, a single value. Because it is a value, you can use it anywhere a value is legal: in a WHERE comparison, in the SELECT list, even inside an expression. The classic use is comparing each row against an aggregate of the whole table, which a plain WHERE cannot do on its own.

Maya's first ticket: "who earns above the company average?" It has a chicken-and-egg shape. You need the average before you can compare. A scalar subquery computes that average first, then the outer query compares every row to it.

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

-- the inner query first computes one number: AVG = 119166.67
-- then WHERE keeps rows whose salary beats it
 name  | salary
-------+---------
 Alice | 185000
 Bob   | 120000
 Carol | 135000

You can also drop a scalar subquery straight into the SELECT list to attach a computed value to every row. Here, each person's salary alongside the gap to the company average:

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

When the inner query returns one column but many rows, it behaves like a list, and IN asks "is this value among those?" In Module 00 you used IN (10, 20) with a hand-written list. Now the list comes from another query, so it stays correct as the data changes.

Next on Maya's list: "which employees work in a department located in NYC?" The departments in NYC live in the departments table; the people live in employees. A subquery pulls the matching dept_ids, and IN filters employees against them.

SELECT name, dept_id
FROM employees
WHERE dept_id IN (
        SELECT dept_id FROM departments
        WHERE location = 'NYC'
      );

-- inner query → the set of NYC dept_ids, e.g. {10, 20}
-- outer query → employees whose dept_id is in that set

NOT IN is the negation: "this value is among none of those." Read literally it seems obvious (think "employees not in any NYC department"), and for clean, NULL-free data it works. But NOT IN hides a trap so common it gets its own section next. For now, know that both forms exist and that the inner query must return a single column to feed them.

So far the inner queries were self-contained: they could run on their own, once, and the outer query reused the result. A correlated subquery is different. It references a column from the outer row, so it cannot run standalone. Conceptually it re-runs once per outer row, with that row's values plugged in. It is the SQL equivalent of a nested loop.

EXISTS (subquery) is true if the subquery returns at least one row, false otherwise. It does not care what the rows contain, only whether any exist. That makes it the natural tool for "does a related row exist?" questions, and it pairs perfectly with correlation. Maya hits it when her manager asks which departments are actually staffed.

SELECT d.dept_name
FROM departments d
WHERE EXISTS (
        SELECT 1                       -- value is irrelevant; only existence matters
        FROM employees e
        WHERE e.dept_id = d.dept_id     -- <- correlation: d.dept_id is the OUTER row
      );

For each department row d, the inner query asks "is there any employee whose dept_id equals this department's id?" The reference to d.dept_id is what makes it correlated. SELECT 1 is idiomatic: since EXISTS ignores the selected values, we select a cheap constant.

Flip it to NOT EXISTS for the opposite, "departments with no employees at all":

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
        SELECT 1 FROM employees e
        WHERE e.dept_id = d.dept_id
      );

EXISTS vs IN: same answer, different feel

Both can express membership tests, and a good planner often optimizes them similarly. The intuition:

This is the most famous subquery gotcha, and it bites Maya hard. Her "employees not in any NYC department" query runs clean and returns nothing, even though she knows it should match people. It follows directly from the three-valued logic you met in Module 00. The rule: if the subquery feeding NOT IN returns even one NULL, the whole NOT IN returns no rows, silently, with no error. The query looks right, runs fine, and gives the wrong answer.

Why? x NOT IN (a, b, NULL) is defined as x <> a AND x <> b AND x <> NULL. That last comparison, x <> NULL, is unknown, never true. And true AND unknown collapses to unknown, which WHERE treats as not-true. So every row fails the test, even rows that clearly should match.

-- Suppose one departments row has a NULL dept_id (bad data, or an outer join upstream).
-- This returns ZERO rows even though NYC clearly excludes some people.
SELECT name
FROM employees
WHERE dept_id NOT IN (
        SELECT dept_id FROM departments   -- if ANY dept_id here is NULL...
      );                                  -- ...the whole NOT IN becomes unknown → 0 rows

There are two robust fixes. The preferred one is to use NOT EXISTS, which uses a per-row existence test and is immune to the NULL collapse. The alternative is to filter the NULLs out of the subquery explicitly.

-- FIX 1 (preferred): NOT EXISTS is NULL-safe
SELECT e.name
FROM employees e
WHERE NOT EXISTS (
        SELECT 1 FROM departments d
        WHERE d.dept_id = e.dept_id
      );

-- FIX 2: strip NULLs from the subquery before NOT IN sees them
SELECT name
FROM employees
WHERE dept_id NOT IN (
        SELECT dept_id FROM departments
        WHERE dept_id IS NOT NULL     -- guard against the trap
      );

ANY and ALL let you combine a comparison operator (>, <, =, …) with a whole list from a subquery. They answer "compared to some of these?" versus "compared to all of these?"

So IN is just sugar for = ANY, and NOT IN is <> ALL, which is exactly why NOT IN inherits the same NULL trap from the previous section. The genuinely useful ones are the inequalities: "earns more than everyone in department 20" is a clean > ALL.

SELECT name, salary
FROM employees
WHERE salary > ALL (
        SELECT salary FROM employees WHERE dept_id = 20
      );
-- dept 20 salaries = {135000, 98000}; > ALL means > 135000 → Alice (185000)

When a subquery returns a full grid of rows and columns, you can put it in the FROM clause and select from it as if it were a real table. This is a derived table (or inline view). It is how you filter or join against an aggregated result, something a plain WHERE cannot do, because aggregates do not exist until after grouping.

Maya's manager wants the budget picture: "which departments have an average salary above 100,000?" First aggregate per department, then filter on that average. The aggregation is the inner table; the filter wraps around it.

SELECT t.dept_id, t.avg_sal
FROM (
        SELECT dept_id, AVG(salary) AS avg_sal
        FROM employees
        GROUP BY dept_id
     ) AS t                       -- the derived table MUST be aliased: t
WHERE t.avg_sal > 100000;

The inner query produces a small table of (dept_id, avg_sal) rows; the outer query treats t like any table and filters it. You could write this with HAVING (Module 02), and for one level of aggregation that is cleaner. Derived tables earn their keep when you need to aggregate, then join or aggregate again on the result.

By Friday Maya's derived-table query works, but a teammate reviewing it has to read it inside-out: start at the deepest parentheses and unwind outward. A Common Table Expression (CTE) fixes that. With the WITH keyword you give a subquery a name up top, then refer to it by name below, so the query reads top-down, like defining a variable before using it.

Here is the exact section-6 query rewritten as a CTE. Same result, but the intent is named and the structure is flat:

WITH dept_avg AS (
       SELECT dept_id, AVG(salary) AS avg_sal
       FROM employees
       GROUP BY dept_id
)
SELECT dept_id, avg_sal
FROM dept_avg                      -- reference the CTE by name, like a table
WHERE avg_sal > 100000;

Read it as a sentence: "with a table called dept_avg defined as the per-department averages, select from it where the average exceeds 100,000." The logic that was buried in nested parentheses now has a label and lives at the top.

When CTEs beat nested subqueries

WITH dept_avg AS (
       SELECT dept_id, AVG(salary) AS avg_sal
       FROM employees GROUP BY dept_id
),
high_paying AS (                  -- second CTE references the first
       SELECT dept_id FROM dept_avg WHERE avg_sal > 100000
)
SELECT e.name, e.salary
FROM employees e
JOIN high_paying h ON e.dept_id = h.dept_id;

Maya's last task of the week is the one that scared her: build the org chart. Here is where subqueries do something nothing else can, traverse a hierarchy. The employees table has a self-referencing manager_id, so each person points at their boss. To produce the full management chain ("who reports up to whom, and how many levels deep?") you follow that chain an unknown number of steps. A recursive CTE does exactly that.

A WITH RECURSIVE CTE has three parts, joined by UNION ALL:

WITH RECURSIVE org AS (
    -- ANCHOR: the top of the tree (no manager)
    SELECT emp_id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- RECURSIVE: people whose manager is already in 'org'
    SELECT e.emp_id, e.name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id   -- join back to the CTE itself
)
SELECT depth, emp_id, name, manager_id
FROM org
ORDER BY depth, emp_id;

 depth | emp_id | name  | manager_id
-------+--------+-------+------------
     1 |      1 | Alice |     (null)     -- anchor
     2 |      2 | Bob   |          1     -- reports to Alice
     2 |      3 | Carol |          1
     2 |      6 | Frank |          1
     3 |      4 | Dan   |          3     -- reports to Carol
     3 |      5 | Eve   |          3

Maya traces the machine by hand. The anchor finds Alice (depth 1). The recursive member then joins employees to the rows just produced: who reports to Alice? Bob, Carol, Frank, all depth 2. Next pass, who reports to those? Dan and Eve report to Carol, so depth 3. The pass after that finds nobody new, so the recursion terminates. UNION ALL piles every batch into the final org table. The chart she dreaded falls out in eight lines.

Use the same three-table schema from Module 00 (recreate it if needed) in any Postgres playground or local psql. Write each query before peeking at a hint, and predict the rows out loud first.

• Scalar subquery: list every employee earning above the company average. Then add a gap_to_avg column showing how far above (or below) each sits.
• IN: find all employees whose department is located in 'NYC' using a subquery against departments. Then rewrite the same intent as a JOIN and confirm the row counts match.
• EXISTS: list departments that have at least one employee. Then flip to NOT EXISTS for departments with none.
• The trap: insert a departments row with a NULL dept_id, then run a NOT IN (SELECT dept_id FROM departments) query. Watch it return zero rows, then fix it two ways (NOT EXISTS, and filtering NULLs).
• > ALL: find employees paid more than everyone in department 20. Confirm it matches > (SELECT MAX(salary) FROM employees WHERE dept_id = 20).
• Derived table: select departments whose average salary exceeds 100,000 using a subquery in FROM. Deliberately omit the alias once to see the error, then add it.
• CTE: rewrite the previous query with WITH. Then chain a second CTE that lists the employees in those high-paying departments.
• Recursive CTE: build the org chart with a depth column from Alice down. Then modify the anchor to start at Carol (emp_id = 3) and produce only her subtree.