Correlated Subqueries & EXISTS

A correlated subquery is an inner SELECT that can see the current row from the outer query. That small change unlocks the cleanest SQL shape for "has at least one" and "has none" questions: EXISTS and NOT EXISTS.

This lesson also pays off the NULL warning from 3.2. NOT IN looks like a natural way to find missing matches, but if the subquery returns even one NULL, it can silently return zero rows.

Correlation: the inner query sees the outer row

In 7.1, the inner query stood on its own. A correlated subquery reaches outward. The giveaway is an outer alias used inside the inner query:

SELECT c.name,
       c.city,
       (
         SELECT count(*)
         FROM orders AS o
         WHERE o.customer_id = c.id
       ) AS order_count
FROM customers AS c
ORDER BY c.name;

   name   |  city  | order_count
----------+--------+-------------
 Ada      | Berlin |           2
 Barbara  | Oslo   |           0
 Edsger   | Lisbon |           2
 Grace    | Lisbon |           1
 Linus    | Berlin |           2
 Margaret | Austin |           0
(6 rows)

Read the condition slowly: o.customer_id = c.id. The o alias belongs to the inner query. The c alias belongs to the outer query. So for each customer row the outer query is considering, the inner query counts orders whose customer_id matches that current customer's id.

That is correlation. You are not copying a value from one query into another by hand; you are letting the inner query depend on the outer row's alias. The optimizer still chooses the physical plan, but the meaning is row-aware: "count orders for this customer."

EXISTS: asking "is there at least one?"

Counting is useful when you need the count. But many questions are simpler: does a match exist at all? For that, use EXISTS.

SELECT c.name, c.city
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
)
ORDER BY c.name;

  name  |  city
--------+--------
 Ada    | Berlin
 Edsger | Lisbon
 Grace  | Lisbon
 Linus  | Berlin
(4 rows)

EXISTS does not use the values selected by the inner query. SELECT 1 is the usual convention because it says the truth plainly: "I do not care which columns are in the matching row; I only care that a row exists." SELECT o.id or SELECT o.product would mean the same thing here.

That is also the performance intuition. Once the database has found one matching order for Ada, the answer to "does Ada have any orders?" is already yes. EXISTS describes a question that can stop at the first match; it does not need to count every matching row just to produce a boolean answer.

The NOT IN trap: NULL poisons the list

Find customers with no orders. The obvious query is tempting:

SELECT c.name, c.city
FROM customers AS c
WHERE c.id NOT IN (
  SELECT o.customer_id
  FROM orders AS o
)
ORDER BY c.name;

 name | city
------+------
(0 rows)

Zero rows. No error, no warning, and completely wrong for the business question. The café does have customers with no orders; the query lost them because the subquery's list contains a NULL.

SELECT o.id, o.product, o.customer_id
FROM orders AS o
WHERE o.customer_id IS NULL;

 id  |  product  | customer_id
-----+-----------+-------------
 108 | Gift Card |        NULL
(1 row)

The NULL-safe version asks the question directly: keep customers for whom no matching order exists.

SELECT c.name, c.city
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
)
ORDER BY c.name;

   name   |  city
----------+--------
 Barbara  | Oslo
 Margaret | Austin
(2 rows)

Notice why NOT EXISTS survives the NULL order. For customer Barbara, the inner condition asks o.customer_id = 6. The gift-card row has NULL = 6, which is UNKNOWN, so it is not a matching inner row. That is exactly what we want: an unknown customer id should not prevent Barbara from being reported as having no orders.

Try the shapes together:

Correlated subqueries and EXISTS are powerful, but nested queries can still become hard to read when each step does real work. Next lesson gives those steps names so long nested queries read from top to bottom.