DISTINCT — Use DISTINCT and understand when it changes results - SQL

SECTION 1

Historical Context & Motivation

Relational databases emerged from a simple but powerful insight: data stored as mathematical relations—tables of rows and columns—could be queried with a declarative language rather than procedural navigation. Edgar F. Codd's foundational 1970 paper introduced the relational model, which treats a relation as a set of tuples. In pure set theory, sets contain no duplicate elements; however, SQL's practical design chose to work with multisets (also called bags) by default, meaning a query's result can contain duplicate rows. The DISTINCT keyword was introduced to bridge this gap, allowing programmers to request true set semantics when needed.

1970 • Codd's Relational Model

Edgar F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," defining relations as sets of tuples with no duplicates by definition.

1974 • SEQUEL at IBM

IBM researchers Chamberlin and Boyce create SEQUEL (later SQL), choosing multiset semantics for performance reasons and adding DISTINCT as an explicit deduplication operator.

1986 • SQL-86 Standard

ANSI adopts the first SQL standard, formally specifying SELECT DISTINCT as part of the language grammar along with its interaction with ORDER BY and aggregate functions.

1992 • SQL-92 Enhancements

The SQL-92 standard clarifies DISTINCT behavior inside aggregate functions (e.g., COUNT(DISTINCT col)) and codifies ALL as the default qualifier.

2003+ • Modern Optimizers

Query optimizers in PostgreSQL, MySQL, SQL Server, and Oracle implement hash-based and sort-based distinct elimination strategies, making DISTINCT performance a key consideration in query tuning.

The fundamental question DISTINCT addresses is deceptively simple: when does a SELECT statement produce duplicate rows, and how should a programmer decide whether to eliminate them? Understanding the answer requires grasping how projections, joins, and the absence of primary key columns in a SELECT list conspire to create duplicates—and recognizing the performance cost of removing them.

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is essential to distinguish between set semantics and bag (multiset) semantics. SQL defaults to bag semantics: a SELECT projection may return the same combination of values many times. The DISTINCT keyword switches the query to set semantics for the result, collapsing all identical rows into a single representative. This distinction is not merely academic—it affects correctness, performance, and even the logical meaning of aggregate calculations.

Bag vs. Set Semantics

SQL SELECT returns a multiset (bag) by default. Adding DISTINCT converts the output to a set, removing duplicate rows. The implicit qualifier is ALL, which preserves duplicates.

Row-Level Comparison

DISTINCT compares entire rows across all columns in the SELECT list. Two rows are duplicates if and only if every corresponding column value is equal (with NULLs treated as equal to each other for this purpose).

Projection Creates Duplicates

Duplicates typically arise when the SELECT list omits columns that differentiate rows—especially primary keys or unique identifiers. A projection onto non-key columns collapses distinct tuples into identical-looking rows.

DISTINCT Inside Aggregates

Aggregate functions like COUNT, SUM, and AVG accept DISTINCT as a modifier: COUNT(DISTINCT department) counts unique departments rather than all rows. This is orthogonal to SELECT DISTINCT.

Performance Implications

DISTINCT requires the engine to sort or hash all result rows to detect duplicates. On large result sets, this adds significant CPU and memory overhead. Use DISTINCT only when duplicates are genuinely possible and undesirable.

✦

KEY TAKEAWAY

Think of DISTINCT like a mail sorter removing duplicate copies of the same letter before delivering the stack. If the original stack has no duplicates—say, each letter has a unique tracking number included in your view—then DISTINCT does nothing except waste time sorting through the pile. Only apply it when you know the projection might produce repeated rows.

SECTION 3

Visual Explanation

The left panel shows the original employees table with six unique rows. The center panel shows the result of SELECT department without DISTINCT—six rows, three of which are duplicates. The right panel applies SELECT DISTINCT department, collapsing the result to three unique department values.

The diagram illustrates the most common scenario that produces duplicates: projecting away the primary key. When you select only the department column from a table where multiple employees share the same department, the engine faithfully returns one row per original tuple—resulting in repeated values. DISTINCT instructs the engine to perform a deduplication pass, comparing every column in the SELECT list across all result rows and retaining only one copy of each unique combination. Notice that if you had instead written SELECT DISTINCT id, department, no rows would be eliminated because id is unique—every row is already distinct, and DISTINCT becomes a no-op.

SECTION 4

How DISTINCT Works Internally

