NATURAL JOIN

Matched Rows

Passing row pairs become output rows.

highlighted = computed this step

Matched rows

Each candidate row pair with matching common values becomes one output row.

matched output\text{matched output}

Read matched rows

The natural join emits 5 output rows.

output rows=5\text{output rows}=5

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.

Matched rows: left tableorder_idcustomer_idnote110new220rush320gift4NULLdraft Matched rows: 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 hidden distinct

Duplicate matches stay visible as separate rows.

bag rows remain\text{bag rows remain}

Summary

Matching decides row survival; it does not deduplicate the result.

no implicit DISTINCT\text{no implicit DISTINCT}