Data Cleansing in SQL: Find and Remove Duplicate Rows

A table of 2,000,000 scraped contacts with 500,000 exact duplicates takes about seven seconds to clean. The row count drops to 1,500,000. The table on disk stays at 187 MB, byte for byte, through a routine VACUUM as well. That gap between "the duplicates are gone" and "the table got smaller" is the first of several places where deduplication behaves differently from the way it reads.

Duplicates are the most common defect in any dataset, and they are worst in scraped data. The same listing gets captured twice from overlapping pagination. A retry writes a row the first attempt already wrote. Two crawling runs land in one table a week apart. Data cleansing in SQL starts here: decide what "the same row" means, find the groups, print the ids, then delete the extras without touching the copy you meant to keep.

Every query below was re-checked against vendor documentation on 13 August 2026, for MySQL 8.4.11, PostgreSQL 18.4, SQL Server 2025, Oracle AI Database 26ai and SQLite 3.45. Timings come from a local PostgreSQL 16.13 instance, with the method given in the measurements section.

Throughout, assume a simple table:

CREATE TABLE contacts (
  id    INT PRIMARY KEY,
  name  VARCHAR(255),
  email VARCHAR(255)
);

Decide what counts as the same row

Your engine already made this decision for you. GROUP BY name does not compare strings the way you compare them. It compares them the way the column collation says to, and the defaults differ sharply between engines.

MySQL's utf8mb4_0900_ai_ci is the default collation for utf8mb4, and the manual is explicit about the suffixes: it "is based on UCA 9.0.0 and CLDR v30, is accent-insensitive and case-insensitive." On MySQL, Ada, ada and Áda collapse into one group before you write a single normalizing function. On PostgreSQL with a deterministic collation they are three groups. We checked on PostgreSQL 16 in a UTF-8 database: SELECT 'Ada' = 'ada' returns false, and so does SELECT 'Ada' = 'Áda'.

Now the twist that catches people migrating. MySQL's UCA 9.0.0 collations carry a NO PAD attribute, and the character set documentation gives the consequence in one line: "'a' and 'a ' compare as different strings, not the same string." Collations built on earlier UCA versions pad with spaces and treat them as equal. A table created under MySQL 5.7 defaults and one created under 8.0 defaults answer the same duplicate query differently, and neither tells you.

Then pick the identity columns, which for scraped listings is rarely one field and rarely the display name. A product is the pair of source site and SKU. A contact is a normalized email. A job posting is a canonical URL with the tracking parameters stripped. Two follow-up questions get their own sections below: what happens to NULLs, and which copy survives.

Step 1: Find the duplicate groups

The classic approach groups by the columns that should be unique and keeps only groups that appear more than once:

SELECT name, COUNT(*) AS duplicate_count
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

HAVING filters groups after aggregation. WHERE filters rows before it. That is why the count test lives in HAVING, and why the alias does not work there. The PostgreSQL manual states the rule directly: "An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead." Writing HAVING duplicate_count > 1 on PostgreSQL 16 gives ERROR: column "duplicate_count" does not exist. MySQL accepts it. Write COUNT(*) twice and the query is portable.

This tells you what is duplicated, not which rows. For that you need ids.

Step 2: Print out the ids of the duplicates

Adding id to the SELECT above fails, because once you GROUP BY name there are several ids per group. PostgreSQL says so precisely: ERROR: column "contacts.id" must appear in the GROUP BY clause or be used in an aggregate function. The fix is to aggregate the ids into one value per group, which every engine spells differently.

MySQL uses GROUP_CONCAT:

SET SESSION group_concat_max_len = 1000000;

SELECT name,
       COUNT(*) AS duplicate_count,
       GROUP_CONCAT(id ORDER BY id) AS ids
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

That first line is not decoration. The MySQL aggregate function reference says the result "is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024," with the effective ceiling then set by max_allowed_packet. A group of 200 nine-digit ids overflows 1024 bytes. The query succeeds, the list comes back short, and the ids that fell off the end are the rows you never delete.

The other three engines change one line of that query and leave the rest alone:

-- PostgreSQL, or ARRAY_AGG when you want a real array
STRING_AGG(id::text, ',' ORDER BY id) AS ids

-- SQL Server 2017 and later
STRING_AGG(CAST(id AS varchar(max)), ',') WITHIN GROUP (ORDER BY id) AS ids

-- Oracle: the overflow clause is the part worth typing
LISTAGG(id, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT)
  WITHIN GROUP (ORDER BY id) AS ids

Two corrections against older write-ups, this article's own earlier version included. The STRING_AGG(CAST(id AS varchar(20)), ',') that circulates everywhere returns varchar(8000) in whatever order the engine felt like, and the Microsoft reference publishes the return-type table that says so. Bare LISTAGG is not safe on real data either: Oracle documents that ON OVERFLOW ERROR "is the default" and raises ORA-01489.

