Inner Joins

Composite Key Join

A join can require more than one key column.

highlighted = computed this step

Composite key join

A composite key join checks more than one column at the same time.

sid and term\text{sid and term}

Read composite matches

The compiler checks 2 key columns and produces 2 output rows.

key columns=2\text{key columns}=2

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.

Composite key join: left tablesidtermname1fallAda1springAda2fallBea Composite key join: right tablesidtermgrade1fallA1springB2springC candidate row pairsleftSourcerightSourceleftKeyrightKeymatched00[1, fall][1, fall]yes01[1, fall][1, spring]no02[1, fall][2, spring]no10[1, spring][1, fall]no11[1, spring][1, spring]yes12[1, spring][2, spring]no20[2, fall][1, fall]no21[2, fall][1, spring]no22[2, fall][2, spring]no inner join rowsleft.sidleft.termleft.nameright.sidright.termright.grade1fallAda1fallA1springAda1springB inner join factsfactvalueleftRowCount3rightRowCount3candidatePairCount9matchingPairCount2unmatchedLeftRows1unmatchedRightRows1joinRowCount2keyColumnCount2nullKeyMatches0rowOrderleft_source_then_right_source

All key parts must match

A row with the right sid but the wrong term is not a match.

all key parts\text{all key parts}

Summary

Composite joins still use the same finite candidate-pair check.

same rule, more columns\text{same rule, more columns}