Subqueries

Start With Base Tables

Subqueries compare visible outer and inner tables.

highlighted = computed this step

Start with base tables

A subquery compares one visible table with another visible table.

outer and inner tables\text{outer and inner tables}

Show both tables

The outer table has 3 rows and the inner table has 2 rows.

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.

Start with base tables: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 Start with base tables: inner tablesourceRowcity_id010130 per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows0noneshowbase_row[]1noneshowbase_row[]2noneshowbase_row[] result rowssourceRowidnamecity_id01Ada1012Ben2023Cy30 subquery factsfactvaluemodebaseouterRowCount3innerRowCount2resultRowCount3rejectednorejectionReasonnonenullInInnernorowOrdersource

Read source rows

The sourceRow column keeps row order deterministic and visible.

source order\text{source order}

Summary

The later lessons keep the tables tiny and recompute every kept row.

finite subquery\text{finite subquery}