A subquery is a SELECT nested inside another statement. It lets you compute a value, a list, or a whole table on the fly and use it in the outer query — without a separate round trip or a temporary table.
The seed has customers and their orders. One customer (Edsger) has placed no orders, which makes the EXISTS examples concrete.
Scalar subquery: a single value
A subquery wrapped so it returns exactly one row, one column can stand in anywhere a single value can. Here we compare each order against the global average:
SELECT product, amount
FROM orders
WHERE amount > (SELECT avg(amount) FROM orders)
ORDER BY amount DESC;
The inner SELECT avg(amount) runs once and produces a single number; the outer query filters against it. You can also drop a scalar subquery into the SELECT list to show it alongside each row:
SELECT product,
amount,
round(amount - (SELECT avg(amount) FROM orders), 2) AS vs_avg
FROM orders
ORDER BY vs_avg DESC;
If a "scalar" subquery accidentally returns more than one row, Postgres raises an error — that's the contract.
Subquery in WHERE: IN
A subquery that returns one column and many rows produces a list you can test membership against with IN. Which customers have ever ordered?
SELECT name, country
FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY name;
Everyone except Edsger. Flip it to NOT IN to find the customers with no orders — but be careful: if the subquery can yield a NULL, NOT IN behaves surprisingly (any comparison to NULL is "unknown", so the whole thing can return nothing). For "rows with no match", NOT EXISTS below is the safer tool.
EXISTS: does a related row exist?
EXISTS (subquery) is true when the subquery returns at least one row — it doesn't care about the values, just the presence. It's the natural fit for "customers who have ordered":