Subqueries

NOT EXISTS Keeps Anti-Matches

NOT EXISTS keeps rows with no inner match.

highlighted = computed this step

NOT EXISTS keeps anti-matches

NOT EXISTS keeps an outer row when no inner row matches it.

no match means keep\text{no match means keep}

Evaluate NOT EXISTS

The result keeps 1 row.

kept rows=1\text{kept rows}=1

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.

NOT EXISTS keeps anti-matches: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 NOT EXISTS keeps anti-matches: inner tablesourceRoworder_idcustomer_id010111102121033 per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows01dropexists[0, 1]12keepnot_exists[]23dropexists[2] result rowssourceRowidnamecity_id12Ben20 subquery factsfactvaluemodenot_existsouterRowCount3innerRowCount3resultRowCount1rejectednorejectionReasonnonenullInInnernorowOrdersource

Read empty matches

The kept row has an empty innerRows list.

anti-match\text{anti-match}

Summary

This is the finite anti-match shape used by many practical queries.

visible anti-match\text{visible anti-match}