Subqueries and Set Operations

Description

Some questions can't be answered by one simple query. "Which products cost more than their category's average?" needs a number worked out first and then compared. "Which newsletter subscribers never bought anything?" compares one list with another. "Merge the webinar list with the newsletter list, no duplicates" stacks two results into one. This lesson is about building queries out of other queries.

There are two tools for that. A subquery is a query in parentheses inside another one - as a single value, as a list to check against, or as a small table to join. A set operation (UNION, INTERSECT, EXCEPT) stacks two query results on top of each other. Both are easy to write, and each has one NULL or duplicate rule that can turn a sensible-looking query into a wrong one. We'll go step by step:

1. Scalar subqueries - a query that stands in for a single value.
2. Correlated subqueries - a subquery that depends on the current row.
3. Derived tables - work out a summary first, then join it.
4. IN, EXISTS, ANY and ALL, including the surprising empty-set rule.
5. UNION, INTERSECT and EXCEPT, and how they treat NULLs and duplicates.
6. Which form to pick for which question.

Examples use a products table (6 products in three categories), a newsletter list of emails and a buyers list of emails - one of which is NULL (a buyer who checked out without one). Every result was produced by PostgreSQL 18.

Scalar subqueries

A subquery in parentheses where a single value is expected - in SELECT, WHERE, SET - must return at most one row with one column. Two rules follow:

• No rows → the value is NULL, and a comparison with NULL is unknown, so the row is filtered out without an error.
• More than one row → a runtime error ("more than one row returned by a subquery used as an expression"). It may pass every test and fail in production the day a second row appears.

Correlated subqueries

A subquery that refers to a column of the outer query is correlated: logically it runs once per outer row, with that row's values plugged in.

SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (SELECT AVG(p2.price)
                 FROM products p2
                 WHERE p2.category = p.category)
ORDER BY p.id;

Coffee averages 17.00 and gear 63.33; Green Tea is the only tea, so it equals its own average and isn't above it. "Runs once per row" describes the meaning, not the plan - Postgres often turns correlated EXISTS into a join - but a correlated scalar subquery in SELECT over millions of rows can genuinely run millions of times.

Derived tables: aggregate first, then join

A subquery in FROM is a derived table: a temporary result you join like any table. It's the natural way to compute something per group once and compare rows against it - and to aggregate a child table before joining it, which is the cure for fan-out from the previous module.

SELECT p.name, p.price, s.avg_price
FROM products p
JOIN (SELECT category, ROUND(AVG(price), 2) AS avg_price
      FROM products
      GROUP BY category) AS s ON s.category = p.category
WHERE p.price > s.avg_price
ORDER BY p.id;

Since PostgreSQL 16 a derived table no longer needs an alias (FROM (SELECT ...) is enough), though naming it still makes the join readable. A WITH clause (next topic) does the same job with the subquery pulled up front.

IN, EXISTS, ANY and ALL

EXISTS (subquery) is true when the subquery returns any row; x IN (subquery) when some row equals x. For "not in", remember the rule from Querying Basics: NOT IN against a list containing a NULL is never true, while NOT EXISTS is safe.

x > ANY (subquery) is true if the comparison holds for at least one row; x > ALL (subquery) if it holds for every row - and ALL over an empty set is true:

SELECT COUNT(*) AS cheaper_than_every_toy
FROM products
WHERE price < ALL (SELECT price FROM products WHERE category = 'toys');

There are no toys, so every product qualifies. If "no toys" should mean "no answer", check for existence explicitly or compare with MIN(...), which is NULL over no rows.

Set operations

UNION, INTERSECT and EXCEPT combine the results of two queries vertically:

• They match columns by position, not by name - the first query's column names win - and the column counts and types must line up.
• UNION, INTERSECT and EXCEPT remove duplicates; the ALL variants keep them. Deduplicating means sorting or hashing the whole result, so use UNION ALL whenever duplicates are impossible or wanted.
• An ORDER BY at the end sorts the combined result.

SELECT
    (SELECT COUNT(*) FROM (SELECT email FROM newsletter UNION     SELECT email FROM buyers)) AS union_rows,
    (SELECT COUNT(*) FROM (SELECT email FROM newsletter UNION ALL SELECT email FROM buyers)) AS union_all_rows;

ava@example.com is on both lists: UNION keeps it once. The NULL is kept once too - for duplicate removal, set operations treat NULLs as equal to each other.

That same rule makes EXCEPT behave differently from NOT IN:

SELECT COUNT(*) AS not_buyers
FROM newsletter
WHERE email NOT IN (SELECT email FROM buyers);

SELECT email FROM newsletter
EXCEPT
SELECT email FROM buyers
ORDER BY email;

NOT IN meets the NULL in buyers and returns nothing; EXCEPT compares whole rows as "distinct or not", where NULL is just another value, and gives the right answer. NOT EXISTS would too.

Where it breaks

• A scalar subquery that can return two rows - works in testing, errors in production. Aggregate it (MAX, COUNT) or add a key that guarantees one row.
• A scalar subquery that returns no rows - NULL, which silently filters the outer row out of a comparison.
• NOT IN with a NULL in the subquery - an empty result. Use NOT EXISTS or EXCEPT.
• > ALL over an empty set - true for every row.
• UNION where UNION ALL was meant - a legitimately repeated row (two identical payments) disappears, and the query pays for a sort it didn't need.
• Set operations line up columns by position - swap two columns in one branch and the result mixes them without an error when the types match.

Which one should I write?

Most questions can be written several ways. Start from what the question asks for:

Tradeoffs

• Correlated subquery vs derived table vs window function for "compare with the group": the correlated subquery reads most like the question; the derived table computes each group once and is easy to extend; AVG(price) OVER (PARTITION BY category) (the Window Functions module) does it in one pass without a join.
• EXISTS vs JOIN for "has at least one": a join returns a row per match (duplicates the outer row); EXISTS returns each outer row once and can stop at the first match.
• EXCEPT vs NOT EXISTS: EXCEPT is concise when comparing the same columns, but it deduplicates and can't return columns that aren't compared; NOT EXISTS keeps the outer rows as they are.

Going further. The deep dive (next tab) shows what the planner does with each kind of subquery: which ones become joins, which run once, and the one that can run once per row - plus LATERAL, how set operations execute, and row comparisons.

Check yourself

1. A scalar subquery in WHERE price > (SELECT price FROM products WHERE name = 'Mug') worked for months, then started failing. What probably changed?

2. buyers.email contains one NULL. Why does email NOT IN (SELECT email FROM buyers) return nothing, while EXCEPT works?

3. When should you write UNION ALL instead of UNION?

Key takeaways

• A scalar subquery must return at most one value: no rows gives NULL, two rows is an error.
• A correlated subquery refers to the outer row; logically it runs once per row.
• A derived table (subquery in FROM) computes a result once to join against - including aggregating a child table before joining it.
• NOT IN with a NULL returns nothing; NOT EXISTS and EXCEPT don't have that problem. ALL over an empty set is true.
• Set operations match columns by position; UNION/INTERSECT/EXCEPT deduplicate (treating NULLs as equal), ALL variants don't.