NATURAL JOIN

An Extra Shared Name Narrows Matches

An extra shared column name can narrow NATURAL JOIN results.

highlighted = computed this step

An extra shared name narrows matches

NATURAL JOIN can surprise you when another same-named column appears on both sides.

extra shared name\text{extra shared name}

Read the narrowed result

Using only the first common column would match 5 pairs; NATURAL JOIN matches 3, narrowing the output by 2 pairs.

narrowed by=2\text{narrowed by}=2

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.

An extra shared name narrows matches: left tableorder_idcustomer_idstatus110open220open320closed An extra shared name narrows matches: right tablecustomer_idstatusname10openAda20closedBen20openBenny automatic common-column discoverycommonColumnleftIndexrightIndexcustomer_id10status21 candidate row comparisonsleftSourcerightSourcechecksmatched00[customer_id: 10 = 10 -> yes, status: open = open -> yes]yes01[customer_id: 10 = 20 -> no, status: open = closed -> no]no02[customer_id: 10 = 20 -> no, status: open = open -> yes]no10[customer_id: 20 = 10 -> no, status: open = open -> yes]no11[customer_id: 20 = 20 -> yes, status: open = closed -> no]no12[customer_id: 20 = 20 -> yes, status: open = open -> yes]yes20[customer_id: 20 = 10 -> no, status: closed = open -> no]no21[customer_id: 20 = 20 -> yes, status: closed = closed -> yes]yes22[customer_id: 20 = 20 -> yes, status: closed = open -> no]no NATURAL JOIN output rowscustomer_idstatusleft.order_idright.name10open1Ada20open2Benny20closed3Ben NATURAL JOIN factsfactvalueleftRowCount3rightRowCount3commonColumns[customer_id, status]commonColumnCount2candidatePairCount9matchingPairCount3matchedLeftRows3duplicateMatchRows0nullCommonCandidatePairs0fallbackModeall_common_columnsfirstCommonColumnOnlyMatchCount5narrowedByExtraCommonColumns2outputColumnCount4rowOrderleft_source_then_right_source

Guarded facts

Wrong expected facts, derived sidecars, duplicate columns, bools, floats, None, and unsafe cells fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite NATURAL JOIN only: discovered common columns, cross fallback, NULL non-matches, and the extra-name footgun.

scoped NATURAL JOIN model\text{scoped NATURAL JOIN model}