Self Joins

Left and Right Aliases

Two aliases give one table two roles.

highlighted = computed this step

Left and right aliases

A self join gives the same table two alias names so each side is readable.

two aliases\text{two aliases}

Read alias copies

The output shape has 8 qualified alias columns.

qualified columns=8\text{qualified columns}=8

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.

Left and right aliases: 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

Aliases label roles

Here employee is the left alias, and manager is the right alias.

employee and manager\text{employee and manager}

Summary

Aliases keep the two roles separate even though the source rows are the same.

alias roles\text{alias roles}