Data Modeling
Duplicate rows survive ordinary SQL queries because SQL commonly uses bag semantics by default: a query result can contain multiple copies of the same visible row. Set semantics would collapse identical tuples automatically. SQL usually does not do that unless you use DISTINCT, aggregate to a grain, or enforce uniqueness with keys.
Why duplicate rows survive ordinary SQL queries
The shortest explanation is this: relational theory often describes relations as sets of tuples, but practical SQL query results are usually bags, also called multisets. In a bag, the same row value can appear twice, three times, or more.
This matters because many SQL operations preserve multiplicity. A filter does not normally ask whether a matching row has already appeared. A projection does not normally collapse rows that become identical after you select fewer columns. A join returns every pair of rows that satisfies the join condition, even when those pairs produce repeated-looking output.
That is the core difference in set semantics versus bag semantics in SQL. Under set semantics, duplicates disappear as part of the model. Under bag semantics, duplicates are data unless the query says otherwise.
In ordinary SQL, duplicate-looking rows are not automatically mistakes. They are often evidence that the query result is still at a finer grain than the visible columns suggest.
A small hypothetical dataset with visible duplicates
Use a tiny order-line dataset to demonstrate the issue. The table has a declared line-level grain: one row per order line. The line_id is the candidate key. The same customer can buy the same SKU on more than one line, and that is not automatically an error.
Now look at what happens when a query hides the key. If you select only customer_id and sku, two different line facts can become visually identical. SQL still has two input rows, so the ordinary result keeps two output rows.
| line_id | order_id | customer_id | sku | qty | Meaning |
|---|---|---|---|---|---|
| 1 | 100 | A | pen | 1 | One order line |
| 2 | 100 | A | pen | 1 | A second order line with the same customer and SKU |
| 3 | 101 | A | notebook | 1 | A different SKU for the same customer |
| 4 | 102 | B | pen | 1 | A pen line for another customer |
Projection can make distinct facts look identical
Suppose the query is: SELECT customer_id, sku FROM order_lines. This query removes line_id, order_id, and qty from the visible result. Rows 1 and 2 were distinct line facts, but after the projection they both appear as customer A and SKU pen.
Under pure set-style thinking, you might expect only one A, pen row. Under ordinary SQL bag semantics, both rows survive because the query did not ask SQL to eliminate duplicates.
If the analyst intended to ask which customer-SKU pairs exist, then SELECT DISTINCT customer_id, sku FROM order_lines is closer to the question. If the analyst intended to count line activity, then the duplicate-looking rows are not duplicates at all; they are separate facts at a finer grain.
| Query idea | Ordinary bag result | Set-style result |
|---|---|---|
| Select customer_id and sku from order_lines | A, pen appears twice because two source rows produced it | A, pen appears once because duplicate tuples collapse |
| Select DISTINCT customer_id and sku from order_lines | A, pen appears once because DISTINCT requests duplicate elimination | A, pen appears once |
| Group by customer_id and sku and count rows | A, pen appears once with count 2 | A, pen appears once with an aggregate count |
Filters keep every matching row, not one row per value
A WHERE clause reduces rows by a predicate. It does not normally convert a bag into a set.
For example, SELECT customer_id, sku FROM order_lines WHERE sku = 'pen' still returns both A, pen rows because both source lines match the predicate. The filter answers which line-level facts match. It does not answer which customer-SKU combinations are unique.
This is a common source of dashboard confusion. A filter can be correct and still leave duplicate-looking rows because the result grain is still inherited from the source rows.
Joins can multiply rows even when each table is valid
Joins are where bag semantics become most visible. A join returns combinations of rows that satisfy the join condition. If one order has two line rows and two shipment rows, an order-level join between lines and shipments produces four matching combinations for that order.
This does not require bad data. It can happen when two valid one-to-many relationships meet at the same parent key. The line table is many rows per order. The shipment table is also many rows per order. Joining them directly creates a many-to-many result at the order_id level.
The practical lesson is to identify the grain before the join. If you need order-level metrics, aggregate each side to one row per order before joining. If you need customer-level metrics from independent fact tables, aggregate each fact table to the shared dimensional grain before combining the results.
If two tables are both many rows per business key, joining them on that key can multiply facts. Aggregate to the intended grain before combining independent facts.
| order_id | line rows | shipment rows | Rows after joining lines to shipments on order_id |
|---|---|---|---|
| 100 | 2 | 2 | 4 |
| 101 | 1 | 1 | 1 |
| 102 | 1 | 0 | 0 for an inner join, 1 with null shipment fields for a left join |
Keys prevent some duplicates, but not every duplicate-looking result
Keys are still essential. A primary key or unique constraint can prevent two rows from having the same key value. That is how the table declares what makes a row identifiable.
But a key on line_id does not prevent repeated customer_id and sku values. Those repeated values may be perfectly valid because they describe different line facts. This is why duplicate diagnosis should start with the grain, not with the visible columns alone.
Ask two questions separately: are there duplicate rows at the table's declared key, and are there repeated values after the query hides some columns? The first question is a data integrity issue. The second is often a query semantics issue.
When to use DISTINCT, GROUP BY, or a model change
DISTINCT is useful when the business question is truly about unique combinations of the selected columns. It is not a general repair tool for uncertain joins or unclear grain.
GROUP BY is useful when the desired result has an aggregate grain, such as one row per customer, one row per order, or one row per day and SKU. It makes the target grain explicit and lets you choose how measures should be combined.
A model change is the better answer when the data allows impossible facts. If a table is supposed to have one row per order but contains multiple rows per order, the fix is not to add DISTINCT forever. The fix is to define and enforce the right key, or to remodel the table so the repeating facts live at their real grain.
Do not use DISTINCT until you can say what duplicate means. Is it duplicate at the key, duplicate after projection, or duplicate after an unsafe join?
| Symptom | Likely cause | Better response |
|---|---|---|
| Same visible row appears after selecting fewer columns | The query projected away the key or distinguishing attributes | Use DISTINCT only if the question is about unique combinations |
| Counts are inflated after a join | Two one-to-many tables were joined at a shared parent key | Pre-aggregate each side to the intended grain before joining |
| A table has multiple rows for a value that should be unique | The table is missing or violating a key constraint | Define and enforce the appropriate primary key or unique constraint |
| Rows are repeated but represent separate events | The business process creates multiple valid facts with the same descriptive values | Keep the rows and model the event grain clearly |
A checklist for demonstrating the issue to a team
When someone asks why SQL returned duplicates, use a repeatable demonstration instead of a vague explanation.
- Name the source grain. Say what one row represents before running the query.
- Identify the key. Show which columns distinguish source rows.
- Project away the key. Select fewer columns and show how distinct facts can become identical-looking rows.
- Apply a filter. Show that WHERE removes non-matching rows but keeps every matching copy.
- Join two one-to-many tables. Show that valid tables can still produce multiplied combinations.
- State the intended grain. Decide whether the result should be one row per fact, per entity, per combination, or per reporting bucket.
- Choose the mechanism. Use DISTINCT for unique combinations, GROUP BY for aggregated results, constraints for impossible duplicates, and pre-aggregation for unsafe joins.
This approach keeps the conversation grounded. The problem is not that SQL is randomly duplicating data. SQL is preserving or multiplying row multiplicity according to the operations you asked it to perform.
Key takeaways
- Set semantics treat a relation as containing each tuple at most once; bag semantics allow repeated tuples.
- Ordinary SQL SELECT results usually preserve multiplicity unless DISTINCT or aggregation changes it.
- Projection can make different source facts look identical by hiding the key columns.
- Filters remove non-matching rows, but they do not deduplicate the rows that remain.
- Joins can multiply rows when two valid one-to-many relationships are combined at the same key.
- The safest diagnosis starts with grain: what does one source row represent, and what should one result row represent?
Next step
Create two tiny tables in a scratch schema or spreadsheet: one order-line table and one shipment table. Write the expected grain beside each query before looking at the result. Then compare plain SELECT, SELECT DISTINCT, GROUP BY, and a pre-aggregated join so the difference between set semantics and bag semantics becomes visible.
- Read First Normal Form and Repeating Groups: How to reshape repeated attributes into child rows while keeping the original parent record clear and queryable.
- Read SQL Null and Three-Valued Logic: A practical guide to why UNKNOWN changes SQL filters, joins, constraints, and predicate reasoning.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.