NATURAL JOIN

NULL Common Values Do Not Match

NULL common values do not match.

highlighted = computed this step

NULL common values do not match

A NULL in a common column makes that row-pair check fail.

NULL not equal\text{NULL not equal}

Read NULL candidates

The candidate grid has 8 pairs with at least one NULL common value.

NULL pairs=8\text{NULL pairs}=8

NATURAL JOIN examples are tiny finite-table transforms; SQL dialect completeness, type coercion, optimizer behavior, execution cost, indexing, output ordering guarantees, and database-product claims are out of scope.

NULL common values do not match: left tableorder_idcustomer_idnote110new220rush320gift4NULLdraft NULL common values do not match: right tablecustomer_idnametier10Adagold20Bensilver20Bennysilver30CybronzeNULLUnknownnone automatic common-column discoverycommonColumnleftIndexrightIndexcustomer_id10 candidate row comparisonsleftSourcerightSourcechecksmatched00[customer_id: 10 = 10 -> yes]yes01[customer_id: 10 = 20 -> no]no02[customer_id: 10 = 20 -> no]no03[customer_id: 10 = 30 -> no]no04[customer_id: 10 = NULL -> no]no10[customer_id: 20 = 10 -> no]no11[customer_id: 20 = 20 -> yes]yes12[customer_id: 20 = 20 -> yes]yes13[customer_id: 20 = 30 -> no]no14[customer_id: 20 = NULL -> no]no20[customer_id: 20 = 10 -> no]no21[customer_id: 20 = 20 -> yes]yes22[customer_id: 20 = 20 -> yes]yes23[customer_id: 20 = 30 -> no]no24[customer_id: 20 = NULL -> no]no30[customer_id: NULL = 10 -> no]no31[customer_id: NULL = 20 -> no]no32[customer_id: NULL = 20 -> no]no33[customer_id: NULL = 30 -> no]no34[customer_id: NULL = NULL -> no]no NATURAL JOIN output rowscustomer_idleft.order_idleft.noteright.nameright.tier101newAdagold202rushBensilver202rushBennysilver203giftBensilver203giftBennysilver NATURAL JOIN factsfactvalueleftRowCount4rightRowCount5commonColumns[customer_id]commonColumnCount1candidatePairCount20matchingPairCount5matchedLeftRows3duplicateMatchRows2nullCommonCandidatePairs8fallbackModeall_common_columnsfirstCommonColumnOnlyMatchCount0narrowedByExtraCommonColumns0outputColumnCount5rowOrderleft_source_then_right_source

Even NULL with NULL fails

NULL does not become an equality match in this finite SQL-style model.

NULL pair drops\text{NULL pair drops}

Summary

NULL rows are visible in candidates before they disappear from output.

visible non match\text{visible non match}