Set Operations

ALL Uses Counts

INTERSECT ALL and EXCEPT ALL use multiplicities.

highlighted = computed this step

ALL uses counts

The ALL versions use row counts instead of just row identity.

counts matter\text{counts matter}

Compare ALL operations

INTERSECT ALL returns 2 rows; EXCEPT ALL returns 2 rows.

INTERSECT ALL rows=2\text{INTERSECT ALL rows}=2

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

INTERSECT ALL: left input bagidtag1red1red2blue3green INTERSECT ALL: right input bagidtag1red3green3green4gold bag countsrowleftCountrightCountresultCount[1, red]211[2, blue]100[3, green]121[4, gold]010 result rowsidtag1red3green set operation factsfactvalueopintersect_allleftRowCount4rightRowCount4resultRowCount2distinctResultRows2rejectednorejectionReasonnonerowOrdercanonical
EXCEPT ALL: left input bagidtag1red1red2blue3green EXCEPT ALL: right input bagidtag1red3green3green4gold bag countsrowleftCountrightCountresultCount[1, red]211[2, blue]101[3, green]120[4, gold]010 result rowsidtag1red2blue set operation factsfactvalueopexcept_allleftRowCount4rightRowCount4resultRowCount2distinctResultRows2rejectednorejectionReasonnonerowOrdercanonical

Read result counts

The resultCount column shows min-count for INTERSECT ALL and remaining-count for EXCEPT ALL.

count arithmetic\text{count arithmetic}

Summary

The rows remain exact pinned tokens; only their multiplicities change.

bag arithmetic\text{bag arithmetic}