Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 2 · Lesson 9 · 7 min read

PostgreSQL Subqueries and CTEs (WITH Queries)

On this page
  1. Subqueries: when one question depends on another
  2. Scalar and table subqueries
  3. Correlated subqueries
  4. EXISTS, and the NOT IN trap with NULLs
  5. Common table expressions (WITH)
  6. Walk a tree with WITH RECURSIVE
  7. Check yourself

Subqueries: when one question depends on another

"Who earns more than the average?" sounds like one question. In SQL it's two: first work out the average, then compare each salary with it. Subqueries and CTEs let you write both steps in one statement.

Set up a small employees table to follow along:

SQL
CREATE TABLE employees (
  employee_id  int PRIMARY KEY,
  name         text NOT NULL,
  department   text NOT NULL,
  salary       numeric(10,2) NOT NULL,
  manager_id   int REFERENCES employees
);
INSERT INTO employees VALUES
  (1, 'Mo',  'Exec',        150000, NULL),
  (2, 'Lin', 'Engineering', 120000, 1),
  (3, 'Raj', 'Engineering',  90000, 2),
  (4, 'Ida', 'Sales',        70000, 1),
  (5, 'Tom', 'Sales',        60000, 4);

Scalar and table subqueries

Put a SELECT in parentheses and you can use its result inside another query. What it returns decides where it can go.

A subquery that returns one value can go anywhere a single value can:

SQL
SELECT name, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);
-- Mo, Lin (the average is 98,000)

If that inner query ever returns two rows, PostgreSQL raises "more than one row returned by a subquery used as an expression". It doesn't guess, which is what you want.

A subquery that returns a whole table can go in FROM, as long as you give it an alias:

SQL
SELECT d.department, d.avg_salary
FROM (
  SELECT department, avg(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS d
WHERE d.avg_salary > 70000;

Correlated subqueries

Now change the question: who earns more than the average of their own department? The inner query needs to know which department you're looking at:

SQL
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   -- refers to the outer row
);
-- Lin, Ida

The inner query refers to e, a table from the outer query. That makes it correlated. Think of it as running once for each outer row. The planner can often rewrite it into something smarter, but on big tables check with EXPLAIN (Day 22) and make sure the inner lookup has an index.

EXISTS, and the NOT IN trap with NULLs

To ask "does a matching row exist?", use EXISTS. It only checks whether the subquery returns any row, so what you SELECT inside doesn't matter. SELECT 1 is the convention.

SQL
-- Managers: people whom someone else reports to
SELECT m.name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.employee_id);
-- Mo, Lin, Ida

IN (subquery) gives the same answer here. The trouble starts with the negative. Find people who manage nobody:

SQL
SELECT name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);
-- zero rows!

Raj and Tom manage nobody, yet the query returns nothing. Mo's manager_id is NULL, so the list is (NULL, 1, 2, 1, 4). PostgreSQL can't say 3 is "not in" a list containing an unknown value, so the answer is unknown, and WHERE drops every row.

NOT EXISTS doesn't compare values, so the NULL can't hurt it:

SQL
SELECT m.name
FROM employees m
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.employee_id);
-- Raj, Tom

Use NOT EXISTS for "has no match" queries. Keep IN for short, fixed lists like status IN ('paid', 'shipped'). Don't pick between them for speed. The planner usually turns both into the same plan, so correctness is what decides.

Common table expressions (WITH)

Nested subqueries get hard to read after two levels. A common table expression (CTE) gives each step a name, and you read the query from top to bottom:

SQL
WITH dept_avg AS (
  SELECT department, avg(salary) AS avg_salary
  FROM employees
  GROUP BY department
),
company_avg AS (
  SELECT avg(salary) AS avg_salary FROM employees
)
SELECT d.department, round(d.avg_salary) AS avg_salary
FROM dept_avg d, company_avg c
WHERE d.avg_salary > c.avg_salary;
-- Exec, Engineering

Later CTEs can use earlier ones. It also makes debugging easier: write the first step, run SELECT * FROM dept_avg, check it, then add the next.

You may read that CTEs are slow because PostgreSQL computes them separately. That was true before PostgreSQL 12. Today a simple CTE is folded into the main query like a subquery. If you want the old behavior, write AS MATERIALIZED.

Walk a tree with WITH RECURSIVE

Some questions have no fixed depth. "Everyone who reports to Mo, directly or indirectly" might be two levels deep or ten. A recursive CTE handles it in two parts: a starting point, and a step that finds the next level from the rows found so far.

SQL
WITH RECURSIVE org AS (
  SELECT employee_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL                            -- start: the top
  UNION ALL
  SELECT e.employee_id, e.name, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id          -- step: their reports
)
SELECT name, depth FROM org ORDER BY depth, name;
-- Mo 1 | Ida 2 | Lin 2 | Raj 3 | Tom 3

PostgreSQL repeats the step until it finds no new rows. If your data has a cycle, say A manages B and B manages A, it never stops. Guard against that with a depth limit like WHERE org.depth < 20 in the step.

Since PostgreSQL 14 there's a cleaner guard, the CYCLE clause. It tracks the path of ids visited so far and stops when one repeats. Here a bad import has made Mo report to Raj, and the query walks up the management chain from Tom:

SQL
UPDATE employees SET manager_id = 3 WHERE employee_id = 1;   -- the bad data

WITH RECURSIVE chain AS (
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE employee_id = 5                               -- start: Tom
  UNION ALL
  SELECT m.employee_id, m.name, m.manager_id
  FROM employees m
  JOIN chain ON m.employee_id = chain.manager_id      -- step: up to the manager
) CYCLE employee_id SET is_cycle USING path
SELECT name, is_cycle, path FROM chain;
Code
 name | is_cycle |           path
------+----------+---------------------------
 Tom  | f        | {(5)}
 Ida  | f        | {(5),(4)}
 Mo   | f        | {(5),(4),(1)}
 Raj  | f        | {(5),(4),(1),(3)}
 Lin  | f        | {(5),(4),(1),(3),(2)}
 Mo   | t        | {(5),(4),(1),(3),(2),(1)}

The query stopped on its own, and the last row shows exactly where the loop is: Mo appears twice in the path. Filter with WHERE NOT is_cycle to hide that row, or alert on it to find bad data. Run UPDATE employees SET manager_id = NULL WHERE employee_id = 1; afterwards to undo the bad data. For plain number or date ranges, skip recursion and use generate_series(1, 10).

Check yourself

At least one order_items.product_id is NULL, which makes every NOT IN test unknown. Rewrite it with NOT EXISTS, or filter out NULLs in the subquery.

Since PostgreSQL 12, simple CTEs are inlined like subqueries, so there's usually no cost. Use WITH for readability, and check EXPLAIN if a specific query gets slow.

The data has a cycle, so the step keeps finding "new" rows. Add a depth limit, or track the visited ids in an array and stop when an id repeats.

When the planner can't turn it into a join and has to run it once per outer row, which gets slow on large tables. Check EXPLAIN for a subplan executed many times. Rewriting it as a join, or with EXISTS, which the planner handles well, usually fixes it.

Since PostgreSQL 12, simple CTEs are usually inlined into the main query so the planner can optimize them together. WITH x AS MATERIALIZED (...) forces the old behavior: compute the CTE once and store the result. That's useful when the CTE is expensive and referenced several times.