Subqueries

Scalar Needs One Row

A scalar subquery needs exactly one inner row.

highlighted = computed this step

Scalar needs one row

A scalar subquery may provide one value when the inner result has exactly one row.

one row gives one value\text{one row gives one value}

Attach the scalar

The scalar value is US, and result rows are 3.

result rows=3\text{result rows}=3

Subquery examples are tiny finite table evaluations; SQL dialects, optimizer rewrites, NULL truth tables beyond the explicit NOT IN caveat, performance, and product behavior are out of scope.

Scalar needs one row: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 Scalar needs one row: inner tablesourceRownamevalue0regionUS per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows0nonekeepscalar_attached[0]1nonekeepscalar_attached[0]2nonekeepscalar_attached[0] result rowssourceRowidnamecity_idscalar01Ada10US12Ben20US23Cy30US subquery factsfactvaluemodescalarouterRowCount3innerRowCount1resultRowCount3rejectednorejectionReasonnonenullInInnernorowOrdersource

Read the scalar column

The result table shows the scalar value attached to each outer row.

scalar attached\text{scalar attached}

Summary

Only the exactly-one-row case is accepted here.

exactly one\text{exactly one}