SQL Extensions

NULL Join Behavior

NULL join keys do not match, even against another NULL key.

highlighted = computed this step

NULL join behavior

SQL equality does not match NULL to NULL in this surface. The NULL-key left row is still kept because the join is left outer.

NULL key does not match\text{NULL key does not match}

NULL stays unmatched

The NULL-key left row has 0 matches, while the non-NULL key has 1 match. The output has 2 rows.

NULL matches=0,non NULL matches=1\text{NULL matches}=0,\quad \text{non NULL matches}=1

SQL extensions are rendered as tiny finite-table semantics; SQL dialect completeness, optimizer behavior, execution cost, full DDL behavior, and database-product claims are out of scope.

compiled summaryfactvaluerowCount2arity4rootKindleft_outer_join NULL join behaviorLefts.sidLefts.labelRights.sidRights.labelxleft_xxright_xNULLleft_nullNULLNULL left-row match countsleftRowmatchCount0110

Summary

NULL non-match is a finite equality rule here. Note: no three-valued predicate surface beyond the modeled operation is added.

NULL does not equal NULL here\text{NULL does not equal NULL here}