Subqueries

Correlate One Row at a Time

Correlated subqueries evaluate per outer row.

highlighted = computed this step

Correlate one row at a time

A correlated subquery is evaluated for each visible outer row in this tiny model.

per-row finite evaluation\text{per-row finite evaluation}

Show guarded correlation

The compiler evaluates 3 outer rows and keeps 2.

outer rows=3\text{outer 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.

Correlate one row at a time: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 Correlate one row at a time: inner tablesourceRoworder_idcustomer_id010111102121033 per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows01keepexists[0, 1]12dropnot_exists[]23keepexists[2] result rowssourceRowidnamecity_id01Ada1023Cy30 subquery factsfactvaluemodeexistsouterRowCount3innerRowCount3resultRowCount2rejectednorejectionReasonnonenullInInnernorowOrdersource

Try to tamper

Wrong expected facts, derived sidecars, missing columns, malformed scalar specs, bools, and floats fail closed.

fail closed before render\text{fail closed before render}

Summary

Expected subquery facts are assertions only; compiler output is the source of truth.

honest subquery facts\text{honest subquery facts}