In this lesson, you will learn how the mathematical rules of sets determine whether your database considers two rows to be identical or distinct.
The Mathematical Foundation: Sets vs. Bags
In strict relational algebra, a relation is a set. In mathematics, a set is a collection of unique elements; if you add the same item to a set twice, the set remains unchanged. However, practical database management systems (DBMS) often treat data as a multiset (or "bag").
A multiset allows duplicate rows. When you query a table, the database does not automatically remove duplicates unless you explicitly instruct it to. This distinction is vital for how we define "equality" between results.
When are two sets equal?
In relational algebra, two sets are considered equal if and only if they contain exactly the same members, regardless of the order in which they appear.
Consider a table Colors containing:
| Color |
|---|
| Red |
| Blue |
If we perform a projection to get a list of colors, the mathematical set is {Red, Blue}. If we add another Red row, our "bag" becomes {Red, Blue, Red}, but our "set" remains {Red, Blue}.
SQL and the DISTINCT keyword
Because SQL operates on bags to maintain performance—checking for uniqueness in every operation would be computationally expensive—we use the DISTINCT keyword to force the engine to behave like a mathematical set.
Compare these two operations:
-- Returns a bag (may include duplicates)
SELECT Color FROM Colors;
-- Returns a set (removes duplicates to ensure unique membership)
SELECT DISTINCT Color FROM Colors;
When you use DISTINCT, the database engine performs a heavy sort or hash operation to identify and collapse duplicates. Without it, the database treats the rows as distinct instances—even if their data is identical—because it views the query output as a bag of tuples rather than a pure set. Understanding this ensures you know exactly why your query might return more rows than expected when you aren't using set-based filtering.
Key Takeaway: While sets define mathematical uniqueness by ignoring duplicates, SQL defaults to bags to save processing time, requiring the DISTINCT keyword to enforce set-equality logic.