Set Operations

Start With Input Bags

Set operations start from two visible input bags.

highlighted = computed this step

Start with input bags

A set operation starts with two visible input bags.

left bag and right bag\text{left bag and right bag}

Show both inputs

The left bag has 4 rows and the right bag has 4 rows.

left rows=4\text{left 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.

Start with input bags: left input bagidtag1red1red2blue3green Start with input bags: right input bagidtag1red3green3green4gold bag countsrowleftCountrightCountresultCount[1, red]213[2, blue]101[3, green]123[4, gold]011 result rowsidtag1red1red2blue3green1red3green3green4gold set operation factsfactvalueopunion_allleftRowCount4rightRowCount4resultRowCount8distinctResultRows4rejectednorejectionReasonnonerowOrdersource

Read duplicate counts

The count table makes duplicate rows visible before any operation is trusted.

bag counts are visible\text{bag counts are visible}

Summary

This book tracks bag counts and then renders exact result rows.

finite bag transform\text{finite bag transform}