NATURAL JOIN

Automatic Same-Name Discovery

Same-named columns are discovered automatically.

highlighted = computed this step

Automatic same-name discovery

NATURAL JOIN finds every column name that appears on both sides.

common columns\text{common columns}

Read discovered columns

This example discovers 1 common column: customer id.

common count=1\text{common count}=1

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.

Automatic same-name discovery: left tableorder_idcustomer_idnote110new220rush320gift4NULLdraft Automatic same-name discovery: 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

The name matters

The shared name becomes the matching rule.

same name becomes condition\text{same name becomes condition}

Summary

NATURAL JOIN writes the condition from column names, not from a typed ON clause.

name driven match\text{name driven match}