Outer Joins

Base Left and Right Tables

A left outer join starts with two visible input tables.

highlighted = computed this step

Base left and right tables

A left outer join starts with a left table and a right table.

two input tables\text{two input tables}

Read the inputs

The left table has 4 rows and the right table has 4 rows.

left rows=4\text{left rows}=4

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.

Base left and right tables: left tablesidname1Ada2Bea3CalNULLNoKey Base left and right tables: 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

Keep both sides visible

The visual keeps both input tables visible before any output row is trusted.

inputs before output\text{inputs before output}

Summary

Outer joins are easiest to read when the input rows stay in view.

visible inputs\text{visible inputs}