Missing Matches

Missing-Match Output

The anti-match output keeps rows with no right match.

highlighted = computed this step

Missing output

The missing-match output is the left rows whose right-side key cell is NULL after the join.

missing output\text{missing output}

Contrast missing and present

The missing filter keeps 3 rows; the present filter keeps 3 matched rows.

missing versus present\text{missing versus present}

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.

Missing-match output: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Missing-match output: 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
Matched rows for contrast: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Matched rows for contrast: right tablepidtask1design1build3test5orphanNULLghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.pidleft.projectright.pidright.task1alpha1design1alpha1build2betaNULLNULL3gamma3test4deltaNULLNULLNULLnokeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, alpha, 1, design]1yes[1, alpha, 1, build]2no[2, beta, NULL, NULL]3yes[3, gamma, 3, test]4no[4, delta, NULL, NULL]5no[NULL, nokey, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount3whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Read the two filters

IS NULL keeps padded rows. IS NOT NULL keeps rows with right-side task values.

opposite filters\text{opposite filters}

Summary

Both filters are post-join filters over the same joined table.

same join, different filter\text{same join, different filter}