How To Find Duplicates In Sql

Finding duplicate records is one of the most common data quality tasks a database developer or analyst faces. Even so, whether you are cleaning a legacy table, preparing data for a unique index, or auditing user sign-ups, identifying rows that share identical values across specific columns is a fundamental SQL skill. The approach you choose depends heavily on your database engine (PostgreSQL, MySQL, SQL Server, Oracle), the size of the dataset, and whether you simply need a report or intend to delete the offending rows Nothing fancy..

Understanding What Constitutes a Duplicate

Before writing a query, you must define "duplicate" in the context of your specific business logic. Now, a duplicate is rarely an exact carbon copy of every column in a row—primary keys or auto-incrementing IDs usually prevent that. Instead, duplicates typically occur on a subset of columns known as the business key or natural key.

As an example, in a users table, a duplicate might be defined as two rows sharing the same email address. In an orders table, it might be the same customer_id, product_id, and order_date combination. Identifying this grain—the specific column combination that should be unique—is the critical first step The details matter here..

The Classic GROUP BY and HAVING Approach

The most universal, ANSI-SQL compliant method for finding duplicates uses the GROUP BY clause combined with HAVING. This works on virtually every relational database system Took long enough..

The logic is straightforward: group the data by the suspect columns, count the occurrences in each group, and filter for groups where the count is greater than one Nothing fancy..

SELECT 
    email, 
    COUNT(*) AS duplicate_count
FROM 
    users
GROUP BY 
    email
HAVING 
    COUNT(*) > 1;

Why this works: The database engine aggregates all rows sharing the same email into a single bucket. The HAVING clause acts as a filter after aggregation, discarding buckets with only a single member The details matter here..

Limitations: This query returns the duplicate values and their frequency, but it does not return the full rows (like user_id, created_at, name) associated with those duplicates. To see the full rows, you must join this result back to the original table.

Joining Back for Full Row Details

To inspect the actual duplicate records—perhaps to decide which one to keep—you can use a Common Table Expression (CTE) or a subquery Worth keeping that in mind..

