NATURAL JOIN

Left and Right Inputs

NATURAL JOIN starts from two input tables.

highlighted = computed this step

Left and right inputs

NATURAL JOIN starts with two ordinary input tables.

two input tables\text{two input tables}

Read the tables

The left table has 4 rows; the right table has 5 rows.

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

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.

Left and right inputs: left tableorder_idcustomer_idnote110new220rush320gift4NULLdraft Left and right inputs: 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

No key is typed

Unlike JOIN USING, this request does not name a key column.

key discovered automatically\text{key discovered automatically}

Summary

The compiler will discover the same-named columns before matching rows.

discover first\text{discover first}