Self Joins

Equality Chooses Matches

Equal non-NULL keys become output rows.

highlighted = computed this step

Equality chooses matches

A candidate pair becomes output when the left key equals the right key and neither key is NULL.

equal non NULL keys\text{equal non NULL keys}

Read matches

The compiler finds 3 matching pairs and emits 3 output rows.

matched rows=3\text{matched rows}=3

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

Equality chooses matches: source tableemp_idnamemanager_idrole1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa employee alias copyemployee.emp_idemployee.nameemployee.manager_idemployee.role1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa manager alias copymanager.emp_idmanager.namemanager.manager_idmanager.role1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa candidate alias pairsleftSourcerightSourceleftKeyrightKeyexcludedSelfmatched00NULL1nono01NULL2nono02NULL3nono03NULL4nono1011noyes1112nono1213nono1314nono2011noyes2112nono2213nono2314nono3021nono3122noyes3223nono3324nono self join output rowsemployee.emp_idemployee.nameemployee.manager_idemployee.rolemanager.emp_idmanager.namemanager.manager_idmanager.role2Ben1dev1AdaNULLlead3Cy1dev1AdaNULLlead4Dee2qa2Ben1dev self join factsfactvaluesourceRowCount4candidatePairCount16matchingPairCount3outputRowCount3excludedSelfPairs0nullKeyCandidatePairs4matchedLeftRows3matchedRightRows2rowOrderleft_alias_source_then_right_alias_source

No hidden matching

The candidate table marks each pair yes or no.

matched flag visible\text{matched flag visible}

Summary

The equality check is recomputed from pinned cells.

recomputed equality\text{recomputed equality}