Outer Joins

ON Versus WHERE After Join

A post-join filter can drop padded rows.

highlighted = computed this step

ON versus WHERE after join

The ON match builds the outer-join rows first; a WHERE-style filter can drop padded rows afterward.

join first, filter after\text{join first, filter after}

Compare row counts

The outer join has 5 rows before the filter and 3 rows after the right-side not-NULL filter.

after filter=3\text{after filter}=3

Outer-join examples are tiny finite-table transforms; SQL dialect completeness, type coercion, optimizer behavior, execution cost, indexing, and database-product claims are out of scope.

ON versus WHERE after join: left tablesidname1Ada2Bea3CalNULLNoKey ON versus WHERE after join: right tablesidscore1A1A23CNULLGhost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]13NULL[]0 left outer join rowsleft.sidleft.nameright.sidright.score1Ada1A1Ada1A22BeaNULLNULL3Cal3CNULLNoKeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, A]1yes[1, Ada, 1, A2]2yes[2, Bea, NULL, NULL]3yes[3, Cal, 3, C]4yes[NULL, NoKey, NULL, NULL] outer join factsfactvalueleftRowCount4rightRowCount4matchPairCount3unmatchedLeftRows2paddedRows2joinRowCount5filteredRowCount5whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source
WHERE after join: left tablesidname1Ada2Bea3CalNULLNoKey WHERE after join: right tablesidscore1A1A23CNULLGhost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]13NULL[]0 left outer join rowsleft.sidleft.nameright.sidright.score1Ada1A1Ada1A22BeaNULLNULL3Cal3CNULLNoKeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, A]1yes[1, Ada, 1, A2]2no[2, Bea, NULL, NULL]3yes[3, Cal, 3, C]4no[NULL, NoKey, NULL, NULL] outer join factsfactvalueleftRowCount4rightRowCount4matchPairCount3unmatchedLeftRows2paddedRows2joinRowCount5filteredRowCount3whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Read kept flags

The post-join filter rows table marks which joined rows survive the filter.

filter after join\text{filter after join}

Summary

This is a finite filter-after-join caveat, not a full SQL dialect rulebook.

scoped caveat\text{scoped caveat}