# Scalar and IN Subqueries

> Use scalar and membership subqueries and handle empty results and NULL correctly.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="36.1" caption="Scalar and IN Subqueries"}
-- 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 {#run}

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

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

## Common pitfalls {#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)`.

> [!TIP] Tip
> Many subqueries can be rewritten as joins or CTEs; the choice is mostly about readability. Use subqueries for *single value*, joins for *multiple rows from another table*, and CTEs (next major lesson) when you want to name intermediate results.

> [!NOTE] Try it
> Find fruits whose id equals the average id (cast appropriately): `SELECT * FROM fruits WHERE id = (SELECT avg(id)::integer FROM fruits);`. Then rewrite the correlated subquery as a `LEFT JOIN + GROUP BY` and compare results.

## Downloads {#downloads}

SQL file: [scalar-and-in-subqueries.sql](/examples/source/scalar-and-in-subqueries.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)