Self Joins

Duplicate Roles and NULL Manager

Duplicate roles can match while NULL manager keys do not.

highlighted = computed this step

Duplicate roles and NULLs

Duplicate role values can create peer rows, while NULL manager keys create no match.

duplicates and NULLs\text{duplicates and NULLs}

Read both boundaries

Duplicate role rows produce 2 peer rows; NULL-key candidate pairs are 4.

peer rows=2\text{peer rows}=2

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.

Duplicate-role rows: source tableemp_idnamemanager_idrole1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa left_emp alias copyleft_emp.emp_idleft_emp.nameleft_emp.manager_idleft_emp.role1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa right_emp alias copyright_emp.emp_idright_emp.nameright_emp.manager_idright_emp.role1AdaNULLlead2Ben1dev3Cy1dev4Dee2qa candidate alias pairsleftSourcerightSourceleftKeyrightKeyexcludedSelfmatched00leadleadyesno01leaddevnono02leaddevnono03leadqanono10devleadnono11devdevyesno12devdevnoyes13devqanono20devleadnono21devdevnoyes22devdevyesno23devqanono30qaleadnono31qadevnono32qadevnono33qaqayesno self join output rowsleft_emp.emp_idleft_emp.nameleft_emp.manager_idleft_emp.roleright_emp.emp_idright_emp.nameright_emp.manager_idright_emp.role2Ben1dev3Cy1dev3Cy1dev2Ben1dev self join factsfactvaluesourceRowCount4candidatePairCount16matchingPairCount2outputRowCount2excludedSelfPairs4nullKeyCandidatePairs0matchedLeftRows2matchedRightRows2rowOrderleft_alias_source_then_right_alias_source
NULL manager boundary: 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

Qualified output

The output columns keep alias names so the two roles stay readable.

alias qualified output\text{alias qualified output}

Summary

Duplicate values and NULL boundaries are both visible before the final output is trusted.

visible boundaries\text{visible boundaries}