Join Multiplication

Duplicate Keys Multiply

Duplicate keys can multiply output rows.

highlighted = computed this step

Duplicate keys multiply

Duplicate keys on both sides can multiply into several output rows.

duplicates multiply\text{duplicates multiply}

Read duplicate output

Duplicate keys create 5 matched pairs and 5 output rows.

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

Inner-join examples are tiny finite-table transforms; SQL dialect completeness, type coercion, optimizer behavior, execution cost, indexing, and database-product claims are out of scope.

Duplicate keys multiply: left tablecustomer_idbadge1gold1vip2new Duplicate keys multiply: right tablecustomer_idvisit1mon1tue2wed candidate row pairsleftSourcerightSourceleftKeyrightKeymatched00[1][1]yes01[1][1]yes02[1][2]no10[1][1]yes11[1][1]yes12[1][2]no20[2][1]no21[2][1]no22[2][2]yes inner join rowsleft.customer_idleft.badgeright.customer_idright.visit1gold1mon1gold1tue1vip1mon1vip1tue2new2wed inner join factsfactvalueleftRowCount3rightRowCount3candidatePairCount9matchingPairCount5unmatchedLeftRows0unmatchedRightRows0joinRowCount5keyColumnCount1nullKeyMatches0rowOrderleft_source_then_right_source

Bag-style output

The compiler does not deduplicate the matched pairs.

no dedup step\text{no dedup step}

Summary

Duplicate matching keys are visible in the row count.

visible multiplication\text{visible multiplication}