Subqueries

A query nested inside another: as a single value, a list, or a derived table.

Syntax

-- scalar: exactly one row, one column
SELECT ..., (SELECT max(x) FROM t) AS m FROM ...
WHERE price > (SELECT avg(price) FROM products)
-- list
WHERE id IN (SELECT ...)
-- derived table
FROM (SELECT ... ) AS sub
-- correlated: refers to the outer row
WHERE x > (SELECT avg(x) FROM t t2 WHERE t2.grp = t1.grp)

Scalar subquery: returns one value. Zero rows → NULL; more than one row → error (more than one row returned by a subquery used as an expression).
List subquery with IN, ANY, ALL: one column, any number of rows.
Derived table in FROM: any result set, used like a table. (An alias is optional since PostgreSQL 16, but name it anyway.)
Correlated subquery: references the outer query's columns, so it is logically evaluated per outer row (e.g. "above the average of its own category").
x > ALL (subquery) is true when x beats every value; x = ANY (subquery) is the same as IN.
When a subquery is reused or gets long, a CTE (WITH) is easier to read.

Examples

Scalar subquery in WHERE starter

SELECT product_name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

The inner query returns one number; the outer query compares each price with it.

Correlated subquery starter

SELECT p.product_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.category, p.price DESC;

Each product is compared with the average of its own category.

Derived table shop

SELECT round(avg(orders_per_customer), 2) AS avg_orders_per_buyer
FROM (
  SELECT customer_id, count(*) AS orders_per_customer
  FROM orders
  GROUP BY customer_id
) AS per_customer;

Aggregate per customer first, then average those counts (nested aggregates need this step).

Pitfalls

A scalar subquery that can return several rows (add an aggregate, a tighter filter or LIMIT 1 with ORDER BY).
NOT IN (subquery) with NULLs in the subquery (returns nothing). Use NOT EXISTS.
Correlated subqueries in the select list over big tables when one GROUP BY + join would do.