Self Joins

Candidate Alias Pairs

Alias rows are paired before matching.

highlighted = computed this step

Candidate alias pairs

The finite self join compares each left-alias row with each right-alias row.

alias pair grid\text{alias pair grid}

Read candidate count

The candidate table has 16 alias pairs.

candidate pairs=16\text{candidate pairs}=16

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.

Candidate alias pairs: 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

Source order

Pairs render in left-alias source order, then right-alias source order.

left alias then right alias\text{left alias then right alias}

Summary

Candidate pairs are possible rows, not output rows yet.

possible pairs\text{possible pairs}