Set Operations

INTERSECT Keeps Shared Rows

INTERSECT keeps rows present on both sides.

highlighted = computed this step

INTERSECT keeps shared rows

INTERSECT keeps rows that appear on both sides.

shared row identities\text{shared row identities}

Keep common rows

The result keeps 2 shared rows.

shared rows=2\text{shared 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 keeps shared rows: left input bagidtag1red1red2blue3green INTERSECT keeps shared rows: right input bagidtag1red3green3green4gold bag countsrowleftCountrightCountresultCount[1, red]211[2, blue]100[3, green]121[4, gold]010 result rowsidtag1red3green set operation factsfactvalueopintersectleftRowCount4rightRowCount4resultRowCount2distinctResultRows2rejectednorejectionReasonnonerowOrdercanonical

Read both counts

A kept row has a positive count in both input bags.

left and right counts\text{left and right counts}

Summary

Plain INTERSECT returns one copy of each shared row identity.

distinct shared rows\text{distinct shared rows}