Semi Joins

Duplicate Right Matches Do Not Multiply

Extra right matches do not duplicate left output rows.

highlighted = computed this step

Duplicate right matches do not multiply

A left row can have more than one matching right row and still appear once.

existence not multiplication\text{existence not multiplication}

Read duplicate matches

There are 5 matching pairs, including 2 extra duplicate-right matches, but only 3 left rows are kept.

kept rows=3\text{kept rows}=3

Semi-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 right matches do not multiply: left tableemp_idnamedept_id1Ada102Ben203Cy204Dee305EliNULL Duplicate right matches do not multiply: right tabledept_iddept_name10ops20eng20platformNULLunknown candidate pairsleftSourcerightSourceleftKeyrightKeymatched00[10][10]yes01[10][20]no02[10][20]no03[10][NULL]no10[20][10]no11[20][20]yes12[20][20]yes13[20][NULL]no20[20][10]no21[20][20]yes22[20][20]yes23[20][NULL]no30[30][10]no31[30][20]no32[30][20]no33[30][NULL]no40[NULL][10]no41[NULL][20]no42[NULL][20]no43[NULL][NULL]no existence per left rowleftSourceleftKeymatchingRightSourcesexistskept0[10][0]yesyes1[20][1, 2]yesyes2[20][1, 2]yesyes3[30][]nono4[NULL][]nono semi join kept-left rowsemp_idnamedept_id1Ada102Ben203Cy20 semi join factsfactvalueleftRowCount5rightRowCount4candidatePairCount20matchingPairCount5leftRowsWithMatch3keptLeftRows3droppedLeftRows2duplicateRightMatches2nullKeyCandidatePairs8modesemirowOrderleft_source_order_once

Once per left row

Once exists is yes, more right matches do not add more left rows.

once per left row\text{once per left row}

Summary

This is the main contrast with an inner join.

not join multiplication\text{not join multiplication}