Missing Matches

Right Rows That May Match

Right rows are possible evidence for each left key.

highlighted = computed this step

Right rows that may match

The right table provides possible matches for each left key.

right rows checked\text{right rows checked}

Read matches

The right table has 5 rows, and the ON-style key match finds 3 matching pairs.

matched pairs=3\text{matched pairs}=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.

Right rows that may match: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Right rows that may match: 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]2yes[2, beta, NULL, NULL]3yes[3, gamma, 3, test]4yes[4, delta, NULL, NULL]5yes[NULL, nokey, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount6whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Only key equality matches

Rows with a different key, or NULL keys, do not attach as matches in this finite model.

non NULL key equality\text{non NULL key equality}

Summary

The match table shows which left rows already have right-side evidence.

visible matches\text{visible matches}