BrevFeed
Sign in

25. Scalar and correlated subqueries

📖 Reading · 8 min
💡 Every code box below is live — edit it and hit Run.

A query, used as a value

A subquery is a SELECT nested inside another SQL statement. You already know IN (...) with a fixed list from Module 1's WHERE lesson — IN (SELECT ...) is the same idea with a query producing the list. This lesson formalizes the vocabulary and covers the two remaining major shapes.

Scalar subqueries: a query that returns exactly one value

A subquery that returns exactly one row and one column can be used anywhere a single value is expected — in SELECT, in WHERE, as an argument to a function:

-- The most expensive film's rental_rate, used as a comparison value.
SELECT title, rental_rate
FROM film
WHERE rental_rate = (SELECT max(rental_rate) FROM film);

-- Used directly in the SELECT list — a per-row computed column that
-- happens to reference an aggregate over the WHOLE table, not the group.
SELECT title, rental_rate,
       rental_rate - (SELECT avg(rental_rate) FROM film) AS diff_from_avg
FROM film
LIMIT 5;

If a scalar subquery unexpectedly returns more than one row, Postgres raises a runtime error (more than one row returned by a subquery used as an expression) — not a silent pick-the-first-row behavior. If it returns zero rows, the scalar subquery evaluates to NULL (not an error) — worth remembering given what Module 1's NULL lesson showed about NULL in comparisons downstream.

Uncorrelated vs. correlated subqueries

Every subquery so far has been uncorrelated: it can be evaluated completely independently, with no reference to the outer query at all — Postgres can (conceptually) run it once and reuse the result.

A correlated subquery references a column from the outer query inside its own body — it can't be evaluated in isolation, because its result depends on which outer row is currently being considered:

-- For EACH film, is its rental_rate above the average for its OWN rating?
-- The inner query's "f.rating" ties it to whichever outer row is current
-- — this cannot be evaluated once and reused, unlike the examples above.
SELECT f.title, f.rating, f.rental_rate
FROM film f
WHERE f.rental_rate > (
    SELECT avg(f2.rental_rate)
    FROM film f2
    WHERE f2.rating = f.rating   -- correlation: references the OUTER row's rating
)
ORDER BY f.rating, f.title
LIMIT 10;

Conceptually, a correlated subquery runs once per outer row (in practice, the planner often rewrites this into something far more efficient — you can watch it do so with EXPLAIN in Module 10 — but the conceptual model of "once per row" is the right way to reason about correctness while writing one). This is strictly more expressive than an uncorrelated subquery — "above average for this row's own group" isn't answerable any other way without GROUP BY and a subsequent join, which is exactly the alternative this pattern replaces.

EXISTS / NOT EXISTS

EXISTS (subquery) returns TRUE if the subquery produces at least one row, FALSE otherwise — it never looks at the subquery's actual column values, only whether rows exist at all. This makes it the natural partner for correlated subqueries answering "does a matching row exist":

-- Customers who have rented at least one NC-17 film — a correlated
-- EXISTS, the standard pattern for "related row exists."
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM rental r
    JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id
    WHERE r.customer_id = c.customer_id   -- correlation
    AND f.rating = 'NC-17'
);

SELECT 1 (or SELECT *) inside an EXISTS is conventional — the actual selected column(s) are irrelevant, since EXISTS only checks row-existence, never the values. Some style guides use SELECT 1 specifically to signal "this is a pure existence check" at a glance.

NOT EXISTS is the direct negation — "no matching row exists":

-- Customers who have NEVER rented an NC-17 film.
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE NOT EXISTS (
    SELECT 1
    FROM rental r
    JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id
    WHERE r.customer_id = c.customer_id
    AND f.rating = 'NC-17'
);

NOT EXISTS vs. NOT IN — a NULL trap

This is where Module 1's NULL semantics come back to bite. NOT IN (subquery) and NOT EXISTS (correlated subquery) look interchangeable but are not, the moment the subquery's column can contain NULL:

-- Setup: a rental with a NULL customer_id would break NOT IN silently.
-- (Doesn't actually occur in pagila — customer_id is NOT NULL there —
-- but reproduced on a scratch table below so you see it happen for real.)

If the subquery behind NOT IN can produce even one NULL, NOT IN returns zero rows for the entire query — not an error, not a partial result, just silently empty. Why: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and x <> NULL is UNKNOWN (the "comparing against unknown" rule from Module 1's NULL lesson) — UNKNOWN combined with AND poisons the whole expression to UNKNOWN, which WHERE discards. NOT EXISTS has no such trap — it never compares against individual values, so a NULL in the subquery's result is simply irrelevant to whether a matching row exists.

The practical rule this course follows: prefer NOT EXISTS over NOT IN for subqueries, always — NOT EXISTS is never wrong, while NOT IN is only safe when you can prove the subquery's column is NOT NULL (easy to prove today, easy to silently break tomorrow if the schema changes).

Check yourself

  1. What's the difference between an uncorrelated and a correlated subquery, in terms of what the inner query is allowed to reference?
  2. What does EXISTS actually check — the subquery's row values, or something else?
  3. Why does this course recommend NOT EXISTS over NOT IN as a default, even though they often produce the same result?
← Previous Next →