Self Joins

Honest by Construction

Expected self-join facts are assertions only.

highlighted = computed this step

Honest by construction

Expected self-join facts are assertions only.

compiler is source of truth\text{compiler is source of truth}

Render checked rows

The guarded self join renders 3 output rows after recomputation.

checked rows=3\text{checked rows}=3

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 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

Try to tamper

Wrong expected facts, derived sidecars, repeated aliases, missing keys, duplicate columns, bools, floats, and unsafe cells fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite self joins with two aliases only.

scoped self join model\text{scoped self join model}