How to Find and Remove Duplicate Rows in SQL

Duplicates sneak in from double form submissions, repeated imports and merged files. They inflate counts, skew averages and make every number you report slightly wrong. The cleanup has two steps: find them, then remove them.

Spotting Duplicates

The idea is simple. Group the rows by the column that should be unique, and look for groups with more than one row. Here is a subscribers table from a newsletter signup.

SELECT email, COUNT(*) AS copies
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1;

Output:

Code Explanation:

• GROUP BY email puts all rows with the same address into one group.
• COUNT(*) counts how many rows each group has.
• HAVING COUNT(*) > 1 keeps only the groups that appear more than once. Divya is single, so she is left out.

Removing the Extra Copies

The usual rule is to keep one row per email, the one with the smallest id, and delete the rest.

DELETE FROM subscribers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM subscribers
    GROUP BY email
);

Output:

DELETE 3

The table now looks like this.

Code Explanation:

• The inner query finds the smallest id for each email, which gives 1, 2 and 4.
• The outer DELETE removes every row whose id is not on that list, which means rows 3, 5 and 6.
• The message confirms that three rows were deleted.

MySQL won't let you read from the same table you're deleting from. If you hit that error, wrap the inner query in another SELECT and give it an alias.

Before You Delete

Run the inner logic as a SELECT first and check what would be removed. A deleted row cannot be brought back unless you have a backup.

Also watch for duplicates that look different. [email protected] and [email protected] (with a trailing space) are treated as two different values. Grouping by LOWER(TRIM(email)) catches these.

If you only need a clean view for analysis and do not want to touch the table, SELECT DISTINCT does the job without deleting anything.