SQL Semantics

Relation vs Bag

A SQL table is a bag, so duplicate rows are allowed.

highlighted = computed this step

A SQL table is a bag

A SQL table can keep duplicate rows instead of rejecting them. That is the first sharp edge after Book One set algebra. Note: the rendered table is the source of row details.

bag table\text{bag table}

Duplicate rows are kept

The recomputed row count is 5 and the recomputed arity is 3. Note: the duplicate appears because bag semantics preserve multiplicity.

rows=5,arity=3\text{rows}=5,\quad \text{arity}=3

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

Grades bagGradesarity 3 rows 5
Gradessidcoursegrades1DB90s1DB90s2DBNULLs2OS80s3OS70

A duplicate changes the count

When the same columns are deduplicated, the recomputed row count is 4 instead of 5. Note: the difference is multiplicity, not another column or hidden attribute.

bag rows=5,deduped rows=4\text{bag rows}=5,\quad \text{deduped rows}=4

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

Same rows after DISTINCTdistinct sid,course,gradearity 3 rows 4Gradesarity 3 rows 5
select_distinctsidcoursegrades1DB90s2DBNULLs2OS80s3OS70

Summary

SQL tables are bags unless an operation says otherwise. Note: this book owns bag and NULL behavior; set algebra was Book One.

bag boundary\text{bag boundary}