Understanding how the database engine implements DISTINCT helps you reason about when it is necessary and what it costs. The SQL logical query processing order places DISTINCT after SELECT and before ORDER BY, meaning the engine first evaluates the FROM, WHERE, GROUP BY, HAVING, and SELECT clauses to produce a multiset of rows, then applies DISTINCT to eliminate duplicates, and finally sorts the result if ORDER BY is specified.

Logical Query Processing Order

FROM — Identify source tables and perform joins
WHERE — Filter rows
GROUP BY — Aggregate rows into groups
HAVING — Filter groups
SELECT — Evaluate expressions and project columns
DISTINCT — Remove duplicate rows from the projected result
ORDER BY — Sort the final output
LIMIT / OFFSET — Restrict the number of rows returned

Physical Implementation Strategies

Database engines typically choose between two physical strategies to implement DISTINCT. The sort-based approach sorts the entire result set, then performs a linear scan to collapse adjacent identical rows. This runs in O(n log n) time and is favorable when the output is already partially sorted or when the query also requires ORDER BY. The hash-based approach builds an in-memory hash table of seen rows, inserting each new row only if its hash bucket is empty. This achieves O(n) average-case time complexity but requires O(k) memory, where k is the number of distinct rows. Modern query optimizers choose between these strategies based on estimated cardinality and available memory.

SORT-BASED DISTINCT

T(n) = O(n log n) + O(n) linear scan

Where n is the number of rows in the pre-DISTINCT result set. The sort dominates cost.

HASH-BASED DISTINCT

T(n) = O(n) Space = O(k)

Where k is the number of distinct rows. When k is small relative to n, hashing is highly efficient; when k ≈ n, the overhead provides no benefit over omitting DISTINCT entirely.

⚠

NULL Handling

For the purposes of DISTINCT, two NULLs are considered equal. This is an exception to the general SQL rule that NULL ≠ NULL in comparison predicates. If a column contains multiple NULL values, DISTINCT collapses them into a single NULL row. This behavior is mandated by the SQL standard (ISO/IEC 9075) and is consistent across major database engines.

SECTION 5

When DISTINCT Changes (and Doesn't Change) Results

A critical skill is recognizing when DISTINCT actually affects the output. Blindly adding DISTINCT to every query is a common antipattern that masks data model misunderstandings and introduces unnecessary overhead. The decision tree below formalizes the reasoning: DISTINCT changes results only when the columns in the SELECT list do not form a superkey of the result set.

This decision tree guides you through the key questions: does the SELECT list contain a unique column? Is GROUP BY already in play? Are JOINs multiplying rows? Following the branches helps determine whether DISTINCT is necessary, redundant, or a sign of a deeper query design issue.

Common Scenarios

Scenario	DISTINCT Changes Result?	Explanation
`SELECT id, name FROM users`	No	id is a primary key; every row is already unique.
`SELECT city FROM users`	Yes (likely)	Multiple users can share the same city; projection onto a non-key column produces duplicates.
`SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id`	Yes (likely)	A one-to-many join produces one row per order; a user with 5 orders appears 5 times.
`SELECT department, COUNT(*) FROM employees GROUP BY department`	No	GROUP BY already produces one row per group; DISTINCT is redundant here.
`SELECT DISTINCT status FROM orders WHERE amount > 100`	Yes	status is a low-cardinality column; many orders share the same status value.

Common query patterns and whether DISTINCT affects the output

SECTION 6

Worked Example

Consider an e-commerce database with two tables: customers(id, name, city) and orders(id, customer_id, product, amount). We want to find all distinct cities from which customers have placed orders exceeding $50. This requires a JOIN, a filter, a projection, and DISTINCT.

Finding Distinct Cities of High-Value Customers

1
Step 1 — Identify the Tables and Join Condition

We need data from both customers (for the city) and orders (for the amount filter). The join condition is customers.id = orders.customer_id. Because this is a one-to-many relationship (one customer can have many orders), the join will produce multiple rows per customer.
2
Step 2 — Write the Base Query Without DISTINCT

Start with: SELECT c.city FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > 50. If a customer in New York has placed three qualifying orders, 'New York' appears three times in the result.

Result may contain duplicate cities due to the one-to-many join.
3
Step 3 — Analyze Whether DISTINCT Is Needed

The SELECT list contains only c.city, which is not a key of the joined result. Multiple customers may also share the same city. Therefore, duplicates are both possible and undesirable for our use case (we want the list of unique cities, not a count of qualifying orders per city).
4
Step 4 — Add DISTINCT

The final query becomes: SELECT DISTINCT c.city FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > 50. The engine performs the join and filter first, then projects the city column, and finally deduplicates the result.

Each city appears exactly once in the output, regardless of how many qualifying orders originated from it.
5
Step 5 — Consider Alternatives

An equivalent approach uses a subquery: SELECT DISTINCT city FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > 50). This avoids the join-induced row multiplication entirely; the DISTINCT here handles the case where multiple customers in the same city qualify. Another option is EXISTS, which can be more efficient because it short-circuits after finding the first matching order.

