Semi Joins

NULL Key and NOT EXISTS

NULL keys do not match, and anti-semi keeps no-match rows.

highlighted = computed this step

NULL key and NOT EXISTS

NULL keys do not match in this finite equality model; anti-semi keeps left rows with no match.

NULL does not match\text{NULL does not match}

Read the contrast

The semi-join grid has 8 NULL-key candidate pairs. The anti-semi contrast keeps 2 no-match left rows.

anti kept rows=2\text{anti kept rows}=2

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.

NULL key boundary: left tableemp_idnamedept_id1Ada102Ben203Cy204Dee305EliNULL NULL key boundary: 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
Anti-semi NOT EXISTS contrast: left tableemp_idnamedept_id1Ada102Ben203Cy204Dee305EliNULL Anti-semi NOT EXISTS contrast: 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]yesno1[20][1, 2]yesno2[20][1, 2]yesno3[30][]noyes4[NULL][]noyes anti-semi kept-left rowsemp_idnamedept_id4Dee305EliNULL semi join factsfactvalueleftRowCount5rightRowCount4candidatePairCount20matchingPairCount5leftRowsWithMatch3keptLeftRows2droppedLeftRows3duplicateRightMatches2nullKeyCandidatePairs8modeantirowOrderleft_source_order_once

NOT EXISTS contrast

The anti-semi mode is the finite NOT EXISTS-style contrast: keep left rows where exists is no.

keep when exists is no\text{keep when exists is no}

Summary

The NULL row is visible as no match, not silently treated as equal.

visible NULL boundary\text{visible NULL boundary}