WITH duplicate_emails AS (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
SELECT 
    u.user_id, 
    u.email, 
    u.name, 
    u.created_at
FROM 
    users u
INNER JOIN 
    duplicate_emails de ON u.email = de.email
ORDER BY 
    u.email, u.created_at;

This pattern is highly readable and performs well on moderately sized tables with proper indexing on the grouped columns.

Leveraging Window Functions: ROW_NUMBER()

Modern SQL engines (PostgreSQL, SQL Server, Oracle, MySQL 8.0+, SQLite) support window functions, which offer a more powerful and flexible way to handle duplicates. The ROW_NUMBER() function assigns a sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.

This allows you to rank duplicates instantly. The first occurrence gets a 1, the second a 2, and so on.

WITH ranked_users AS (
    SELECT 
        user_id,
        email,
        name,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS row_num
    FROM 
        users
)
SELECT 
    user_id, 
    email, 
    name, 
    created_at, 
    row_num
FROM 
    ranked_users
WHERE 
    row_num > 1;

Key Advantages:

1. Single Pass: It scans the table once (conceptually), whereas the GROUP BY + JOIN method often requires two passes or a temp table spool.
2. Total Control: The ORDER BY clause inside OVER() lets you define exactly which duplicate is "Row 1" (the keeper) and which are "Row 2+" (the duplicates). In the example above, ORDER BY created_at ASC keeps the oldest account.
3. Deletion Ready: This CTE structure is the perfect precursor for a DELETE statement. You can simply change the final SELECT to DELETE FROM users WHERE user_id IN (SELECT user_id FROM ranked_users WHERE row_num > 1);.

RANK() vs DENSE_RANK() vs ROW_NUMBER()

While ROW_NUMBER() is the standard for deduplication (it guarantees unique integers 1, 2, 3...* DENSE_RANK(): Does not skip numbers (1, 2, 2, 3). ), it is worth knowing the alternatives:

• RANK(): Skips numbers after ties (1, 2, 2, 4). Also risky for deletion if you assume row_num = 1 is a single row. Useful for reporting standings, dangerous for deletion logic. Here's the thing — * ROW_NUMBER(): Always unique (1, 2, 3, 4). **Always use this for deduplication.

Finding Duplicates Across Multiple Columns

Real-world deduplication rarely relies on a single column. In real terms, g. Consider this: you often need to check a composite key (e. , first_name + last_name + date_of_birth).

Both methods scale naturally to multiple columns.

Using GROUP BY:

SELECT 
    first_name, 
    last_name, 
    date_of_birth, 
    COUNT(*)
FROM 
    customers
GROUP BY 
    first_name, 
    last_name, 
    date_of_birth
HAVING 
    COUNT(*) > 1;

Using Window Functions:

WITH ranked_customers AS (
    SELECT 
        customer_id,
        first_name,
        last_name,
        date_of_birth,
        ROW_NUMBER() OVER (
            PARTITION BY first_name, last_name, date_of_birth 
            ORDER BY customer_id
        ) AS rn
    FROM 
        customers
)
SELECT * FROM ranked_customers WHERE rn > 1;

Note that in the PARTITION BY clause, you simply list all columns that define the uniqueness constraint.

Handling NULL Values in Duplicate Checks

A frequent pitfall involves NULL values. In standard SQL, NULL = NULL evaluates to Unknown (not True). That said, GROUP BY and PARTITION BY treat all NULLs as a single group.

If your phone_number column allows NULLs, and you have 50 users with no phone number, the queries above will flag "NULL" as a massive duplicate group with a count of 50 And that's really what it comes down to..

To exclude NULLs from duplicate detection: Add a WHERE clause before grouping or partitioning That's the part that actually makes a difference..

-- GROUP BY approach
WHERE phone_number IS NOT NULL

-- Window Function approach
WHERE phone_number IS NOT NULL

To treat NULLs as distinct values (never duplicates): This requires more complex logic, often using a surrogate key or concatenating a placeholder string, but usually, the business rule dictates that missing data shouldn't trigger a duplicate alert And it works..

Performance Considerations for Large Tables

On tables with millions of rows, "find duplicates" queries can be resource-intensive.

Indexing Strategy

The single most effective optimization is a covering index on the duplicate key columns.

• For GROUP BY email: `CREATE INDEX idx_users_email ON users(email);

Deleting Duplicates: Safe and Efficient Methods

Once duplicates are identified, the next step is removal. The safest approach always uses a unique, immutable identifier (like a primary key) to decide which row to keep. The general principle is to retain the oldest or most "trusted" record and delete the rest.

Method 1: The ROW_NUMBER() Approach (Recommended)

Basically the most common and often safest method. It assigns a unique number to each row within a duplicate group, allowing you to delete all rows except the one you want to keep (typically the one with the lowest or highest ID) That's the whole idea..

To keep the row with the MIN(customer_id):

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM (
        SELECT 
            customer_id,
            ROW_NUMBER() OVER (
                PARTITION BY first_name, last_name, date_of_birth 
                ORDER BY customer_id
            ) AS rn
        FROM customers
    ) AS ranked
    WHERE rn > 1
);

Why the Triple Nesting? You cannot directly use a window function in a WHERE clause or a subquery that is then used in a DELETE statement in many database systems (like MySQL). The innermost subquery (ranked) is necessary to materialize the results with the rn column, making the outer query valid Turns out it matters..

Method 2: The Self-Join Approach

This method uses a JOIN to compare rows against each other. It's effective when your table has a very clear ordering column.

DELETE c1 
FROM customers AS c1
INNER JOIN customers AS c2
WHERE 
    c1.customer_id > c2.customer_id -- Keep the one with the smaller ID
    AND c1.first_name = c2.first_name
    AND c1.last_name = c2.last_name
    AND c1.date_of_birth = c2.date_of_birth;

This deletes c1 if it finds another row c2 with the same key but a smaller customer_id. It's concise but can be less intuitive for complex composite keys.

Handling NULLs During Deletion

The deletion logic must handle NULLs consistently with your detection logic. If you excluded NULLs during detection, you must ensure the deletion query does the same. Add the same IS NOT NULL filter to both the main table and the subquery/join condition.

Example with ROW_NUMBER():

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM (
        SELECT 
            customer_id,
            ROW_NUMBER() OVER (
                PARTITION BY first_name, last_name, date_of_birth 
                ORDER BY customer_id
            ) AS rn
        FROM customers
        WHERE phone_number IS NOT NULL -- Exclude NULLs here
    ) AS ranked
    WHERE rn > 1
);

Transaction and Backup: Your Safety Net

Always wrap duplicate deletion in a transaction. This allows you to roll back if the query has an unexpected effect.

BEGIN TRANSACTION; -- Or START TRANSACTION;

-- Your DELETE query here

-- Review the number of affected rows and spot-check a few deleted records.
-- If everything looks correct:
COMMIT;

-- If anything is wrong:
-- ROLLBACK;

For large tables, consider creating a backup table or copying the rows to be deleted to an archive table before the final deletion Simple, but easy to overlook..

Conclusion

Deduplication is a critical data maintenance task that requires a methodical approach. By understanding the nuances of ROW_NUMBER(), RANK(), and DENSE_RANK(), you can accurately identify duplicates. Handling composite keys and NULL values is essential for real-world data. Even so, finally, always use a safe deletion strategy—preferably the ROW_NUMBER() method within a transaction—to protect data integrity. A well-executed deduplication process cleans your database, improves query performance, and prevents analytical errors caused by inflated counts.