Subqueries

EXISTS Keeps Matches

EXISTS keeps rows with at least one inner match.

highlighted = computed this step

EXISTS keeps matches

EXISTS asks whether at least one inner row matches the current outer row.

at least one match\text{at least one match}

Evaluate EXISTS

The result keeps 2 customer rows.

kept rows=2\text{kept rows}=2

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.

EXISTS keeps matches: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 EXISTS keeps matches: inner tablesourceRoworder_idcustomer_id010111102121033 per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows01keepexists[0, 1]12dropnot_exists[]23keepexists[2] result rowssourceRowidnamecity_id01Ada1023Cy30 subquery factsfactvaluemodeexistsouterRowCount3innerRowCount3resultRowCount2rejectednorejectionReasonnonenullInInnernorowOrdersource

Read innerRows

The innerRows list is non-empty for kept rows.

nonempty innerRows\text{nonempty innerRows}

Summary

EXISTS cares about presence, not a copied inner value.

presence check\text{presence check}