One row per duplicated name, with the offending ids collected into a string:

+--------------+-----------------+----------------+
| name         | duplicate_count | ids            |
+--------------+-----------------+----------------+
| Ada Lovelace | 2               | 1148,1149      |
| Alan Turing  | 2               | 1201,1202      |
| Grace Hopper | 3               | 1178,1179,1190 |
+--------------+-----------------+----------------+

Now you can see exactly which rows to review. Read them before you delete them.

Step 3: Duplicates across several columns

Real duplicates rarely hinge on one field. Two contacts are the same only when name and email both match, so group by every column that defines identity:

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

Near-duplicates that differ by case or whitespace need normalizing inside the group:

GROUP BY LOWER(TRIM(name)), LOWER(TRIM(email))

TRIM does less than the name suggests. It strips leading and trailing whitespace and leaves the middle alone. We checked on PostgreSQL: lower(btrim(' ada Lovelace ')) returns ada lovelace, double space intact, which does not equal ada lovelace. Collapse the runs explicitly:

GROUP BY LOWER(REGEXP_REPLACE(TRIM(name), '\s+', ' ', 'g')),
         LOWER(TRIM(email))

Lower-casing an email is technically wrong, and you should probably do it anyway. RFC 5321 section 2.4 says "The local-part of a mailbox MUST BE treated as case sensitive," while "Mailbox domains follow normal DNS rules and are hence not case sensitive." Strictly, only the part after the @ folds. The same RFC calls exploiting local-part case sensitivity something that "impedes interoperability and is discouraged," and almost no mail host distinguishes the two. Fold the whole address and write down that you did. Gmail-style aliasing folding does not touch: a.da@gmail.com and ada@gmail.com reach one inbox and compare as different strings.

Normalizing inside GROUP BY costs you the index, because every row gets the function applied. MySQL supports functional key parts with the expression in its own parentheses, PostgreSQL indexes expressions directly, and SQL Server takes a persisted computed column. Index what you group by, or accept the scan knowingly.

Step 4: Window functions, and why they win

GROUP BY collapses rows, which is awkward when you want to keep the originals and merely tag the extras. ROW_NUMBER() does exactly that:

SELECT *
FROM (
  SELECT id, name, email,
         ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
  FROM contacts
) t
WHERE rn > 1;

Within each set of duplicates the rows are numbered 1, 2, 3 in id order. Row 1 is the keeper. Every row with rn > 1 is an extra. Support is universal now: MySQL 8.0 and later, PostgreSQL, SQL Server, Oracle, MariaDB, and SQLite since 3.25.0, released 15 September 2018.

The ordering has to be deterministic or the query is a coin flip. ORDER BY id on a unique id is fine. ORDER BY scraped_at DESC on a timestamp with ties is not: two rows from the same batch swap places between runs, and you delete a different one each time. Put a unique tiebreaker in every partition ordering.

PostgreSQL has a shorter form. DISTINCT ON "keeps only the first row of each set of rows where the given expressions evaluate to equal," and the manual warns that this row "is unpredictable unless ORDER BY is used to ensure that the desired row appears first":

SELECT DISTINCT ON (name, email) id, name, email
FROM contacts
ORDER BY name, email, id;

Analytical engines go one better with QUALIFY, which filters window results the way HAVING filters aggregates. Snowflake and DuckDB both have it, so the subquery collapses to QUALIFY ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) = 1. PostgreSQL 18 and MySQL 8.4 do not. Keep the subquery there.

Step 5: Decide which row survives

Almost every tutorial keeps the lowest id, the earlier version of this one included. For scraped data that is usually the worst choice available: the lowest id is the oldest capture, and the oldest capture has the least in it.

Two rows for the same contact. Row 11 was captured in March with no phone number, row 42 in August with one:

-- keeps id 11: the first row you ever saw
ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id)

-- keeps id 42: the most complete, most recent row
ROW_NUMBER() OVER (PARTITION BY name, email
                   ORDER BY (phone IS NULL), scraped_at DESC, id)

We ran both. The first returns 11, the second returns 42. (phone IS NULL) sorts false before true, so rows with a phone number win; scraped_at DESC breaks the remaining ties toward fresher data; id makes the result deterministic. Stack one expression per column you care about.

Ranking cannot help when the copies are complementary rather than redundant. One row has the phone, another has the postcode, and picking either loses a field. Merge first with an UPDATE that pulls COALESCE values from the group, then delete. Deleting first is not reversible.

Step 6: Delete the extras

Run the SELECT version and read what it returns before you delete anything. Wrap the delete in a transaction so you can roll back. Count the rows before and after, and check the difference against the number of extras you found.

