SQL Querying

Subqueries and Correlation

A subquery is a query expression nested where a value, row, table, or existence test is needed. A correlated subquery refers to the current row of an outer query, creating a useful per-row mental model even when the optimizer decorrelates it into a join-like plan.

A scalar subquery must produce at most one row and one column; more than one row is an error rather than an arbitrary choice.
A table subquery can feed FROM, membership tests, or another relational expression and should expose clear column names.
Correlation creates a parameterized inner query.
Optimizers may decorrelate equivalent subqueries.
Correlation scope must be explicit: qualified aliases show which query level supplies each column.
A join is not inherently faster than a subquery; compare equivalent semantics and inspect the actual plan for the target workload.

A parameterized-query mental model

Correlated existence test

Correlated scalar aggregate

SELECT c.customer_id,
       c.name,
       (SELECT COUNT(*)
        FROM orders AS o
        WHERE o.customer_id = c.customer_id
          AND o.status = 'PAID') AS paid_order_count
FROM customers AS c;

For customer 7, bind c.customer_id = 7; the inner aggregate counts only customer 7's paid orders and returns one scalar even when the count is zero. Then repeat conceptually for customer 8. The engine may instead scan or index orders once and join grouped counts.

Subquery result shapes
Shape	Typical position	Contract
Scalar	`SELECT`, comparison, assignment	Zero rows generally yields null; more than one row is an error
Single column	`IN`, quantified comparison	Any number of values; nulls affect three-valued logic
Table	`FROM`, CTE input	Rows and named columns compose with other table expressions
Existence	`EXISTS` / `NOT EXISTS`	Only emptiness matters; projected values are irrelevant