Set Operations

NULL Rows Have Identity

NULL positions participate in row identity for set operations.

highlighted = computed this step

NULL rows have identity

For set operations, this tiny model treats two rows with NULL in the same positions as the same row identity.

NULL position matters\text{NULL position matters}

Deduplicate NULL rows

The result has 3 rows after deduplication.

result rows=3\text{result rows}=3

Set-operation examples are tiny finite bag transforms; SQL dialect details, type coercion, collations, ordering clauses, performance, and product behavior are out of scope.

NULL rows have identity: left input bagidtag1NULL1NULL2blue NULL rows have identity: right input bagidtag1NULL3gold bag countsrowleftCountrightCountresultCount[1, NULL]211[2, blue]101[3, gold]011 result rowsidtag1NULL2blue3gold set operation factsfactvalueopunionleftRowCount3rightRowCount2resultRowCount3distinctResultRows3rejectednorejectionReasonnonerowOrdercanonical

Read the count table

The duplicate NULL row is counted, then UNION keeps one copy.

NULL row counted\text{NULL row counted}

Summary

This is row identity for set operations, not WHERE predicate truth.

set row identity\text{set row identity}