Outer Joins

NULL Join Keys Do Not Match

NULL keys do not match, even against NULL.

highlighted = computed this step

NULL join keys do not match

A NULL join key is not equal to another NULL join key in this scoped join model.

NULL does not match NULL\text{NULL does not match NULL}

Read NULL-key facts

The left table has 1 NULL-key row, and NULL-key matches are 0.

NULL matches=0\text{NULL matches}=0

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.

NULL join keys do not match: left tablesidname1Ada2Bea3CalNULLNoKey NULL join keys do not match: 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

Still keep the left row

The NULL-key left row is still kept because the join is left outer.

kept then padded\text{kept then padded}

Summary

NULL-key nonmatching is separate from left-row preservation.

no NULL equality\text{no NULL equality}