36 Scalar and IN Subqueries

A subquery is a SELECT written inside another query. There are three common flavors. A scalar subquery returns at most one row and exactly one column and can be used wherever a single value is allowed. An IN subquery tests membership: WHERE col IN (subquery). An EXISTS subquery tests whether the subquery returns any rows (next lesson). Subqueries can be correlated (referencing the outer row) or uncorrelated (independent). PostgreSQL is very good at optimizing them — you can lean on them without much worry.

What you’ll learn

• Scalar subqueries: one row, one column
• IN (subquery) and how it tests membership
• NOT IN and the NULL trap
• ANY / ALL as more general comparators
• When to use a subquery vs a join

Example

-- Scalar subquery: the fruit with the largest id
SELECT id, name
FROM fruits
WHERE id = (SELECT max(id) FROM fruits);

-- Scalar subquery in the SELECT list
SELECT name,
       id,
       (SELECT count(*) FROM fruits) AS total_fruits
FROM fruits
ORDER BY id
LIMIT 3;

-- IN: membership in a set
SELECT *
FROM fruits
WHERE id IN (SELECT id FROM fruits WHERE id <= 2)
ORDER BY id;

-- NOT IN with no NULLs is fine
SELECT id, name
FROM fruits
WHERE id NOT IN (SELECT id FROM fruits WHERE id > 100)
ORDER BY id;

-- ANY/ALL: generalize comparators with a subquery
SELECT id, name
FROM fruits
WHERE id > ALL (SELECT id FROM fruits WHERE id < 4)
ORDER BY id;

-- A correlated subquery references the outer row (here, c.id)
SELECT c.id, c.name,
       (SELECT count(*) FROM orders_example o WHERE o.customer_id = c.id) AS order_count
FROM customers_example c
ORDER BY c.id;

A scalar subquery must return at most one row. Zero rows is treated as NULL; more than one row raises an error. They are most useful when you want a value that depends on the table itself (max, count, latest).

IN (subquery) is equivalent to = ANY (subquery) — true when the outer value equals at least one row of the subquery. The planner usually turns this into a semi-join, which can be very efficient.

ANY and ALL generalize comparison to a set. x > ALL (s) is true when x is greater than every element of s (including the empty set, where it is vacuously true). x > ANY (s) is true when x is greater than at least one element.

Correlated subqueries reference columns from the outer query (e.g. o.customer_id = c.id above). They run once per outer row conceptually, though the planner often optimizes them into joins.

Run the example

To run (requires fruits and the join lesson tables):

$ psql -f source/scalar-and-in-subqueries.sql postgres
 id  |   name
-----+----------
 103 | fruit_3
(1 row)
 ...

Common pitfalls

• A scalar subquery that returns more than one row raises “more than one row returned by a subquery used as an expression.” Add a LIMIT 1 and a clear ORDER BY if you genuinely want “the first.”
• NOT IN (subquery) is the famous gotcha: if a NULL is present, nonmatching comparisons produce unknown while matching comparisons produce false; the filter keeps no rows. Use NOT EXISTS for safety.
• Correlated subqueries inside SELECT lists run once per outer row. If the planner cannot optimize, the cost is O(outer * inner). Re-express as a LEFT JOIN + count() ... GROUP BY when the subquery is expensive.
• IN on a long, hard-coded list (IN ('a', 'b', ..., thousands)) is fine but inelegant. For dynamic large lists, prefer a join against a temp table or unnest(array).

Downloads

SQL file: scalar-and-in-subqueries.sql · All lesson files · Setup guide