Chapter 04 · Reading Data with SELECT
DISTINCT and Duplicate Semantics
Learn why SQL results may contain duplicate rows, when DISTINCT expresses a real requirement, and when it merely hides a modeling or join problem.
Learning outcomes
SQL query results are commonly described using bag or multiset semantics: multiple result rows may have the same values. DISTINCT asks the DBMS to collapse duplicate result rows after the select-list expressions are evaluated.
Explain why duplicate result rows can appear even when source tables have primary keys.
Use DISTINCT over one or several output expressions and reason about NULL values.
Distinguish legitimate deduplication from using DISTINCT to conceal an incorrect query.
Inspect duplicate groups before deciding how to remove them.
Primary keys do not make every projection unique
The customer table has unique customer_id values. But a projection that omits the key can repeat.
SELECT c.cityFROM customer AS c;Two customers live in Berlin, so Berlin appears twice. The source rows are different; the projected values are equal.
Projection can remove the attributes that distinguished source rows.
SELECT DISTINCT compares the complete output row
SELECT DISTINCT c.cityFROM customer AS c;For a one-column result, duplicate city values collapse. For a multi-column result, two rows are duplicates only when all selected expressions compare as duplicates for DISTINCT processing.
SELECT DISTINCT c.city, c.segmentFROM customer AS c;| Output row A | Output row B | Duplicate for two-column DISTINCT? |
|---|---|---|
| Berlin, business | Berlin, consumer | No; segment differs |
| Berlin, consumer | Berlin, consumer | Yes |
| NULL, consumer | NULL, consumer | Yes for DISTINCT processing |
NULL and DISTINCT
SQL’s ordinary equality predicate involving NULL evaluates to unknown rather than true. Duplicate elimination is defined differently: repeated output rows containing NULL in corresponding positions are treated as duplicates for DISTINCT.
DROP TABLE IF EXISTS contact_city;CREATE TABLE contact_city (city TEXT);INSERT INTO contact_city (city)VALUES ('Berlin'), ('Berlin'), (NULL), (NULL), ('Tehran');SELECT cityFROM contact_city;SELECT DISTINCT cityFROM contact_city;Do not infer predicate truth from duplicate-elimination behavior. NULL = NULL is not true, yet duplicate NULL result rows collapse under DISTINCT.
DISTINCT is applied to result expressions
Aliases label the output; they do not determine uniqueness. The values produced by the expressions do.
SELECT DISTINCT p.category AS product_categoryFROM product AS p;If several products share the same category, only one row per category is returned. The query does not alter or merge product rows in storage.
Find the reason before removing duplicates
| Reason repeated rows appear | Appropriate response |
|---|---|
| Several valid source rows share the projected values | Use DISTINCT if the requirement is a list of unique values |
| A join multiplies rows unexpectedly | Fix join keys or cardinality; do not hide the defect |
| Source table contains forbidden duplicates | Add data cleanup and a key or UNIQUE constraint |
| The query intentionally reports occurrences | Keep duplicates; they carry frequency information |
| The consumer wants one arbitrary representative row | Define a deterministic rule rather than DISTINCT |
Deduplication should express a requirement, not suppress evidence.
Inspect duplicate groups explicitly
Aggregation is taught in Chapter 8, but this diagnostic pattern is worth recognizing now:
SELECT c.city, COUNT(*) AS occurrence_countFROM customer AS cGROUP BY c.cityHAVING COUNT(*) > 1;This query explains which city values repeat and how often. It is often more informative than immediately adding DISTINCT.
DISTINCT has execution cost
The DBMS must identify equal result rows, commonly through sorting, hashing, or an index-assisted strategy. The exact plan depends on the engine, data, and available indexes.
Duplicate elimination is additional work beyond producing the original result.
On small datasets the cost may be negligible. On wide or large results, unnecessary DISTINCT can consume memory, CPU, and temporary storage.
First express the correct result. Then inspect plans and measurements if performance matters. The main warning is against adding DISTINCT habitually without understanding the duplicates.
Lab: reason from repeated sales attributes
-- Every sale contributes a customer identifier.SELECT s.customer_idFROM sale AS s;-- Unique customers who have at least one sale.SELECT DISTINCT s.customer_idFROM sale AS s;-- Every product occurrence in sales.SELECT s.product_idFROM sale AS s;-- Unique products that have appeared in sales.SELECT DISTINCT s.product_idFROM sale AS s;The raw results preserve occurrence information. The distinct results answer membership questions: which customers or products appear at least once?
Multi-column distinct lab
SELECT DISTINCT s.customer_id, s.product_idFROM sale AS s;This returns each observed customer-product combination once. It does not say how many purchases occurred, nor does it select a particular sale record.
Interpret the result
- If one customer buys the same product in three sales, how many rows represent that pair under DISTINCT?
- If the same customer buys two products, how many distinct pairs can appear?
- Does the result identify which sale happened first?
- Would adding sale_id make every row unique?
Review the answers
The repeated pair collapses to one row. Two products create two pairs. No sale order is represented. Adding the primary key sale_id would distinguish every sale row.
Common mistakes
Adding DISTINCT after every JOIN
Unexpected multiplication often reveals a missing or incomplete join condition. Hiding it can produce plausible but wrong results.
Assuming DISTINCT chooses one full source row
It compares only the selected expressions. It does not select a canonical representative unless those expressions define one.
Expecting sorted output
Duplicate elimination may use sorting internally, but it does not guarantee presentation order. Only ORDER BY defines order.
Removing meaningful repetitions
Repeated rows may represent multiple events. Deduplicating them destroys frequency information.
Summary and references
- SQL results may contain equal rows.
- Projection can create duplicates by removing distinguishing attributes.
DISTINCTcompares the entire output row.- Repeated
NULLoutput values collapse during duplicate elimination. - Use DISTINCT only when uniqueness is part of the required answer.