Inner Joins

Unmatched Rows Are Dropped

Inner joins do not pad unmatched rows.

highlighted = computed this step

Unmatched rows are dropped

Inner join output has no padded row for an unmatched input row.

no match means no output row\text{no match means no output row}

Read dropped rows

The compiler finds 2 unmatched left rows and 2 unmatched right rows.

unmatched left=2\text{unmatched left}=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.

Unmatched rows are dropped: left tablesidname1Ada2Bea3CalNULLNoKey Unmatched rows are dropped: right tablesidscore1A1A23C4DNULLGhost candidate row pairsleftSourcerightSourceleftKeyrightKeymatched00[1][1]yes01[1][1]yes02[1][3]no03[1][4]no04[1][NULL]no10[2][1]no11[2][1]no12[2][3]no13[2][4]no14[2][NULL]no20[3][1]no21[3][1]no22[3][3]yes23[3][4]no24[3][NULL]no30[NULL][1]no31[NULL][1]no32[NULL][3]no33[NULL][4]no34[NULL][NULL]no inner join rowsleft.sidleft.nameright.sidright.score1Ada1A1Ada1A23Cal3C inner join factsfactvalueleftRowCount4rightRowCount5candidatePairCount20matchingPairCount3unmatchedLeftRows2unmatchedRightRows2joinRowCount3keyColumnCount1nullKeyMatches0rowOrderleft_source_then_right_source

Compare with outer join

Unlike a left outer join, this inner join does not add NULL padding.

no padding\text{no padding}

Summary

Inner means only matched pairs survive.

unmatched dropped\text{unmatched dropped}