Removing Duplicates in SQL
Introduction
Duplicate data appears in reports, integrations, migrations, event streams, customer lists, and operational tables. Sometimes the records are genuinely redundant. In other cases, rows only look alike because the query omitted a key or because several valid events share the same visible values. Correct duplicate removal begins by defining identity and business grain, not by adding DISTINCT automatically.
SQL offers several tools for different stages of the problem. DISTINCT, GROUP BY, and set operators can deduplicate query output without changing stored data. Aggregate queries can reveal duplicate groups. Window functions such as ROW_NUMBER() can select a deterministic survivor. Keys and unique constraints prevent invalid repetitions from entering the table.
This tutorial connects those techniques into a safe workflow. It explains exact and business-key duplicates, normalization, null and case behavior, joins, multi-table results, safe deletion, auditing, constraints, concurrency, performance, testing, and real project scenarios.
What Is Duplicate Removal in SQL?
Duplicate removal means producing or storing only the rows that should be unique under a defined rule. The rule may cover the complete selected row, a single field, or a composite business key such as tenant, email, and account type.
Two operations are often confused:
| Operation | Effect | Typical tools |
|---|---|---|
| Deduplicate query output | Stored rows remain unchanged | DISTINCT, GROUP BY, UNION |
| Repair stored duplicates | Rows are updated, merged, archived, or deleted | ROW_NUMBER, transactions, constraints |
A report that shows one city value does not remove repeated city records from the table. A delete operation permanently changes data and requires a much stronger validation process.
Why Duplicate Handling Matters
Invalid duplicates inflate counts and totals, send repeated marketing messages, create conflicting customer profiles, consume storage, violate downstream assumptions, and make reconciliation difficult. Duplicate-looking rows can also be legitimate: two payments may have equal amounts, two tests may produce the same result, and two API requests may share status and duration.
The goal is not to make every visible result unique. It is to preserve every valid business fact while eliminating only records that violate a documented identity rule.
Types of Duplicates
Exact duplicates repeat every compared attribute. Business-key duplicates share the attributes that should be unique but may differ in technical ID, timestamp, or source. Presentation duplicates appear because a query selects too few columns. Join-generated duplicates arise when one-to-many relationships multiply a parent row. Near duplicates differ only through case, whitespace, punctuation, abbreviation, or formatting.
Each type needs a different response. A report may use DISTINCT for presentation rows, while stored business-key duplicates need a survivor rule and constraints.
Sample Customer Data
Assume a customer table contains customer_id, tenant_id, email, customer_name, status, created_at, and updated_at. The technical ID is unique, but the business might require one active customer per normalized email within a tenant.
That definition immediately answers several questions. The duplicate key is tenant plus normalized email, inactive historical records may or may not participate, and a survivor must be selected when several active rows exist.
Removing Output Duplicates with DISTINCT
DISTINCT returns one occurrence of each complete selected row.
SELECT DISTINCT city
FROM customers;
This returns each city once. It does not modify the customer table and does not identify which customers share a city.
DISTINCT with Multiple Columns
When several columns are selected, uniqueness applies to their complete combination.
SELECT DISTINCT city, status
FROM customers;
Chicago and ACTIVE differs from Chicago and INACTIVE. Adding customer_id, if already unique, makes every selected row unique and prevents DISTINCT from reducing anything.
DISTINCT Does Not Use the Primary Key Automatically
SQL does not infer a separate deduplication key. It compares all projected expressions. To obtain unique emails, select email. To obtain unique tenant-email combinations, select both. Do not project unrelated changing values when the desired result is one row per business key.
Removing Duplicates with UNION
UNION combines compatible query results and removes complete duplicate rows across the combined result.
SELECT customer_id, customer_name
FROM online_customers
UNION
SELECT customer_id, customer_name
FROM store_customers;
Rows collapse only when every selected value matches. If the same customer has different names, both rows remain.
UNION vs UNION ALL
UNION ALL preserves every occurrence and usually avoids global duplicate-elimination work. Use it for transactions, events, test executions, and logs where equal values can represent separate facts. Use UNION when a distinct combined projection is the requirement.
| Question | UNION | UNION ALL |
|---|---|---|
| Removes complete duplicates? | Yes | No |
| Preserves every event? | No | Yes |
| Typical additional work | Sort or hash distinct | Append |
Deduplicating a UNION ALL Result
An outer query can apply a deliberate distinct rule after source rows are normalized.
SELECT DISTINCT customer_id, customer_name
FROM (
SELECT customer_id, customer_name FROM online_customers
UNION ALL
SELECT customer_id, customer_name FROM store_customers
) AS all_customers;
This is useful when branches need source-specific transformations before one final uniqueness decision.
Removing Output Duplicates with GROUP BY
GROUP BY can produce one row per grouping key.
SELECT tenant_id, email
FROM customers
GROUP BY tenant_id, email;
Without aggregates, this can resemble DISTINCT. Use grouping when the query also needs counts, dates, or other summaries.
DISTINCT vs GROUP BY
DISTINCT directly states that the selected projection should be unique. GROUP BY states that rows should be organized into groups, usually for aggregation. Optimizers may produce similar plans for equivalent queries, but readability should follow intent.
Finding Duplicate Groups
Before removing anything, identify groups whose occurrence count exceeds one.
SELECT tenant_id,
LOWER(TRIM(email)) AS normalized_email,
COUNT(*) AS duplicate_count
FROM customers
WHERE status = 'ACTIVE'
GROUP BY tenant_id, LOWER(TRIM(email))
HAVING COUNT(*) > 1;
This reports duplicate keys, not the individual rows. Use the same key definition throughout detection, review, and prevention.
Finding Exact Duplicate Rows
Exclude the technical ID and audit timestamps if they are expected to differ.
SELECT tenant_id, email, customer_name, status,
COUNT(*) AS occurrences
FROM customers
GROUP BY tenant_id, email, customer_name, status
HAVING COUNT(*) > 1;
Choose columns based on business equality. Including every column can make technically distinct timestamps hide obvious duplicate profiles.
Counting Duplicate Records
Group count tells how many rows exist, but removable duplicates are usually count minus one survivor.
SELECT SUM(duplicate_count - 1) AS excess_rows
FROM (
SELECT COUNT(*) AS duplicate_count
FROM customers
GROUP BY tenant_id, LOWER(TRIM(email))
HAVING COUNT(*) > 1
) AS duplicate_groups;
Using ROW_NUMBER to Select a Survivor
ROW_NUMBER() ranks rows inside each duplicate group. Rows with rank greater than one are candidates for repair.
WITH ranked AS (
SELECT customer_id,
tenant_id,
email,
ROW_NUMBER() OVER (
PARTITION BY tenant_id, LOWER(TRIM(email))
ORDER BY updated_at DESC,
created_at DESC,
customer_id DESC
) AS duplicate_rank
FROM customers
WHERE status = 'ACTIVE'
)
SELECT *
FROM ranked
WHERE duplicate_rank > 1;
The ordering defines the survivor. Include deterministic tiebreakers so repeated runs choose the same row.
DISTINCT vs ROW_NUMBER
DISTINCT suppresses repeated output values but cannot choose a complete survivor row based on recency or quality. ROW_NUMBER preserves row identity and supports rules such as latest update, most complete profile, trusted source, or highest version.
Designing the Winner Rule
"Keep the latest" is not always sufficient. The latest row may be incomplete or created by an untrusted import. A production rule can prioritize verified status, trusted source, non-null attributes, update time, and stable ID.
ORDER BY is_verified DESC,
CASE source_system
WHEN 'CRM' THEN 1
WHEN 'IMPORT' THEN 2
ELSE 3
END,
updated_at DESC,
customer_id DESC
Document the policy and test ties. If no automatic rule is defensible, send groups to manual review.
Normalizing Case and Whitespace
Near duplicates often differ through capitalization or spaces.
SELECT LOWER(TRIM(email)) AS normalized_email,
COUNT(*)
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
Normalization should reflect the field's semantics and collation. Lowercasing case-sensitive identifiers or trimming significant fixed-format codes can merge distinct values incorrectly.
Calculated Duplicate Keys
Business identity may depend on several normalized expressions, such as tenant, uppercase tax ID without punctuation, and account category. Consider a generated column or stored canonical value when the same expensive expression is used repeatedly.
Building a Canonical Business Key
A durable duplicate-removal process begins with a canonical business key rather than a convenient column. A technical identifier such as customer_id distinguishes stored rows, but the business may consider two rows the same customer when their tenant, normalized email address, and account type match. The canonical key should express that rule exactly. It may use trimmed text, controlled case folding, standardized phone numbers, normalized dates, or a trusted external identifier, but every transformation must have a business justification.
Canonicalization is not the same as approximate matching. Converting " Alice@example.com " to lowercase trimmed text is deterministic. Deciding that two slightly different names represent the same person is entity resolution and normally requires confidence scores, manual review, or another verified attribute. Keeping these processes separate prevents an aggressive cleanup from merging unrelated records.
WITH canonical_customers AS (
SELECT customer_id,
tenant_id,
LOWER(TRIM(email)) AS canonical_email,
updated_at
FROM customers
)
SELECT tenant_id, canonical_email, COUNT(*) AS row_count
FROM canonical_customers
GROUP BY tenant_id, canonical_email
HAVING COUNT(*) > 1;
Before using a calculated key in production, profile how many rows have null or invalid components and sample the largest groups. A key that places every missing email into one group is not identifying customers; it is identifying missing data. Exclude incomplete records, add fallback attributes, or route them to a separate review process.
Defining Row Grain Before Deduplication
Row grain states what one row represents. An order table may have one row per order, while an order-line table has one row per product within an order. Grouping order lines only by order_id would incorrectly label valid lines as duplicates. State the grain in plain language, map it to columns, and verify that the selected columns truly identify that grain before writing cleanup SQL.
This step is especially important in reporting queries. Joining a one-row-per-customer table to orders changes the result grain to one row per customer-order combination. Repeated customer names in that result are expected, not defective. If the requirement is a customer list, use EXISTS, aggregation, or a deliberate distinct projection instead of treating the joined rows as corrupt data.
NULL Values and DISTINCT
Distinct operations generally return one null for a single projected nullable column. In multiple-column results, the complete tuple determines uniqueness. Rows (101, NULL) and (102, NULL) are different.
A unique constraint's treatment of null can vary by database product. Verify exact behavior before relying on it for nullable business keys.
Duplicates Created by JOINs
A parent row repeats when it joins to several child rows. That is often correct relational behavior, not duplicate stored data.
SELECT 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 appears five times. Adding DISTINCT can hide the multiplication, but the better query depends on intent.
Use EXISTS When Only Presence Matters
If the requirement is "customers with at least one order," EXISTS preserves customer grain naturally.
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
);
This avoids joining every child and then deduplicating the parent projection.
COUNT and COUNT DISTINCT
COUNT(*) counts rows, while COUNT(DISTINCT expression) counts distinct non-null expression values under product semantics.
SELECT COUNT(*) AS order_rows,
COUNT(DISTINCT customer_id) AS ordering_customers
FROM orders;
Composite distinct-count syntax varies. A grouped subquery is a portable way to count unique tuples.
Set Operators and Duplicate Analysis
INTERSECT finds common distinct rows, while EXCEPT or MINUS finds directional differences. They support reconciliation but do not repair stored data. UNION can conceal overlaps, while UNION ALL exposes every source occurrence for analysis.
Customer and Marketing Scenarios
Customer duplicates can cause conflicting preferences, repeated messages, and inaccurate lifetime value. Use tenant-aware stable keys and consent rules. Email alone may not be globally unique, shared households may use one address, and aliases may be legitimate.
Marketing audience output may use DISTINCT for one recipient per campaign, but master-data cleanup needs a separate governed merge process.
Sales, Banking, and Transaction Scenarios
Transaction rows that share customer, amount, and date are not automatically duplicates. Reliable deduplication usually needs a gateway reference, idempotency key, source event ID, or composite transaction identity.
Never delete financial records based only on visible equality. Preserve audit history, reversal relationships, settlement status, and regulatory retention.
Testing and API Scenarios
Test executions and API requests are occurrence data. Equal test names, statuses, and durations may represent separate runs. Use execution IDs, request IDs, build numbers, environments, and timestamps to define identity.
API clients should send idempotency keys for operations where retries must not create duplicate business effects. Database constraints should back the application rule.
Migration and ETL Scenarios
Ingestion should usually preserve raw records with source system, source key, batch ID, and ingestion time. A later transformation can identify duplicates and record the survivor decision. Removing rows during raw ingestion can erase evidence needed for reconciliation.
Measure duplicates by source and batch. A sudden increase may indicate a broken incremental watermark, replayed file, or retry defect.
Product, Student, and Inventory Scenarios
Product catalogs may contain repeated SKUs under different vendors or regions, so the correct key may be tenant, vendor, and SKU. Student systems may allow one person to have several program enrollments. Inventory movements must remain separate even when quantities match.
Model legitimate multiplicity explicitly rather than forcing uniqueness onto fields that are only locally unique.
Log Analysis
Repeated log messages usually represent separate events. Deduplicate only when transport or ingestion replay creates the same event more than once. Use a stable event ID or source offset where possible. Hashing message text alone can merge valid repeated events.
Safely Deleting Duplicate Records
Deletion syntax varies by database, but the safe process is consistent: detect groups, select survivors, preview candidate IDs, validate references, archive or log candidates, delete in a transaction, verify counts, and add prevention.
WITH ranked AS (
SELECT customer_id,
ROW_NUMBER() OVER (
PARTITION BY tenant_id, LOWER(TRIM(email))
ORDER BY updated_at DESC, customer_id DESC
) AS duplicate_rank
FROM customers
)
SELECT customer_id
FROM ranked
WHERE duplicate_rank > 1;
Run the candidate query and review it before adapting it to the target database's delete syntax.
Merge Before Delete
Duplicates may have child records, preferences, addresses, notes, or audit history. Reassign child references to the survivor and merge non-conflicting attributes before deletion. Foreign keys can reveal dependencies but cannot decide business precedence.
Record old-to-new ID mappings so external systems and audit investigations can trace merged identities.
Creating an Auditable Duplicate Repair Runbook
Production cleanup should be repeatable and reviewable. Start by assigning the repair a batch identifier. Store every candidate row, its duplicate group, its proposed survivor, the rule version, and the reason it lost. Record pre-change counts and important financial or operational totals. These records allow reviewers to approve the exact change set rather than a query whose results might change between executions.
CREATE TABLE duplicate_repair_audit (
repair_batch_id VARCHAR(50) NOT NULL,
duplicate_id BIGINT NOT NULL,
survivor_id BIGINT NOT NULL,
duplicate_key VARCHAR(500) NOT NULL,
rule_version VARCHAR(30) NOT NULL,
recorded_at TIMESTAMP NOT NULL,
PRIMARY KEY (repair_batch_id, duplicate_id)
);
A practical runbook separates discovery, approval, merge, deletion, validation, and prevention. Discovery can run repeatedly without changing data. Approval freezes candidate IDs. The merge phase moves dependencies and preserves useful attributes. Deletion removes only approved rows. Validation compares before-and-after controls, and prevention adds a constraint or corrects the ingestion path. Each phase should stop on unexpected counts rather than continuing with a partially understood data set.
For large tables, process bounded batches and checkpoint progress. A single massive transaction can retain locks, expand transaction logs, delay replication, and make recovery difficult. Batching does not remove the need for transactional correctness; it creates smaller units that can be validated, retried, and reconciled.
Choosing Survivors Deterministically
The survivor rule must produce the same winner every time. Ordering only by updated_at DESC is insufficient when two rows share a timestamp. Add a stable final tiebreaker, commonly the primary key. More advanced rules can prefer a verified record, a trusted source system, the most complete profile, or the row already referenced by the most dependencies before comparing timestamps.
Completeness scoring should be transparent. For example, award one point for each verified phone number, validated address, or non-null legal name, but do not let a low-value optional field override a verified identity. Store the score components or winner reason in the audit record so a later investigation can reproduce the decision.
ROW_NUMBER() OVER (
PARTITION BY tenant_id, canonical_email
ORDER BY is_verified DESC,
completeness_score DESC,
updated_at DESC,
customer_id DESC
) AS duplicate_rank
Transaction and Concurrency Safety
New duplicates can arrive between detection and deletion. Use an appropriate transaction isolation level, lock strategy, maintenance window, or staged repair process. Exact capabilities differ by database.
Add the uniqueness constraint as soon as cleanup allows. Otherwise, the race remains after the repair completes.
Handling Concurrent Writes and Retries
Duplicate repair is incomplete if the same write path can recreate the problem. Two application requests may both check that an email is absent and then insert it. That check-then-insert sequence has a race condition. The database must make the uniqueness decision atomically through a constraint, and the application must handle the resulting conflict as an expected outcome.
Retries create another common source of duplicates. A client may not receive the response even though the server committed the transaction, then repeat the request. For commands such as payment creation or order submission, accept an idempotency key scoped to the caller and operation. Persist the key with the created resource or response so a retry returns the original outcome instead of inserting again.
Message consumers need equivalent protection. A delivery system may provide at-least-once delivery, which means the same message can arrive more than once. Store a stable event identifier and reject or ignore an identifier that has already been processed within the required retention period. Do not infer identity from payload equality when two legitimate events can contain identical values.
Preventing Duplicates with Primary Keys
A primary key prevents repeated technical identity and disallows null keys. It does not prevent two rows with different IDs from sharing the same business key.
Preventing Duplicates with UNIQUE Constraints
A unique constraint enforces business uniqueness at the database boundary.
ALTER TABLE customers
ADD CONSTRAINT uq_customers_tenant_email
UNIQUE (tenant_id, normalized_email);
Store or generate a canonical email if the rule is based on normalization. Confirm null behavior and maximum index sizes for the target engine.
Composite and Conditional Uniqueness
Many rules involve multiple columns or only active records. Some products support filtered or partial unique indexes; others need generated columns, status-aware keys, or application design changes. Verify portability before choosing an implementation.
Idempotent Writes and Upserts
Constraints reject invalid repeats, but applications should also make retries safe. Use idempotency keys for external commands and product-supported upsert patterns when a repeated request should update or reuse an existing row.
Define conflict behavior explicitly. "Do nothing" and "update selected fields" represent different business rules.
Monitoring Duplicate Data Quality
After cleanup, treat duplicate rate as a quality metric rather than waiting for another incident. Schedule a detection query using the same canonical rule and report the number of duplicate groups, affected rows, maximum group size, and age of the oldest unresolved group. Break results down by source system, tenant, API client, import batch, or application version to identify where prevention is failing.
Use both absolute counts and rates. Ten duplicates may be severe in a batch of one hundred rows but negligible in a historical table containing billions, while even one duplicate payment may require immediate investigation. Thresholds should reflect business impact and the expected grain of each data set.
Monitor the constraint or upsert conflict rate as well. A sudden rise can expose a retry storm, broken client identifier, replayed file, or incorrect canonicalization rule even when the database successfully prevents duplicate storage. Prevention events are operational signals, not merely harmless errors.
Reconciling Results After Cleanup
A zero-row duplicate query proves only that the chosen rule no longer finds repeated keys. It does not prove that the cleanup preserved business meaning. Reconcile independent controls: total customer balances, order amounts, payment counts, active subscriptions, child-row counts, and orphan checks. Compare these controls before and after each repair batch.
Also validate downstream effects. Search indexes, caches, analytics warehouses, exports, and third-party systems may still reference removed identifiers. Publish the old-to-survivor mapping where authorized, refresh derived data, and confirm that consumers can resolve merged identities. This closes the repair across the system rather than only inside one table.
Performance of DISTINCT and GROUP BY
Duplicate elimination often uses sorting or hashing. Wide rows, large volumes, expensive collations, and insufficient memory increase cost and can cause temporary-storage spills. Select only required columns and filter early.
An index covering the duplicate key can support grouping or ordered processing, but it does not make unnecessary deduplication free.
ROW_NUMBER Performance
Ranking requires partitioning and ordering by duplicate keys and survivor criteria. An index aligned with those columns can help, but wide partitions may still require sorting. Test realistic skew because one extremely large duplicate group can behave differently from many small groups.
Reading an EXPLAIN Plan
Inspect scans, filters, sorts, hash aggregates, window operators, memory grants, spills, and row estimates. For join-generated duplicates, examine cardinality at each join rather than treating the final distinct stage as the problem.
Avoiding Unnecessary DISTINCT
Adding DISTINCT defensively can hide incorrect joins, missing predicates, or misunderstood grain. State why duplicates are expected and why output uniqueness is required. If a primary or unique key already guarantees the projection, distinct processing may be redundant.
Common Mistakes
- Assuming
DISTINCTuses only one selected column. - Using
UNION ALLwhen a distinct reference list is required. - Using
UNIONwhen every event must remain. - Ignoring null, case, whitespace, and collation behavior.
- Deleting legitimate duplicate-looking transactions.
- Using
DISTINCTto hide an incorrect join. - Choosing a survivor without deterministic ordering.
- Deleting parent duplicates before reassigning children.
- Cleaning data without adding prevention.
- Normalizing values more aggressively than the business rule allows.
- Running deletion without an audit trail or recovery plan.
- Assuming duplicate removal always improves performance.
Testing Checklist
Test no duplicates, one duplicate group, several groups, null keys, case and whitespace variants, tied survivor timestamps, child references, concurrent inserts, and large skewed groups. Confirm the selected winner and exact candidate IDs.
After cleanup, verify row counts, orphan checks, downstream totals, and constraint creation. Re-run detection and ensure the result is empty under the same rule.
Best Practices
- Define business identity and row grain explicitly.
- Separate output deduplication from stored-data repair.
- Detect and count groups before changing data.
- Use deterministic survivor ordering.
- Preserve lineage and merge child relationships.
- Normalize only according to documented semantics.
- Preview candidate IDs and use transactions.
- Retain an audit or archive of removed records.
- Add keys or unique constraints after cleanup.
- Make application retries idempotent.
- Inspect actual plans on realistic data.
- Monitor duplicate rates after deployment.
Interview Questions and Answers
What does DISTINCT do?
It returns one occurrence of each complete selected row without modifying stored data.
Does DISTINCT apply to all selected columns?
Yes. Uniqueness is based on the complete projected combination.
How do you find duplicate records?
Group by the duplicate key and use HAVING COUNT(*) > 1.
How do you keep the latest record?
Use ROW_NUMBER() partitioned by the duplicate key and ordered by update time plus a stable tiebreaker.
Does DISTINCT delete rows?
No. It only affects query output.
Why is UNION ALL often faster than UNION?
It normally avoids global duplicate-elimination work.
How can stored duplicates be prevented?
Use primary and unique constraints, canonical keys, idempotent writes, and correct transactional logic.
Is DISTINCT faster than GROUP BY?
Not inherently. Equivalent queries may receive similar plans; measure the target engine and data.
Practical Exercises
Find repeated emails within each tenant, count excess rows, select the latest verified customer, and identify duplicate products by vendor and SKU. Then create a candidate-delete query without executing it and design the unique constraint that prevents recurrence.
Compare a join plus DISTINCT with an EXISTS query, and compare UNION with UNION ALL on transaction data. Explain which repeated rows are legitimate.
Technique Selection Guide
| Requirement | Recommended starting point |
|---|---|
| Unique query projection | DISTINCT |
| Unique groups with metrics | GROUP BY |
| Choose one complete survivor row | ROW_NUMBER() |
| Distinct combined sources | UNION |
| Check related-row presence | EXISTS |
| Prevent invalid stored repeats | Unique constraint |
Quick Reference
-- Unique output rows
SELECT DISTINCT key1, key2 FROM source;
-- Detect duplicate groups
SELECT key1, key2, COUNT(*)
FROM source
GROUP BY key1, key2
HAVING COUNT(*) > 1;
-- Rank survivors
ROW_NUMBER() OVER (
PARTITION BY key1, key2
ORDER BY quality DESC, updated_at DESC, id DESC
)
Key Takeaway
Removing duplicates in SQL is not one operation. Output deduplication, duplicate detection, survivor selection, stored-data repair, and future prevention are separate responsibilities. DISTINCT, GROUP BY, set operators, and window functions solve different parts of the workflow.
Start with business identity and row grain. Preserve legitimate repeated facts, normalize carefully, choose survivors deterministically, merge dependencies, audit every change, and enforce the rule with database constraints and idempotent writes. That approach produces trustworthy results without using uniqueness as a mask for deeper data or query defects.