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.

UNIONunionarity 1 rows 3LeftCoursesarity 1 rows 3RightCoursesarity 1 rows 2
unioncourseAIDBOS

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 ALLunion_allarity 1 rows 5LeftCoursesarity 1 rows 3RightCoursesarity 1 rows 2
union_allcourseAIDBDBDBOS

NULL rows are still rows

In the NULL example, UNION recomputes to row count 3 with NULL rows 1, while UNION ALL recomputes to row count 5 with NULL rows 2. Note: ALL preserves both duplicate DB rows and duplicate NULL rows.

UNION NULL rows=1,ALL NULL rows=2\text{UNION NULL rows}=1,\quad \text{ALL NULL rows}=2

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

UNION with NULLunionarity 1 rows 3LeftWithNullarity 1 rows 3RightWithNullarity 1 rows 2
unioncourseDBOSNULL
UNION ALL with NULLunion_allarity 1 rows 5LeftWithNullarity 1 rows 3RightWithNullarity 1 rows 2
union_allcourseDBDBOSNULLNULL
UNION NULL countsgroup_byarity 2 rows 3unionarity 1 rows 3
group_bycoursenDB1OS1NULL1
UNION ALL NULL countsgroup_byarity 2 rows 3union_allarity 1 rows 5
group_bycoursenDB2OS1NULL2

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}