When duplicates come from JOINs, restructuring the query with subqueries or EXISTS can eliminate the need for DISTINCT and improve performance.

SECTION 7

Strengths, Limitations & Common Pitfalls

Aspect	Strength	Limitation / Pitfall
Simplicity	A single keyword eliminates duplicates with no code changes to the rest of the query.	Over-reliance masks incorrect joins or missing WHERE conditions that inflate row counts.
Correctness	Guarantees set semantics when the business question demands unique values.	If used with aggregates naively (e.g., SELECT DISTINCT with a SUM), it can silently drop valid data that happens to look identical.
Performance	When the distinct count is very low (high duplication ratio), DISTINCT significantly reduces data transferred to the client.	Requires a sort or hash of the full result set. On millions of rows, this can dominate query execution time.
Composability	Works cleanly with ORDER BY, LIMIT, and in subqueries.	Cannot be combined with SELECT * in a meaningful way if you only want uniqueness on a subset of columns—use GROUP BY instead.
Debugging Signal	Comparing row counts with and without DISTINCT can reveal data model issues.	Habitual DISTINCT is a 'code smell'—if you need it frequently, revisit your schema or join logic.

DISTINCT: strengths vs. limitations

✦

KEY TAKEAWAY

DISTINCT is like a lint checker for your query output—it cleans up visible duplicates, but it doesn't fix the underlying issue. If you find yourself routinely adding DISTINCT, treat it as a diagnostic signal. Investigate whether the root cause is a faulty join condition (producing a Cartesian product), a missing WHERE clause, or a data model that lacks proper normalization. Fixing the root cause is almost always preferable to masking it with DISTINCT.

SECTION 8

Connection to GROUP BY, Window Functions & Set Operations

DISTINCT is closely related to several other SQL constructs that also manipulate duplicate rows. Understanding these connections helps you choose the most appropriate tool for each situation and prepares you for more advanced query design patterns.

Feature	What It Does	Relationship to DISTINCT
`GROUP BY`	Partitions rows into groups and collapses each group into a single output row via aggregate functions.	SELECT DISTINCT a, b is logically equivalent to SELECT a, b GROUP BY a, b when no aggregates are used. GROUP BY is more powerful because it allows aggregation.
`UNION`	Combines results of two queries and removes duplicates across both result sets.	UNION implicitly applies DISTINCT. Use UNION ALL to preserve duplicates (better performance when dedup is unnecessary).
`ROW_NUMBER()`	Assigns a unique sequential integer to rows within a partition, enabling more nuanced deduplication.	For complex dedup (e.g., keep the most recent row per group), use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) with a WHERE rn = 1 filter—DISTINCT cannot handle this.
`EXISTS / IN subquery`	Tests for the existence of related rows without joining them into the result.	Avoids the join-induced duplication that often necessitates DISTINCT. Preferred when you only need columns from the outer table.
`DISTINCT ON (PostgreSQL)`	Returns the first row for each unique combination of specified columns, respecting ORDER BY.	A PostgreSQL extension that goes beyond standard DISTINCT by allowing you to pick which row to keep from each group—similar to ROW_NUMBER() + filter but more concise.

DISTINCT in the context of related SQL features

As you progress to advanced SQL, you will find that DISTINCT is often the simplest tool in a family of deduplication strategies. Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() provide fine-grained control over which representative row survives from a group of duplicates, which DISTINCT alone cannot achieve. Similarly, Common Table Expressions (CTEs) combined with ROW_NUMBER() are the standard idiom for deduplicating tables during data cleaning pipelines—a scenario far more complex than simple SELECT DISTINCT.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Consider the query SELECT DISTINCT id, email FROM users where id is the primary key. Will DISTINCT ever remove any rows from this result? Explain why or why not.

Show answer

