How To Remove Duplicate Data In Sql

Duplicate records are one of the most persistent challenges in database management. Now, whether caused by missing primary keys, faulty application logic, or bulk data imports without validation, redundant rows inflate storage costs, skew analytical reports, and degrade query performance. Which means understanding how to remove duplicate data in SQL is a fundamental skill for developers, data analysts, and database administrators alike. This guide explores the most effective techniques—ranging from modern window functions to classic grouping methods—ensuring you can clean your tables efficiently regardless of your specific RDBMS.

Short version: it depends. Long version — keep reading.

Understanding the Nature of Duplicates

Before writing a DELETE statement, you must define what constitutes a "duplicate" in your specific context. There are generally two categories:

1. Exact Duplicates: Every column value in one row matches every column value in another row. This often happens in staging tables lacking a primary key.
2. Logical Duplicates (Key-Based): A specific subset of columns (a business key) identifies a unique entity, but other columns (like timestamps, auto-increment IDs, or status flags) differ. As an example, a users table might have two rows for email = 'john@example.com' but different user_id values.

Identifying the grain of your data—what makes a row unique—is the prerequisite for any safe deletion strategy. Always back up your table or run a transaction block (BEGIN TRANSACTION ... ROLLBACK) before executing destructive operations on production data.

Method 1: Using ROW_NUMBER() (The Modern Standard)

For most modern relational databases (PostgreSQL, SQL Server, Oracle, MySQL 8.0+, SQLite), the ROW_NUMBER() window function is the gold standard. It assigns a sequential integer to rows within a partition of a result set, allowing you to surgically target only the redundant copies while preserving a single "canonical" row.

The Logic

You partition the data by the columns that define uniqueness (the business key) and order by a column that determines which row to keep (e.g., the earliest created_at or the lowest id) Not complicated — just consistent..

Syntax Pattern

WITH CTE_Duplicates AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY column1, column2, column3 -- The Business Key
            ORDER BY id ASC -- Criteria to keep (e.g., first inserted)
        ) AS rn
    FROM your_table
)
DELETE FROM your_table
WHERE id IN (
    SELECT id FROM CTE_Duplicates WHERE rn > 1
);

Practical Example

Imagine a customer_leads table where email should be unique, but imports created doubles. You want to keep the oldest record (lowest lead_id) That's the part that actually makes a difference..

WITH RankedLeads AS (
    SELECT 
        lead_id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY lead_id ASC) AS row_num
    FROM customer_leads
)
DELETE FROM customer_leads
WHERE lead_id IN (SELECT lead_id FROM RankedLeads WHERE row_num > 1);

Why this wins: It handles logical duplicates perfectly. You retain full control over which survivor remains (oldest, newest, most complete profile) via the ORDER BY clause inside the OVER() clause And that's really what it comes down to. Less friction, more output..

Method 2: The GROUP BY and MIN/MAX Approach (Universal Compatibility)

If you are on an older database version that lacks window function support (like MySQL 5.7 or older SQL Server versions), or if you prefer a set-based approach without CTEs, the GROUP BY method is the classic workaround.

The Logic

Identify the unique key columns, group by them, and select the minimum (or maximum) primary key value for each group. These minimum IDs represent the rows to keep. Everything else with the same key but a higher ID is a duplicate.

Syntax Pattern

DELETE FROM your_table
WHERE id NOT IN (
    SELECT MIN(id) 
    FROM your_table 
    GROUP BY column1, column2, column3 -- The Business Key
);

Critical Caveats

• NULL Handling: GROUP BY treats NULL as a distinct value group in standard SQL, but behavior varies. If your key columns allow NULLs, two rows with NULL in the key column might be grouped together unexpectedly. Use COALESCE or IS NOT DISTINCT FROM (PostgreSQL) if NULLs are part of your key logic.
• Performance: On massive tables (millions of rows), the NOT IN subquery can be slow. A LEFT JOIN / IS NULL pattern or NOT EXISTS is often more performant:

DELETE t1
FROM your_table t1
LEFT JOIN (
    SELECT MIN(id) as keep_id
    FROM your_table
    GROUP BY column1, column2, column3
) t2 ON t1.id = t2.keep_id
WHERE t2.keep_id IS NULL;

Method 3: Handling Exact Duplicates (No Primary Key)

Tables without a primary key or unique identifier require a different tactic because you cannot reference a specific id to delete. You cannot say "delete this specific row" if two rows are bit-for-bit identical.

