Outer Joins

Duplicate Matches

Multiple right matches produce multiple output rows.

highlighted = computed this step

Duplicate matches

If one left row matches multiple right rows, each match produces an output row.

duplicates produce rows\text{duplicates produce rows}

Read one-to-many output

The first left row has 2 matches, and the join output has 5 rows.

first matches=2\text{first 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.

Duplicate matches: left tablesidname1Ada2Bea3CalNULLNoKey Duplicate matches: 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

No dedup step

The compiler does not collapse duplicate match rows.

bag-style output\text{bag-style output}

Summary

Outer joins still preserve multiple matches.

one match one row\text{one match one row}