Subqueries

IN Keeps Matching Rows

IN keeps outer rows whose value appears inside.

highlighted = computed this step

IN keeps matching rows

IN keeps an outer row when its value appears in the inner result.

outer value in inner values\text{outer value in inner values}

Evaluate IN

The result keeps 2 outer 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.

IN keeps matching rows: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 IN keeps matching rows: inner tablesourceRowcity_id010130 per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows010keepmatch[0]120dropno_match[]230keepmatch[1] result rowssourceRowidnamecity_id01Ada1023Cy30 subquery factsfactvaluemodeinouterRowCount3innerRowCount2resultRowCount2rejectednorejectionReasonnonenullInInnernorowOrdersource

Read per-row matches

The per-row table shows which inner rows matched each outer row.

semijoin view\text{semijoin view}

Summary

This is a finite semijoin-style check, not an optimizer claim.

result from matches\text{result from matches}