Missing Matches

Matched Rows Stay Out

Matched rows do not pass the missing filter.

highlighted = computed this step

Matched rows stay out

A left row with right-side matches is not missing, even if it has more than one match.

matched means not missing\text{matched means not missing}

Read duplicate matches

The first left row has 2 matches, while the missing output still has 3 rows.

first row matches=2\text{first row matches}=2

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.

Matched rows stay out: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Matched rows stay out: right tablepidtask1design1build3test5orphanNULLghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.pidleft.projectright.pidright.task1alpha1design1alpha1build2betaNULLNULL3gamma3test4deltaNULLNULLNULLnokeyNULLNULL post-join filter rowsjoinSourcekeptvalues0no[1, alpha, 1, design]1no[1, alpha, 1, build]2yes[2, beta, NULL, NULL]3no[3, gamma, 3, test]4yes[4, delta, NULL, NULL]5yes[NULL, nokey, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount3whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Do not count matched duplicates

Multiple matches create multiple joined rows, but none of those rows pass the right-side IS NULL test.

matched rows dropped\text{matched rows dropped}

Summary

The anti-match question is about having no right match, not about having one or many.

zero matches only\text{zero matches only}