Correlated subquery.

A correlated subquery uses a column of the outer row — for example WHERE o2.customer_id = o.customer_id. Logically it runs once per outer row with that row's values. That answers questions such as "this customer's largest order" or "above the average of the row's own customer".

Give the outer and inner tables different aliases (o and o2); otherwise a column is compared with itself. A scalar correlated subquery in SELECT returns NULL when the row has no matches (for example a customer without orders).

Note: "once per row" is the logical reading, not a promise about how PostgreSQL executes it internally. A NULL key never matches in a correlated condition: NULL = NULL is not true.

Common mistakes: the same alias on both levels; a missing link condition; expecting 0 instead of NULL when nothing matches.

Small example:

SELECT c.id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_count FROM customers c;