DISTINCT Keyword in SQL
Introduction
The SQL DISTINCT keyword removes duplicate rows from a query result. It is useful when a table contains repeated values but a report, dropdown, validation query, or business calculation needs each result value or combination only once.
SELECT DISTINCT department
FROM employees;
If hundreds of employees belong to the same few departments, this query returns one row for each department represented in the table. It does not modify the employees table, delete repeated records, or enforce future uniqueness. DISTINCT is a result-processing operation whose meaning depends entirely on the expressions selected by the query.
What Is DISTINCT?
DISTINCT asks the database to compare the complete rows produced by the SELECT list and keep one representative of every unique combination. Without it, SQL normally returns every qualifying row, including repeated values that are valid consequences of the data model.
Suppose five employees belong to IT, HR, IT, Finance, and HR. Selecting department returns five rows. Selecting DISTINCT department returns three rows: IT, HR, and Finance. The repeated result values disappear, but all five employee records remain stored.
Basic DISTINCT Syntax
SELECT DISTINCT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;
DISTINCT appears immediately after SELECT. WHERE first limits the qualifying source rows, the SELECT list calculates the output expressions, DISTINCT eliminates duplicate output rows, and ORDER BY arranges the remaining result. The exact physical plan may differ because the optimizer can reorganize operations while preserving this logical result.
DISTINCT on One Column
SELECT DISTINCT city
FROM customers
ORDER BY city;
This query returns every city represented by at least one customer. It is appropriate for a city filter or a data-profiling query when only city values are needed. Selecting customer_id as well would change the result because each customer-city pair would usually be unique.
DISTINCT on Multiple Columns
SELECT DISTINCT department, job_title
FROM employees
ORDER BY department, job_title;
With multiple expressions, DISTINCT applies to the entire combination. Two rows are duplicates only when both department and job_title compare as equal under the database's data type and collation rules. IT-Developer and IT-Tester remain separate, while repeated IT-Developer rows collapse into one.
DISTINCT does not independently build one list of departments and another list of job titles. SQL returns rows, and uniqueness is evaluated at row level. This rule is central to designing correct queries and explaining why adding one more selected column can increase the result count dramatically.
DISTINCT Does Not Clean Stored Data
A query such as SELECT DISTINCT email can hide repeated email values in its output, but it does not determine whether those repetitions are valid. Multiple family members may intentionally share an email, historical imports may have duplicated customers, or the application may have failed to enforce a business rule.
When stored data should be unique, investigate existing duplicates, define normalization rules, clean invalid records, and add an appropriate UNIQUE constraint. Use DISTINCT when the business request genuinely needs unique result rows, not as a substitute for data-quality work.
DISTINCT Versus a UNIQUE Constraint
DISTINCT and UNIQUE solve different problems. DISTINCT is written in a SELECT query and affects only that query's result. A UNIQUE constraint is part of the database schema and rejects new or updated rows that would violate the defined stored-data rule.
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);
A schema constraint protects every application and import process that writes to the table. DISTINCT provides no such protection. A system may legitimately use both: the constraint preserves unique emails, while a separate DISTINCT query produces unique countries or customer categories.
How DISTINCT Treats NULL
For duplicate elimination, multiple NULL values in the same selected position normally collapse into one result row. A column containing IT, HR, NULL, and NULL therefore produces IT, HR, and one NULL when selected with DISTINCT.
This behavior sometimes surprises learners because ordinary comparisons such as NULL = NULL evaluate as unknown rather than true. Duplicate elimination follows grouping-style semantics for NULL, so repeated unknown values are treated as belonging to one distinct group for this operation.
Multiple Columns Containing NULL
For a multi-column SELECT, the database compares complete combinations. Two rows containing the same department and NULL job title can collapse together, but IT-NULL and HR-NULL remain different because the department values differ.
Do not replace NULL with an empty string merely to simplify DISTINCT. NULL, blank text, and zero can represent different business states. Normalize only when the business definition explicitly says those values are equivalent.
DISTINCT with WHERE
SELECT DISTINCT department
FROM employees
WHERE employment_status = 'ACTIVE'
ORDER BY department;
The result contains departments represented by active employees, not every department stored in the table. Applying selective authorization and business filters before duplicate elimination reduces the input and makes both meaning and performance clearer.
Logical Query Processing
A useful simplified sequence is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or FETCH. This mental model explains why an alias created in SELECT can often be used by ORDER BY, why WHERE changes the population before DISTINCT, and why a limit applies after unique values have been identified.
Physical execution may use an index, hash table, stream aggregate, or another plan. Logical order describes the required result; the execution plan describes how the database obtains it.
DISTINCT with ORDER BY
SELECT DISTINCT department
FROM employees
ORDER BY department;
DISTINCT does not guarantee sorted output. A hash-based implementation can return values in an order unrelated to their text, and a plan can change after statistics, indexes, or database versions change. Include ORDER BY whenever presentation sequence is part of the contract.
Some database systems require ORDER BY expressions to appear in the SELECT list when DISTINCT is used. This avoids ambiguity where several source rows collapse into one result but have different values for the hidden sort expression. Use an explicit selected expression or a wrapping query when portability matters.
DISTINCT with Expressions
SELECT DISTINCT UPPER(city) AS normalized_city
FROM customers
ORDER BY normalized_city;
DISTINCT compares the value produced by an expression, not necessarily the raw stored value. Chicago, CHICAGO, and chicago may collapse after UPPER even when the underlying collation treats them as different. The output is then a list of normalized forms.
Expression semantics must match the requirement. LOWER(email), rounded prices, extracted JSON properties, and cast dates all define new notions of uniqueness. Document that definition instead of presenting the result as universally unique.
Distinct Calendar Dates from Timestamps
A timestamp often has enough precision that nearly every row is unique. If the requirement is a list of calendar days on which orders occurred, convert the timestamp to the correct business date before applying DISTINCT.
SELECT DISTINCT CAST(created_at AS DATE) AS order_date
FROM orders
ORDER BY order_date;
Time-zone conversion may need to happen before the date cast. Midnight in UTC is not midnight in Chicago, London, or Chennai. Define whose calendar controls the report and use an indexed derived value when this query is frequent and the database supports it.
Result Grain Comes Before DISTINCT
Result grain means what one output row represents: one customer, one order, one order item, one customer-product pair, or one customer per day. DISTINCT cannot repair a query whose grain is not understood. It can only remove rows that are identical across the selected expressions.
Before adding DISTINCT, state the desired grain in one sentence. Then select the columns that define that grain and inspect every join relationship. This approach reveals whether repeated rows are true duplicates, valid detail rows, or evidence of an incorrect join.
DISTINCT with JOIN
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;
A customer with five orders produces five joined rows before duplicate elimination. DISTINCT collapses them because only customer columns are selected. This can correctly answer "which customers have at least one order," but it also makes the database create and then remove repetition.
If order columns are selected, each customer-order combination remains distinct. Adding DISTINCT cannot turn detail rows into one customer row unless the varying detail columns are removed or aggregated.
EXISTS Often Expresses Relationship Intent Better
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
EXISTS directly asks whether at least one related row is present. It avoids exposing child multiplicity in the output and allows the optimizer to use a semijoin strategy. This is often clearer than JOIN plus DISTINCT when no child columns are required.
Do not memorize that EXISTS is always faster. Both forms can be transformed into efficient plans. Prefer the expression that accurately communicates intent, then measure representative execution plans.
DISTINCT Is Not a Universal Join Fix
Adding DISTINCT after a join returns too many rows can conceal an incomplete composite key, an accidental many-to-many relationship, a Cartesian product, or an unnecessary table. The visible result may look correct while totals, performance, and future maintenance remain wrong.
Inspect the relationship cardinality and compare expected rows before and after each join. If a child table must contribute one row, define which child through filtering, ranking, pre-aggregation, or a business rule. Remove joins that do not contribute filtering or output.
DISTINCT Versus GROUP BY
SELECT DISTINCT department and SELECT department GROUP BY department can produce the same set of department values. DISTINCT is clearer when the only requirement is duplicate removal. GROUP BY is clearer when rows must be grouped to calculate COUNT, SUM, MIN, MAX, or another aggregate.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department;
DISTINCT alone cannot calculate employee_count. Conversely, using GROUP BY only to imitate DISTINCT can make a simple uniqueness request look like an aggregation problem. Choose syntax that communicates the business operation.
COUNT DISTINCT
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= :start_date
AND order_date < :end_date;
COUNT(DISTINCT customer_id) counts unique non-NULL customer IDs among qualifying orders. It is commonly used for active users, purchasing customers, visited products, and other reach metrics. COUNT(*) counts rows, while COUNT(customer_id) counts non-NULL values including repetitions.
The handling of NULL is important: SELECT DISTINCT customer_id can display one NULL row, while COUNT(DISTINCT customer_id) normally excludes NULL. Tests should include NULL so reports do not accidentally equate these operations.
Counting Unique Combinations
Support for COUNT(DISTINCT column1, column2) varies by database. A portable approach is to create the distinct combinations in a subquery and count those rows.
SELECT COUNT(*) AS unique_customer_products
FROM (
SELECT DISTINCT customer_id, product_id
FROM order_items
) AS pairs;
Avoid concatenating columns into one string to simulate composite uniqueness. Delimiters can collide with actual values, implicit conversions can lose information, NULL requires special handling, and index use becomes difficult.
SUM DISTINCT and AVG DISTINCT
Some databases support SUM(DISTINCT amount) and AVG(DISTINCT salary). They aggregate each different numeric value once, not each different business entity once. If two legitimate payments are both 100, SUM(DISTINCT amount) counts 100 only once and produces an incorrect financial total.
Never apply SUM(DISTINCT) blindly to repair double counting created by a join. Correct the relationship, pre-aggregate at the intended grain, or select unique transaction keys and amounts together before summing. Numeric equality is rarely the same as business-event identity.
DISTINCT and UNION
UNION removes duplicate rows from the combined branch results, while UNION ALL retains every row. Applying DISTINCT inside each branch before UNION is often redundant because UNION already performs global duplicate elimination.
SELECT email FROM customers
UNION
SELECT email FROM prospects;
Use UNION ALL when duplicates are meaningful or when branches are known to be disjoint. If all rows must first be combined and then deduplicated, UNION expresses that directly. Compare plans because unnecessary repeated duplicate-elimination stages add cost.
DISTINCT with Window Functions
A window expression such as ROW_NUMBER assigns a different value to every row. If it appears in the SELECT list, DISTINCT sees those values as different and cannot collapse otherwise repeated departments.
SELECT DISTINCT department,
ROW_NUMBER() OVER (ORDER BY employee_id) AS row_number
FROM employees;
This query generally preserves every row because row_number is unique. If the objective is one representative employee per department, rank rows in a subquery and filter row_number = 1. If only unique departments are required, omit the window expression entirely.
DISTINCT in Subqueries and CTEs
A subquery or common table expression can define a reusable unique set before later joins or calculations. This is valuable when the uniqueness boundary is meaningful and prevents many-to-many multiplication.
WITH purchasing_customers AS (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= :start_date
)
SELECT c.customer_id, c.customer_name
FROM customers AS c
JOIN purchasing_customers AS p
ON p.customer_id = c.customer_id;
Do not add DISTINCT to every CTE defensively. Each stage should have a documented grain, and duplicate elimination should occur only where the required semantics demand it.
Case, Accent, and Collation Behavior
Whether Chicago, CHICAGO, and chicago are considered distinct depends on the active collation, data type, and database. Accent-sensitive rules may treat Jose and Jose with an accented final character differently, while accent-insensitive rules may group them.
Trailing spaces and Unicode normalization can also vary by engine and type. If the business requires normalized uniqueness, define a consistent storage and comparison strategy rather than relying on an installation default. Query-time LOWER or TRIM may help analysis but does not prevent inconsistent data from being stored.
Numeric and Floating-Point Distinctness
Exact numeric types such as DECIMAL are appropriate when equality has financial or contractual meaning. Floating-point values that display identically after formatting may differ internally, so SELECT DISTINCT measurement can return values that appear duplicated to a user.
If the requirement concerns rounded display buckets, apply the documented rounding expression before DISTINCT. Do not confuse formatting equality with stored numeric equality, and test values around rounding boundaries.
DISTINCT with JSON and Large Values
Database support for comparing JSON, XML, BLOB, and large character values differs. Textually different JSON documents can represent equivalent objects with different property order or whitespace. A raw DISTINCT operation may therefore be unsupported, expensive, or semantically misleading.
Extract the stable business attributes required by the report instead of deduplicating entire documents. Narrow keys reduce memory, temporary storage, and network cost and make the intended definition of uniqueness reviewable.
DISTINCT and Pagination
SELECT DISTINCT department
FROM employees
ORDER BY department
FETCH FIRST 20 ROWS ONLY;
Conceptually, the database identifies unique departments, orders them, and then returns the first twenty. Fetching twenty employee rows first and deduplicating in application code may produce fewer than twenty departments and miss values appearing later.
Pagination needs deterministic ORDER BY. When the selected distinct key is not itself unique under the requested collation, add an appropriate stable expression or redesign the page contract. Cursor values must represent the ordered distinct result, not the underlying duplicated detail rows.
How Databases Implement DISTINCT
The optimizer may use a sort followed by duplicate removal, a hash aggregate, a stream aggregate over already ordered input, or an index-based unique scan. The chosen plan depends on input size, estimated distinct cardinality, row width, ordering needs, memory, indexes, and parallel execution.
A hash strategy groups equal keys without producing sorted output. A sort strategy brings equal values together and can sometimes satisfy a compatible ORDER BY at the same time. Both may spill to temporary storage when memory is insufficient.
DISTINCT Performance Cost
DISTINCT is not free. The database must inspect qualifying rows and determine whether each result key has already appeared. Millions of wide rows containing text or JSON can demand substantial CPU, memory, and temporary I/O even when only a small number of rows are ultimately returned.
Filter early, select only required columns, avoid unnecessary detail joins, and inspect actual execution metrics. A correct query with a small filtered input may be inexpensive, while DISTINCT over an unbounded export can become a major workload.
Indexes That Can Help DISTINCT
An index beginning with the selected distinct columns can provide equal keys together and may allow a stream or index-only strategy. A composite index on department and job_title can help a query requesting those unique combinations, particularly when filtering aligns with earlier index columns.
An index does not guarantee a cheap query. Low selectivity may still require scanning a large structure, projected columns may force table lookups, and expression or collation differences can prevent index use. Validate plans with realistic data and account for the write and storage cost of every index.
Avoid DISTINCT Star
SELECT DISTINCT *
FROM employees;
DISTINCT star compares every selected column, making rows wider and duplicate elimination more expensive. If the table includes a primary key, every row is already distinct, so the keyword usually changes nothing.
Select the minimal columns that define the requested uniqueness. This improves clarity, reduces transfer size, and prevents volatile values such as timestamps or audit IDs from defeating duplicate elimination.
Primary Keys Can Make DISTINCT Redundant
If the SELECT list contains a candidate key such as customer_id, every output row from a single table is already unique unless a join repeats that key. The optimizer may recognize and remove a redundant DISTINCT, but developers should not rely on that cleanup.
When a join repeats a primary key, ask whether the query should return one parent or one parent-child combination. Use EXISTS, aggregation, or corrected join logic according to the requirement instead of automatically hiding repetition.
Cardinality and Query Planning
Distinct cardinality is the expected number of different keys. A status column may have five values across ten million rows, while a transaction ID may have nearly ten million different values. These cases place different demands on hashing, sorting, and result transfer.
Accurate statistics help the optimizer estimate memory and choose a plan. Large estimation errors can cause spills or inefficient parallelism. Review actual input rows, output rows, memory grants, and spill warnings when tuning important DISTINCT queries.
Tenant Isolation and Security
In a multi-tenant application, authorization filtering must happen before DISTINCT. A global query for distinct statuses, regions, roles, or diagnosis codes can reveal the existence of data belonging to another tenant even when individual records are hidden.
SELECT DISTINCT status
FROM orders
WHERE tenant_id = :tenant_id
ORDER BY status;
Return only fields required by the feature. A value remains sensitive even when duplicate copies have been removed. Parameterization, row-level security, and application authorization should be tested together.
Useful Reporting and Profiling Patterns
DISTINCT is well suited to dimension lists such as represented countries, active cities, available categories, and valid order statuses. It also helps profile unexpected variations including mixed case, obsolete codes, blank values, and NULL.
For deeper profiling, GROUP BY with COUNT often provides more information than a bare distinct list because it shows how frequently every value occurs. Use DISTINCT to discover the set and grouped counts to understand its distribution.
Database Deduplication Versus Application Deduplication
The database should normally apply DISTINCT when uniqueness determines filtering, counting, ordering, pagination, or the amount of data sent to a client. It has access to indexes, statistics, collation rules, and set-based operators, and it can avoid transferring thousands of repeated rows merely so application code can discard them.
Application deduplication can still be reasonable when a small complete result is already loaded, when objects from several non-database sources must be merged, or when the uniqueness rule depends on domain behavior that is not represented in SQL. In that case, the application must define equality, NULL behavior, case sensitivity, and stable ordering just as carefully as the database query would.
Do not split one logical operation unpredictably between layers. Deduplicating after database pagination produces incomplete pages, and deduplicating after aggregate totals have been calculated cannot repair double counting. Assign one layer clear ownership of the uniqueness contract and test the complete result across that boundary.
Reference Tables and Maintained Dimension Lists
A frequent SELECT DISTINCT query can indicate that the application is deriving reference data from a large transactional table. For example, scanning millions of products to discover active category IDs may be less reliable than maintaining a categories table with explicit status, display name, sort order, and tenant ownership.
Reference tables make valid values and relationships visible, support foreign keys, and avoid making current transactional presence the definition of existence. A category can remain valid even when it temporarily has no active products, while a DISTINCT query over products would omit it.
For analytical workloads, a materialized view or warehouse dimension can precompute expensive unique sets. Refresh frequency then becomes part of the contract: users must know whether the list is real-time or delayed. Choose direct DISTINCT, a reference table, or a maintained summary according to source size, update frequency, consistency requirements, and the business meaning of the values.
Common DISTINCT Mistakes
- Adding DISTINCT to every query as a defensive habit.
- Using it to hide an incorrect or incomplete join.
- Selecting a primary key and expecting other columns to collapse.
- Assuming each selected column is deduplicated independently.
- Assuming DISTINCT guarantees sorted output.
- Ignoring NULL, collation, trailing-space, and time-zone semantics.
- Using SUM(DISTINCT) to repair duplicated financial totals.
- Using DISTINCT when aggregation or EXISTS better expresses intent.
- Deduplicating wide SELECT star results unnecessarily.
- Applying security filters after producing a global distinct set.
Testing DISTINCT Queries
Test no rows, one row, all values identical, all values unique, mixed duplicates, NULL, blank strings, mixed case, accents, trailing spaces, and multiple-column combinations. Join tests should include one parent with many children, a parent without children, and malformed relationship data.
For date expressions, test time-zone boundaries and daylight-saving transitions. For aggregates, compare row count, non-NULL count, and distinct count explicitly. Assertions should compare complete result keys rather than only the number of rows.
Performance Testing DISTINCT
Use production-like volume, row width, skew, and distinct cardinality. Measure elapsed time, CPU, logical reads, memory, temporary storage, spill behavior, rows scanned, rows returned, and network transfer. A tiny uniform test table cannot predict a large multi-tenant workload.
Compare the original query with alternatives such as EXISTS, pre-aggregation, narrower projection, selective filtering, and an aligned index. Retain the simplest correct design that meets measured service objectives.
Production DISTINCT Checklist
- State what one output row represents.
- Define exactly which expressions must be unique.
- Confirm that duplicate source rows are valid or understood.
- Verify every join key and relationship cardinality.
- Apply tenant, authorization, and business filters first.
- Select only the columns needed by the consumer.
- Define NULL, case, accent, space, and time-zone semantics.
- Use ORDER BY when sequence matters.
- Prefer EXISTS for pure relationship existence checks.
- Use GROUP BY when aggregates define the result.
- Inspect plans, indexes, memory, and spills at realistic scale.
- Add constraints when stored data itself must be unique.
Practical Unique Customer-Product Query
SELECT DISTINCT o.customer_id, i.product_id
FROM orders AS o
JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.tenant_id = :tenant_id
AND o.order_date >= :start_date
AND o.order_date < :end_date
ORDER BY o.customer_id, i.product_id;
The grain is one customer-product pair for a tenant and date range. Repeated purchases collapse because order identity is intentionally omitted. Including order_id would change the grain to one order-product combination.
Practical Active Filter Values Query
SELECT DISTINCT category_id
FROM products
WHERE tenant_id = :tenant_id
AND product_status = 'ACTIVE'
AND category_id IS NOT NULL
ORDER BY category_id;
This query can populate a category filter from active products while excluding an unusable NULL option. For a large catalog, a maintained category relationship or reference table may be more efficient and semantically stronger than repeatedly scanning products.
Interview Questions and Answers
What does DISTINCT do? It removes duplicate rows from the SELECT result based on every selected expression.
Does DISTINCT change table data? No. It affects only the query result.
How does DISTINCT work with multiple columns? It keeps unique combinations of all selected columns.
DISTINCT versus UNIQUE? DISTINCT is query-time result processing; UNIQUE is a stored-data integrity constraint.
DISTINCT versus GROUP BY? DISTINCT expresses duplicate removal; GROUP BY forms groups, usually for aggregation.
How are NULL values handled? Repeated NULLs normally form one distinct output group, while COUNT(DISTINCT column) excludes NULL.
Does DISTINCT sort? No. Use ORDER BY for guaranteed sequence.
Why can DISTINCT be expensive? The database may need to hash or sort many wide input rows and can spill to temporary storage.
Why is JOIN plus DISTINCT sometimes an anti-pattern? It can hide incorrect cardinality or create child repetition that EXISTS could avoid.
When is DISTINCT appropriate? When unique result values or combinations are an explicit part of the requirement.
Key Takeaway
DISTINCT is a precise tool for returning unique result rows. Its correctness depends on the selected expressions, result grain, NULL and collation rules, join relationships, security scope, and business meaning of uniqueness.
GOOD DISTINCT USAGE = CLEAR UNIQUENESS REQUIREMENT
+ CORRECT RESULT GRAIN
+ REQUIRED COLUMNS ONLY
+ VERIFIED JOIN LOGIC
+ DEFINED NULL AND COLLATION RULES
+ SECURITY FILTERS FIRST
+ MEASURED PERFORMANCE
Use DISTINCT when uniqueness belongs to the result contract. Do not use it to conceal duplicate source data, repair an unknown join, imitate aggregation, or avoid defining what one row should represent.