Finding and Removing Duplicates

Concept

"Duplicate" is a business definition, not a technical one. Two rows with different ids can be the same customer. Always state the duplicate key first - same email, same name and city - then everything else follows.

Step 1: find them

-- Which values occur more than once?
SELECT email, COUNT(*) AS copies
FROM   employees
WHERE  email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

-- Composite duplicate key
SELECT name, city, COUNT(*) AS copies
FROM   customers
GROUP BY name, city
HAVING COUNT(*) > 1;

Step 2: see the actual rows

-- Every row belonging to a duplicated key, numbered
WITH dupes AS (
    SELECT id, email, first_name, hire_date,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY hire_date, id) AS rn,
           COUNT(*)     OVER (PARTITION BY email)                        AS copies
    FROM   employees
    WHERE  email IS NOT NULL
)
SELECT * FROM dupes WHERE copies > 1 ORDER BY email, rn;

This is the query to run before any delete. rn = 1 is the row you intend to keep - the earliest hire, in this case - and every row with rn > 1 is a candidate for removal. Check them by eye first.

Step 3: delete all but one

-- Portable and explicit: keep the lowest id per email
DELETE FROM employees
WHERE  email IS NOT NULL
  AND  id NOT IN (SELECT keep_id FROM (
           SELECT MIN(id) AS keep_id
           FROM   employees
           WHERE  email IS NOT NULL
           GROUP BY email) k);

The extra nesting is not decoration. MySQL refuses to read from the table it is deleting from in a subquery; wrapping it in a derived table forces the result to be materialised first and makes the statement legal.

-- PostgreSQL and SQL Server: delete straight from a CTE
WITH dupes AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY hire_date, id) AS rn
    FROM   employees
    WHERE  email IS NOT NULL
)
DELETE FROM employees
WHERE  id IN (SELECT id FROM dupes WHERE rn > 1);

-- MySQL 8: delete through a self join
DELETE e1 FROM employees e1
JOIN   employees e2
  ON   e1.email = e2.email
 AND   e1.id    > e2.id;

Step 4: stop it happening again

ALTER TABLE employees ADD CONSTRAINT uq_employees_email UNIQUE (email);

Without this final step you will be running the same cleanup again next quarter. The constraint is the actual fix; the delete is just tidying up.

Deduplicating on the way in

-- Insert only rows that are not already present
INSERT INTO customers (id, name, city, country, signup_date)
SELECT s.id, s.name, s.city, s.country, s.signup_date
FROM   staging_customers s
WHERE  NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = s.id);

-- Or let the unique constraint decide
INSERT IGNORE INTO customers ...                       -- MySQL: skips conflicts
INSERT ... ON CONFLICT (id) DO NOTHING;                -- PostgreSQL, SQLite

Important rules

• NULLs do not group as duplicates of a value, but they do group with each other under GROUP BY. Decide whether NULL rows count as duplicates.
• MySQL cannot reference the target table in a subquery of a DELETE without a derived table wrapper.
• DELETE with a join is MySQL syntax; PostgreSQL uses USING, SQL Server uses FROM.
• Deleting duplicates can violate foreign keys if children reference the rows being removed - repoint them first.
• A unique index cannot be added while duplicates remain.

Common mistakes

• Deleting before inspecting, and removing the wrong copy.
• Not defining what "duplicate" means, then deleting rows that were legitimately distinct.
• Cleaning up without adding the constraint, guaranteeing a repeat.
• Forgetting child rows, which either blocks the delete or orphans data.
• Using SELECT DISTINCT in a report to hide duplicates rather than fixing the data.

Best practices

• Write the duplicate definition down as a sentence before writing any SQL.
• Always run the numbered inspection query first, and keep its output.
• Do the delete inside a transaction where the engine allows it, and check the row count before committing.
• Repoint child rows to the surviving parent before deleting.
• Add the UNIQUE constraint in the same migration as the cleanup.

Practice

1. Find customers duplicated by name and city in the sample data.
2. Write the query that shows every duplicate row numbered, keeping the earliest signup.
3. Write the delete that keeps the lowest id, then the constraint that prevents recurrence.