Set Operations

UNION Deduplicates

UNION keeps one copy of each row identity.

highlighted = computed this step

UNION deduplicates

UNION keeps each row identity once.

one copy per row identity\text{one copy per row identity}

Remove duplicate rows

The result has 4 rows and 4 distinct row identities.

result rows=4\text{result rows}=4

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

UNION deduplicates: left input bagidtag1red1red2blue3green UNION deduplicates: right input bagidtag1red3green3green4gold bag countsrowleftCountrightCountresultCount[1, red]211[2, blue]101[3, green]121[4, gold]011 result rowsidtag1red2blue3green4gold set operation factsfactvalueopunionleftRowCount4rightRowCount4resultRowCount4distinctResultRows4rejectednorejectionReasonnonerowOrdercanonical

Read canonical order

Distinct set results render in deterministic canonical row order.

canonical order\text{canonical order}

Summary

UNION is not UNION ALL; duplicates are collapsed.

deduped result\text{deduped result}