PostgreSQL, deleting via a CTE:

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
  FROM contacts
)
DELETE FROM contacts
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

The PostgreSQL wiki has carried this shape since the 8.4 era. It is still the right default.

SQL Server deletes straight from the CTE, which is legal there and nowhere else. Same ranked definition, then one line: DELETE FROM ranked WHERE rn > 1;

MySQL 8.0 and later. The manual states the restriction flatly: "You cannot delete from a table and select from the same table in a subquery." A multi-table delete against a derived table gets around it, because the derived table is materialized first:

DELETE c
FROM contacts c
JOIN (
  SELECT name, email, MIN(id) AS keep_id
  FROM contacts
  GROUP BY name, email
  HAVING COUNT(*) > 1
) d
  ON c.name = d.name
 AND c.email = d.email
 AND c.id <> d.keep_id;

PostgreSQL with no unique id. The hidden ctid gives you a physical row address:

DELETE FROM contacts a
USING contacts b
WHERE a.name = b.name
  AND a.email = b.email
  AND a.ctid  > b.ctid;

Two warnings the recipe usually travels without. The PostgreSQL manual is blunt: "a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid should not be used as a row identifier." Read it inside one transaction, use it, throw it away. And the self-join degrades quadratically on large duplicate groups, which the next section measures.

Oracle has the same trick under a different name, and the earlier version of this guide left it out:

DELETE FROM contacts
WHERE ROWID NOT IN (
  SELECT MIN(ROWID) FROM contacts GROUP BY name, email
);

Oracle's caution on the ROWID pseudocolumn matches PostgreSQL's on ctid: "You should not use ROWID as the primary key of a table," because a delete-and-reinsert can hand the same rowid to a different row.

What breaks at ten million rows

A one-off cleanup on 50,000 rows tolerates any of the queries above. A nightly job over a scrape table does not. Measured on a two-core Intel Xeon at 2.10 GHz with 8 GB of RAM, PostgreSQL 16.13 at stock settings, work_mem 4 MB and shared_buffers 128 MB. The table holds 2,000,000 rows, 500,000 of them exact duplicates in pairs, physically shuffled. Best of three runs, warm cache. Your numbers will differ; the shape will not.

On pair-shaped duplicates the self-join wins, which is not what its reputation suggests. Skew kills it. A self-join pairs every row in a group with every other row, so a group of k rows costs k(k-1)/2 comparisons. We rebuilt the table with one hot value repeated k times among 200,000 unique ones:

Fifty thousand rows in one group is 1.25 billion pairs. Double the group and the pair count quadruples. The window function does not care; it sorts once and walks the partition. One placeholder repeated across a crawl, an empty name or a default unknown@example.com, builds that group by accident.

Deleting rows does not return disk space. The number from the opening paragraph, measured:

A plain VACUUM marks the dead tuples reusable for future inserts without giving anything back to the filesystem. Only VACUUM FULL rewrites the table, and it holds an ACCESS EXCLUSIVE lock while it does.

Copying is often cheaper than deleting. The in-place delete of 500,000 rows took 7.4 s and left a 187 MB table. Building a clean copy took 2.9 s and produced 109 MB with no bloat to reclaim:

CREATE TABLE contacts_clean AS
SELECT DISTINCT ON (name, email) id, name, email
FROM contacts
ORDER BY name, email, id;

Add the indexes and constraints, swap the names inside a transaction, drop the old table. Past a few million rows this is usually the faster path, and the original stays untouched until you drop it.

Batch the delete if you must delete in place. MySQL documents a LIMIT on single-table DELETE: "If the number of rows to delete is larger than the limit, repeat the DELETE statement until the number of affected rows is less than the LIMIT value." PostgreSQL has no such clause, and we confirmed the syntax error rather than trusting memory. Use ctid instead:

DELETE FROM contacts
WHERE ctid IN (
  SELECT ctid FROM contacts
  WHERE /* your rn > 1 predicate */
  LIMIT 10000
);

Ten thousand rows came back in 13 ms. Loop until it reports zero. One unbatched delete of ten million rows holds locks and a growing dead-tuple footprint for its whole runtime, and blocks autovacuum from cleaning up behind it.

Step 7: Stop duplicates coming back

Cleaning is only worth it if the mess stays gone. Once the table is clean, enforce uniqueness at the schema level:

ALTER TABLE contacts
ADD CONSTRAINT uq_contacts UNIQUE (name, email);

