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.
Honest by construction: source table Honest by construction: source table emp_id name manager_id role 1 Ada NULL lead 2 Ben 1 dev 3 Cy 1 dev 4 Dee 2 qa employee alias copy employee alias copy employee.emp_id employee.name employee.manager_id employee.role 1 Ada NULL lead 2 Ben 1 dev 3 Cy 1 dev 4 Dee 2 qa manager alias copy manager alias copy manager.emp_id manager.name manager.manager_id manager.role 1 Ada NULL lead 2 Ben 1 dev 3 Cy 1 dev 4 Dee 2 qa candidate alias pairs candidate alias pairs leftSource rightSource leftKey rightKey excludedSelf matched 0 0 NULL 1 no no 0 1 NULL 2 no no 0 2 NULL 3 no no 0 3 NULL 4 no no 1 0 1 1 no yes 1 1 1 2 no no 1 2 1 3 no no 1 3 1 4 no no 2 0 1 1 no yes 2 1 1 2 no no 2 2 1 3 no no 2 3 1 4 no no 3 0 2 1 no no 3 1 2 2 no yes 3 2 2 3 no no 3 3 2 4 no no self join output rows self join output rows employee.emp_id employee.name employee.manager_id employee.role manager.emp_id manager.name manager.manager_id manager.role 2 Ben 1 dev 1 Ada NULL lead 3 Cy 1 dev 1 Ada NULL lead 4 Dee 2 qa 2 Ben 1 dev self join facts self join facts fact value sourceRowCount 4 candidatePairCount 16 matchingPairCount 3 outputRowCount 3 excludedSelfPairs 0 nullKeyCandidatePairs 4 matchedLeftRows 3 matchedRightRows 2 rowOrder left_alias_source_then_right_alias_source