7 Different Types of Duplicate Data and How to Remove Each

1. Exact duplicates -- byte-for-byte identical rows

The simplest case: two rows identical across every column except an auto-increment ID, usually caused by a webhook or form submission that fired twice. GROUP BY every business column and count the groups:

SELECT email, name, source, created_at, COUNT(*) AS dup_count
FROM signups
GROUP BY email, name, source, created_at
HAVING COUNT(*) > 1;

Removal keeps one survivor per group -- here, the lowest ID, since id is a non-nullable primary key so NOT IN is safe (it silently breaks the moment the inner set can contain a NULL -- see 10 SQL Queries That Look Correct but Produce Wrong Results for that trap):

DELETE FROM signups
WHERE id NOT IN (
  SELECT MIN(id) FROM signups GROUP BY email, name, source, created_at
);

2. Business-key duplicates with a different ID (live example)

Two rows that don't match on every column, but share the real-world key that should be unique -- the same customer email inserted a second time weeks later, each with its own auto-increment ID. A plain GROUP BY on all columns won't find these, because the columns genuinely differ (a new ID, a new timestamp). You have to group by the business key alone and decide which row survives:

The same ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) pattern above also naturally absorbs the case/whitespace duplicate in this dataset, because the partition key is normalized with LOWER(TRIM(email)) rather than the raw column -- one fix, three duplicate patterns solved at once.

3. Case and whitespace duplicates

As you just saw above, 'anna@acme.com' and 'ANNA@ACME.COM ' are the same person to a human and two completely different strings to a database. Plain GROUP BY email or a naive JOIN ... ON a.email = b.email treats them as unrelated. Normalize before comparing -- LOWER(TRIM(email)) -- on both sides of any join or grouping that depends on that column matching, not just one.

4. Duplicates created by a JOIN (not real duplicates)

Sometimes there's nothing wrong with the data at all -- a one-to-many JOIN evaluated before aggregation repeats a parent row once per matching child row, and the output looks exactly like duplicate data even though the base tables are perfectly clean. This is a query bug, not a data bug, and DELETE is the wrong tool for it. Full breakdown, live example, and an interactive visualizer: Why Is My SQL SUM() Wrong After a JOIN?, How a SQL JOIN Can Quietly Multiply Your Revenue, and the SQL JOIN Fan-Out Visualizer.

5. Cross-system duplicates after a merge

The same customer exists in two source systems -- a CRM and a billing system, say -- each with its own surrogate ID and no shared key, merged into one warehouse table during an ETL run. There's no single column to GROUP BY; you need a composite match on normalized attributes (name, phone, domain) and a priority rule for which source wins when they disagree:

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY LOWER(TRIM(email)), RIGHT(phone, 4)
    ORDER BY CASE source WHEN 'billing_system' THEN 1 ELSE 2 END
  ) AS rn
FROM merged_customers;
-- keep rn = 1: billing_system wins ties, treated as the source of truth

6. Fuzzy / near-duplicate typos

"Jon Smith" and "John Smith" are almost certainly the same person, but no exact or normalized match will ever catch them -- this category needs similarity scoring, not equality. Most engines ship something for this: Postgres has the pg_trgm extension with SIMILARITY(), Snowflake and BigQuery have EDIT_DISTANCE(), and SQL Server has no built-in equivalent (a CLR function or app-layer library is the usual workaround). Treat fuzzy matches as candidates for human review rather than auto-merging -- a similarity threshold that's loose enough to catch real typos will also catch some genuinely different people.

7. Retry duplicates in an event log

An append-only event or webhook log where a network retry re-sent the same event, producing two log rows with the same event_id but different ingested_at timestamps. Detection is a simple GROUP BY event_id HAVING COUNT(*) > 1; removal should keep whichever occurrence your business logic actually needs -- usually the first, since it reflects when the event genuinely happened:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY event_id ORDER BY ingested_at ASC
  ) AS rn
  FROM event_log
)
DELETE FROM event_log WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- The safest pattern going forward is an idempotency key checked at insert time,
-- so the duplicate is rejected before it ever reaches the table.

Challenge: dedupe the signups table

Same signups dataset used in the live example above. Three tasks -- find the exact-duplicate groups, keep the most recent record per real person, and find who resubscribed under the same email on a genuinely later date.