PostgreSQL Subqueries and CTEs (WITH Queries)
On this page
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:
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:
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:
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:
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, IdaThe 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.
-- 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, IdaIN (subquery) gives the same answer here. The trouble starts with the negative. Find people who manage nobody:
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:
SELECT m.name
FROM employees m
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.employee_id);
-- Raj, TomUse 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:
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, EngineeringLater 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.
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 3PostgreSQL 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:
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; 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.