Self Joins

Exclude Self-Pairs

Self-pair exclusion is explicit.

highlighted = computed this step

Exclude self-pairs

Sometimes the same source row should not pair with itself.

exclude same source\text{exclude same source}

Read excluded pairs

The peer example excludes 4 self-pairs and keeps 2 role-match rows.

excluded self pairs=4\text{excluded self pairs}=4

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.

Exclude self-pairs: 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

Still match other rows

Rows with the same role can still match when they are different source rows.

same role different row\text{same role different row}

Summary

Self-pair exclusion is explicit and visible in the candidate table.

visible exclusion\text{visible exclusion}