Technique: SELECT DISTINCT into a New Table

The safest, most performant way to deduplicate a heap table (a table without a clustered index/PK) is to rebuild it.

-- 1. Create a clean copy
CREATE TABLE your_table_clean AS
SELECT DISTINCT * FROM your_table;

-- 2. Verify counts
SELECT COUNT(*) FROM your_table;
SELECT COUNT(*) FROM your_table_clean;

-- 3. Swap (requires downtime/lock)
DROP TABLE your_table;
ALTER TABLE your_table_clean RENAME TO your_table;

-- 4. Immediately add a Primary Key!
ALTER TABLE your_table ADD COLUMN id SERIAL PRIMARY KEY; -- Postgres syntax
-- ALTER TABLE your_table ADD id INT AUTO_INCREMENT PRIMARY KEY; -- MySQL syntax

Technique: ctid (PostgreSQL Specific) or ROWID (Oracle)

Some systems expose a physical location pointer. In PostgreSQL, ctid represents the physical tuple location.

DELETE FROM your_table
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM your_table
    GROUP BY column1, column2, column3 -- All columns for exact dupes
);

Note: Relying on physical pointers is generally discouraged for application logic but acceptable for one-off maintenance tasks.

Method 4: The MERGE Statement (Upsert Logic for Prevention)

While the prompt focuses on removal, the best strategy is often prevention during ingestion. The SQL MERGE statement (supported in SQL Server, Oracle, PostgreSQL 15+, DB2) allows you to "upsert" (Update or Insert) data from a staging table into a target table, effectively preventing duplicates from ever landing in your production table Still holds up..

MERGE INTO target_table t
USING staging_table s
ON t.business_key = s.business_key
WHEN MATCHED THEN
    UPDATE SET t.col1 = s.col1, t.updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (business_key, col1, created_at) VALUES (s.business_key, s.col1, NOW());

This pattern is essential for ETL pipelines. If you find yourself constantly running delete scripts, refactor your ingestion layer to use MERGE or INSERT ... In practice, oN CONFLICT (PostgreSQL/MySQL) / INSERT ... ON DUPLICATE KEY UPDATE (MySQL) That alone is useful..

Advanced Scenario: Fuzzy Duplicates and Data Quality

Sometimes duplicates aren't exact. "John Smith"

and "Jon Smith" or "John Smyth" represent the same person. This goes beyond SQL into data quality and fuzzy matching.

Techniques for Fuzzy Matching:

1. SOUNDEX() - Phonetic similarity (limited to English)
2. DIFFERENCE() - Soundex-based numeric comparison
3. LEVENSHTEIN() - Edit distance (available in some databases/extensions)
4. Custom Algorithms - Trigrams, Jaro-Winkler (often implemented in application code)

-- Example using SOUNDEX (SQL Server/PostgreSQL)
DELETE FROM customers c1
USING customers c2
WHERE c1.id > c2.id 
  AND SOUNDEX(c1.name) = SOUNDEX(c2.name)
  AND ABS(c1.created_at - c2.created_at) < INTERVAL '30 days';

Warning: Fuzzy matching is computationally expensive and can produce false positives. Always validate results manually before deletion.

Performance Considerations

• Index Strategy: Ensure columns used in GROUP BY or JOIN clauses are indexed
• Batch Processing: For large tables, delete in chunks to avoid long locks
• Temporary Tables: Use CREATE TEMP TABLE for intermediate results
• Analyze Before Delete: Run ANALYZE to update statistics after major data changes

Conclusion

Duplicate data is a persistent challenge that manifests differently across database schemas and requirements. Whether dealing with simple primary key duplicates or complex fuzzy matches, the key is selecting the right tool for your specific scenario:

• Primary keys present? Use self-joins with NOT IN or NOT EXISTS
• No identifiers? Rebuild with SELECT DISTINCT or use physical pointers
• Preventing future issues? Implement upsert patterns with MERGE or conflict resolution clauses
• Subtle duplicates? Employ fuzzy matching with caution

The most efficient approach combines preventive measures during data ingestion with regular maintenance routines. Here's the thing — always test deduplication scripts on a backup copy first, verify row counts before and after, and consider the business impact of false-positive deletions. As your data volume grows, invest in solid ETL processes that address duplicates at the source rather than relying on periodic cleanup operations And that's really what it comes down to. Turns out it matters..