A constraint on the table is not the absence of duplicates. GROUP BY treats two NULLs as one group. A UNIQUE constraint treats them as distinct. PostgreSQL states it plainly: "By default, null values in a unique column are not considered equal, allowing multiple nulls in the column." We ran the whole cycle. Two rows of ('Ada', NULL) show up as a duplicate group; dedup them; add UNIQUE (name, email), which succeeds; insert ('Ada', NULL) twice more, which also succeeds. The group is back at three rows and every constraint on the table is satisfied.

PostgreSQL 15, released 13 October 2022, added the fix: UNIQUE NULLS NOT DISTINCT (name, email) rejects the second insert with duplicate key value violates unique constraint. SQL Server treats nulls as equal in a unique index and permits exactly one, and the documented workaround for allowing several is a filtered index with WHERE (column IS NOT NULL). MySQL and Oracle allow the repeats. The portable answer is to stop storing NULL in a key column.

Constrain the normalized value, not the raw one. A unique index on an expression rejects the near-duplicates too, and ON CONFLICT can target it:

CREATE UNIQUE INDEX contacts_key
  ON contacts (LOWER(BTRIM(name)), LOWER(BTRIM(email)));

INSERT INTO contacts (name, email)
VALUES ('  ADA lovelace ', 'Ada@Example.com')
ON CONFLICT (LOWER(BTRIM(name)), LOWER(BTRIM(email))) DO NOTHING;

Run against a table already holding Ada Lovelace / ada@example.com, that insert leaves the row count at 1. Drop the ON CONFLICT clause and it raises duplicate key value violates unique constraint instead, which is what you want when the write was a bug rather than a re-scrape.

Then make the inserts idempotent:

• PostgreSQL: INSERT ... ON CONFLICT (...) DO NOTHING. The conflict target is optional for DO NOTHING and mandatory for DO UPDATE. MERGE arrived in PostgreSQL 15 for batch-oriented cases.
• SQLite: the same ON CONFLICT syntax, added in 3.24.0 on 4 June 2018, with multiple conflict clauses since 3.35.0.
• MySQL: INSERT ... ON DUPLICATE KEY UPDATE for a real upsert, or INSERT IGNORE if you accept the cost. The manual is clear that IGNORE swallows more than key collisions: "With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort." Silent truncation of a long field looks exactly like a successful insert.
• SQL Server: MERGE, or a WHERE NOT EXISTS guard.

With the right constraint the database rejects duplicates for you. No cleanup script required.

Where the SQL-only approach runs out

Everything above finds exact matches after normalization. It will not find Ada Lovelace Ltd. against Ada Lovelace Limited, and that is where most real scraped-data duplication lives.

PostgreSQL answers with pg_trgm, which scores similarity from 0 to 1 on shared three-character sequences, defaults its % operator to a threshold of 0.3, and backs the comparison with GiST or GIN indexes so it does not collapse into a cross join. SQL Server 2025 added native fuzzy string matching: EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE and JARO_WINKLER_SIMILARITY, the similarity pair scoring 0 to 100. Microsoft still labels it preview and it needs a database-scoped configuration switch, so check before you build on it.

Columnar warehouses change the rules again. ClickHouse's ReplacingMergeTree deduplicates on the ORDER BY key rather than the primary key, and the documentation is explicit that you cannot schedule it: "Data deduplication occurs only during a merge. Merging occurs in the background at an unknown time, so you can't plan for it." Query with FINAL when you need a correct answer now. Dedup logic that assumes DELETE semantics has to be rewritten for engines like that.

The deeper limit is that duplicate rates are a property of the crawler, not the database. When the same listing arrives four times because pagination overlaps and retries are not keyed, the cheapest fix lives upstream in how the scraping pipeline assigns and stores identifiers. A nightly DELETE treats the symptom, and you pay for it nightly.

Data cleansing in SQL beyond duplicates

Deduplication is one pillar of data cleansing in SQL. The same table usually needs a few more passes before it is trustworthy:

• Trim and normalize text with UPDATE contacts SET name = TRIM(name), lower-casing where it is safe. Broader data normalization across units, dates and currencies is a topic in itself.
• Turn blanks into NULLs with NULLIF(email, '') so empty strings stop masquerading as data. Then remember the NULL rule above: you have just created key columns that no unique constraint will police.
• Standardize formats: one casing, one phone and date format, currency stripped of symbols before numeric conversion.
• Validate ranges with a CHECK constraint or an audit query that flags negative prices and impossible dates.

Do the text normalization before the dedup step. Otherwise Ada and Ada survive as separate rows and you run the whole cycle again.

Four things decide whether a dedup job holds up: the identity key, the collation applied to it, which copy you keep, and whether the constraint covers NULLs. Get those right and the queries are five lines each. Get them wrong and you run the same cleanup next month against the same rows. Teams that would rather consume a table arriving deduplicated, normalized and refreshed buy it as data as a service instead of maintaining the pipeline behind it.