Self Joins

Same Source Table

A self join starts from one table.

highlighted = computed this step

Same source table

A self join starts from one source table.

one source table\text{one source table}

Read source rows

The source table has 4 employee rows.

source rows=4\text{source rows}=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.

Same source table: 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

One table, two roles

The same rows will be read in two roles, not copied by hand.

one table two roles\text{one table two roles}

Summary

The compiler keeps the source table visible before alias pairing.

source first\text{source first}