SQL Semantics

UNION vs UNION ALL

UNION deduplicates, while UNION ALL preserves bag multiplicity.

highlighted = computed this step

UNION and UNION ALL differ

UNION deduplicates like a set operation, while UNION ALL keeps bag multiplicity. This is the capstone contrast with Book One set algebra. Note: the two rendered outputs have different cardinalities.

union contrast\text{union contrast}

UNION deduplicates

The recomputed UNION row count is 3. Note: duplicates are removed by this operator.

union rows=3\text{union rows}=3

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

UNION union arity 1 rows 3 LeftCourses arity 1 rows 3 RightCourses arity 1 rows 2
union course AI DB OS

UNION ALL keeps duplicates

The recomputed UNION ALL row count is 5. Note: ALL is the signal to preserve multiplicity.

union all rows=5\text{union all rows}=5

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

UNION ALL union_all arity 1 rows 5 LeftCourses arity 1 rows 3 RightCourses arity 1 rows 2
union_all course AI DB DB DB OS

Summary

SQL can move between set-like and bag-preserving behavior depending on the operator. Note: bag and NULL semantics are here; set algebra was Book One.

union summary\text{union summary}