No, DISTINCT will never remove rows here. Because id is the primary key, every row already has a unique id value. Since DISTINCT compares all columns in the SELECT list (both id and email), no two rows can be identical—even if two users share the same email, their ids will differ. The DISTINCT is a no-op and adds unnecessary sorting/hashing overhead.

Question 2: BASIC CALCULATION

Given a table products(id, category, price) with 10,000 rows and 25 unique categories, how many rows does SELECT DISTINCT category FROM products return? What about SELECT DISTINCT category, price FROM products if there are 8,500 unique (category, price) combinations?

Show answer

The first query returns exactly 25 rows—one per unique category value. The second query returns 8,500 rows because DISTINCT operates on the entire tuple (category, price), and there are 8,500 unique combinations of those two columns. This illustrates that adding columns to the SELECT list of a DISTINCT query can only increase (or maintain) the result count, never decrease it, because more columns means more possible unique combinations.

Question 3: INTERMEDIATE

Write a query to find all unique product categories that have been ordered at least once, given tables products(id, name, category) and order_items(id, order_id, product_id, quantity). Provide two versions: one using DISTINCT and one that avoids DISTINCT entirely.

Show answer

Version 1 (with DISTINCT): SELECT DISTINCT p.category FROM products p JOIN order_items oi ON p.id = oi.product_id. This joins the tables (potentially producing many rows per product) and then deduplicates. Version 2 (without DISTINCT): SELECT category FROM products WHERE id IN (SELECT product_id FROM order_items). Here, the IN subquery checks existence without multiplying rows, so DISTINCT is only needed if multiple products share a category—but since we're selecting from products directly, we might still get duplicate categories. A fully DISTINCT-free approach uses SELECT category FROM products WHERE EXISTS (SELECT 1 FROM order_items WHERE product_id = products.id) GROUP BY category. The GROUP BY eliminates duplicates without DISTINCT.

Question 4: APPLIED

A colleague writes SELECT DISTINCT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id. They say the DISTINCT is needed 'for safety.' Is the DISTINCT doing anything here? What would you tell them during a code review?

Show answer

The DISTINCT is completely redundant. The GROUP BY customer_id clause already guarantees one output row per unique customer_id, so no two rows in the result can have the same customer_id. Since the SELECT list contains customer_id and an aggregate derived from the group, every row is inherently unique. During code review, you should explain that the unnecessary DISTINCT forces the engine to perform an additional sort or hash pass on the already-unique result set, wasting CPU cycles. More importantly, habitual 'safety DISTINCT' hides bugs: if a query unexpectedly returns duplicates after GROUP BY, it signals a logic error that should be investigated, not papered over.

Question 5: CRITICAL THINKING

Consider two queries: (A) SELECT COUNT(DISTINCT department) FROM employees and (B) SELECT COUNT(*) FROM (SELECT DISTINCT department FROM employees) AS sub. Are these guaranteed to return the same result? What happens if there are NULL values in the department column? Discuss any edge cases.

Show answer

They differ when NULLs are present. Query A uses COUNT(DISTINCT department), and COUNT of a column expression ignores NULLs by definition—so if there are rows with department = NULL, they are not counted. Query B first computes SELECT DISTINCT department, which keeps one NULL row (since DISTINCT treats NULLs as equal), and then COUNT() counts all rows in the subquery including the NULL row, because COUNT() counts rows regardless of column values. Therefore, if there is at least one NULL department, Query B returns a value one greater than Query A. If there are no NULLs, the results are identical. This edge case highlights the subtle interaction between DISTINCT's grouping behavior (NULLs are equal) and COUNT's column-skipping behavior (NULLs are ignored). Understanding these semantics is essential for writing correct analytical queries.

SUMMARY

Summary

The DISTINCT keyword transforms SQL's default multiset (bag) semantics into set semantics, eliminating duplicate rows from a query's result. Duplicates arise primarily when the SELECT list projects away unique key columns or when one-to-many JOINs multiply rows. DISTINCT operates on the entire tuple of selected columns, treating two NULLs as equal. Internally, the engine uses either a sort-based (O(n log n)) or hash-based (O(n)) strategy, both of which add overhead.

Use DISTINCT deliberately: when the SELECT list includes a primary key or unique column, DISTINCT is a no-op and should be omitted. When GROUP BY is already present, it typically makes DISTINCT redundant. For complex deduplication—such as keeping one representative row per group—consider ROW_NUMBER() window functions or restructuring with EXISTS / IN subqueries. Habitual use of DISTINCT without understanding why duplicates appear is a code smell that may mask join errors or schema issues.