Removing Duplicates

A unique key on a column makes one promise: the table will never hold two rows with the same value in that column. It makes no promise that two different values belong to two different things. To a person, ana.silva@corvid.io and Ana.Silva@Corvid.io are one address, but whether a key treats them as one value depends on the engine's rules for comparing text. A unique key also says nothing about rows whose value is missing.

Removing rows that describe the same thing more than once (deduplication) therefore starts with a decision that the database cannot make on its own: which values count as the same. The sections below make that decision for one contact list, then compare three ways to keep one row per contact.

The Table

A sales team keeps one contact list, filled from three sources: a signup form on the website (web_form), badge scans from a trade show (trade_show), and a list from a reseller (partner). Each file is loaded when it arrives, so import_id follows the load order. The day_no column is the day the source last updated the record, which is not the same as the day it was loaded.

The quotes show the space at the end of row 6. Rows 5 and 8 have no email. Every query below was run on MySQL 8.4, and the output shown is the real output.

What a Unique Key Promises

A unique key, built as a UNIQUE index, is a constraint that rejects any new row whose key value matches a row already in the table. The test below creates a small table with a unique key on email and inserts four values into it:

CREATE TABLE contact_keys (
  email VARCHAR(60),
  UNIQUE KEY uq_email (email)
);
INSERT INTO contact_keys VALUES
  ('ana.silva@corvid.io'), ('ana.silva@corvid.io '), (NULL), (NULL);
SELECT email, LENGTH(email) AS chars
FROM contact_keys
ORDER BY chars;
Copy

Output:

+----------------------+-------+
| email                | chars |
+----------------------+-------+
| NULL                 |  NULL |
| NULL                 |  NULL |
| ana.silva@corvid.io  |    19 |
| ana.silva@corvid.io  |    20 |
+----------------------+-------+
Copy

All four rows were accepted. The two NULLs are allowed because, in the words of the MySQL manual, a UNIQUE index "permits multiple NULL values." The trailing space is accepted because of the column's collation, the set of rules an engine uses to compare and sort text. MySQL 8.4's default collation, utf8mb4_0900_ai_ci, treats a space at the end of a string "like any other character", so the 20-character value differs from the 19-character one.

The same collation ignores letter case, which is what the _ci (case-insensitive) in its name means. A capitalized copy of the first address is therefore rejected:

INSERT INTO contact_keys VALUES ('Ana.Silva@Corvid.io');
Copy

Output:

ERROR 1062 (23000) at line 1: Duplicate entry 'Ana.Silva@Corvid.io' for key 'contact_keys.uq_email'
Copy

Other engines compare text by different rules. The PostgreSQL manual says that the index behind a UNIQUE column "is case-sensitive", and it names lower() as the standard way to compare text without case. Snowflake does not enforce UNIQUE or PRIMARY KEY constraints on standard tables, and BigQuery does not enforce key constraints at all, so in a warehouse the load or the query must remove duplicates.

Exact and Near Duplicates

An exact duplicate repeats another row's identifying value character for character, while a near duplicate differs only in formatting, such as letter case or spaces at either end. Grouping by the raw column shows which values the engine already considers equal:

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

Output:

+------------------------+--------+
| email                  | copies |
+------------------------+--------+
| NULL                   |      2 |
| ana.silva@corvid.io    |      2 |
| raj.mehta@tidewell.com |      2 |
+------------------------+--------+
Copy

Rows 2 and 3 are an exact pair, because the web form was submitted twice. The Ana group counts rows 1 and 4 only because MySQL's default collation ignores case, and a case-sensitive engine would not pair them. Row 6 is missing, because its trailing space makes it a different value. The NULL group shows one more difference: GROUP BY puts all NULLs into one group, while the unique key above treated each NULL as distinct.

To make the rule the same on every engine, build the comparison key explicitly. TRIM removes spaces at both ends, and LOWER converts every letter to lowercase:

SELECT LOWER(TRIM(email)) AS email_key, COUNT(*) AS copies
FROM contact_imports
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY email_key;
Copy

Output:

+------------------------+--------+
| email_key              | copies |
+------------------------+--------+
| ana.silva@corvid.io    |      3 |
| raj.mehta@tidewell.com |      2 |
+------------------------+--------+
Copy

Ana now has all 3 of her rows. The NULL rows are excluded on purpose, because a missing email cannot match anything, and rows 5 and 8 would need a second key such as name plus company.

Keeping the Latest Row per Key

Keeping the latest row means numbering the rows of each key from the most recently updated to the oldest, and keeping number 1. ROW_NUMBER gives the numbers 1, 2, 3 in the window's order, and an outer query keeps number 1. The filter sits in a CTE because a window cannot be filtered in WHERE, as Window Functions showed.

WITH ranked AS (
  SELECT import_id, source, LOWER(TRIM(email)) AS email_key,
         company, day_no,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY day_no DESC, import_id DESC
         ) AS rn
  FROM contact_imports
  WHERE email IS NOT NULL
)
SELECT import_id, source, email_key, company, day_no
FROM ranked
WHERE rn = 1
ORDER BY email_key;
Copy

Output:

+-----------+------------+------------------------+-------------+--------+
| import_id | source     | email_key              | company     | day_no |
+-----------+------------+------------------------+-------------+--------+
|         4 | trade_show | ana.silva@corvid.io    | Corvid Labs |      9 |
|         7 | partner    | li.wong@brightpath.co  | Brightpath  |      7 |
|         3 | web_form   | raj.mehta@tidewell.com | Tidewell    |      4 |
+-----------+------------+------------------------+-------------+--------+
Copy

Ana's partition holds row 4 (day 9), row 6 (day 6), and row 1 (day 3), so row 4 is number 1 and is kept with the company Corvid Labs. Load order would have chosen differently: row 6 arrived last, but its partner record is three days older. Ordering by day_no rather than import_id is what keeps the newer company name.

The second sort column, import_id DESC, decides ties. Rows 2 and 3 share day 4, and the MySQL manual says that ROW_NUMBER "assigns peers different row numbers" without saying which peer comes first. A rerun could then keep a different row. Ranking and Ties covers tiebreaker columns in detail.

GROUP BY Alternatives

Two other queries avoid the window function, at a cost. The first takes MAX of each column separately:

SELECT LOWER(TRIM(email)) AS email_key,
       MAX(company) AS company, MAX(day_no) AS day_no
FROM contact_imports
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
ORDER BY email_key;
Copy

Output:

+------------------------+----------------+--------+
| email_key              | company        | day_no |
+------------------------+----------------+--------+
| ana.silva@corvid.io    | Corvid Systems |      9 |
| li.wong@brightpath.co  | Brightpath     |      7 |
| raj.mehta@tidewell.com | Tidewell       |      4 |
+------------------------+----------------+--------+
Copy

Ana's result pairs day 9 with Corvid Systems, a combination that no source row contains. MAX(company) took the alphabetically largest name from row 6, while MAX(day_no) took day 9 from row 4. This form is correct only when each column is wanted as its own summary, such as the latest day and a count of rows.

The second query finds the newest day per key and joins back to the table, so every output row is a real row:

SELECT c.import_id, LOWER(TRIM(c.email)) AS email_key, c.company, c.day_no
FROM contact_imports c
JOIN (
  SELECT LOWER(TRIM(email)) AS email_key, MAX(day_no) AS max_day
  FROM contact_imports
  WHERE email IS NOT NULL
  GROUP BY LOWER(TRIM(email))
) m ON LOWER(TRIM(c.email)) = m.email_key AND c.day_no = m.max_day
ORDER BY email_key, c.import_id;
Copy

Output:

+-----------+------------------------+-------------+--------+
| import_id | email_key              | company     | day_no |
+-----------+------------------------+-------------+--------+
|         4 | ana.silva@corvid.io    | Corvid Labs |      9 |
|         7 | li.wong@brightpath.co  | Brightpath  |      7 |
|         2 | raj.mehta@tidewell.com | Tidewell    |      4 |
|         3 | raj.mehta@tidewell.com | Tidewell    |      4 |
+-----------+------------------------+-------------+--------+
Copy

Raj appears twice, because rows 2 and 3 both match day 4, and the query reads the table twice.

Deduplicating on Load

The queries above clean the data on every read. The other choice is to stop duplicates on write, with a unique key on the normalized value. Here a contacts table has UNIQUE KEY (email_key), and it starts with Ana's day-3 row from the web form.

An insert that updates the existing row when the key is already present is called an upsert. MySQL writes it as INSERT ... ON DUPLICATE KEY UPDATE, which updates the old row whenever the new one "would cause a duplicate value in a UNIQUE index or PRIMARY KEY". The AS new after VALUES is a row alias that names the incoming row. The older VALUES(column) form is deprecated in its favor.

INSERT INTO contacts (email_key, company, day_no)
VALUES ('ana.silva@corvid.io', 'Corvid Labs', 9) AS new
ON DUPLICATE KEY UPDATE
  company = IF(new.day_no >= contacts.day_no, new.company, contacts.company),
  day_no  = GREATEST(contacts.day_no, new.day_no);
SELECT ROW_COUNT() AS affected_rows;
Copy

Output:

+---------------+
| affected_rows |
+---------------+
|             2 |
+---------------+
Copy

Each assignment compares the days before it writes, so an older row cannot overwrite a newer one. The affected-rows count reports what happened, and 2 means that an existing row was updated. The same statement with the partner row ('Corvid Systems', 6) reports 0, because both assignments return the stored values. A new address, li.wong@brightpath.co on day 7, reports 1, which means a row was inserted:

SELECT email_key, company, day_no
FROM contacts
ORDER BY email_key;
Copy

Output:

+-----------------------+-------------+--------+
| email_key             | company     | day_no |
+-----------------------+-------------+--------+
| ana.silva@corvid.io   | Corvid Labs |      9 |
| li.wong@brightpath.co | Brightpath  |      7 |
+-----------------------+-------------+--------+
Copy

The two places to deduplicate have these costs.

• Benefit of load-time deduplication: reads stay simple. Duplicates never reach contacts, so a read needs no window and no sort.
• Cost: the rule is fixed when the row is written. If the team later decides that trade-show data should take priority over newer partner data, the merged rows cannot be ranked again, because the replaced versions are gone. Keeping the raw contact_imports table and building contacts from it avoids that loss.
• Cost of the shortcut, INSERT IGNORE: a later row is dropped without an error, even when it is newer. With IGNORE, a duplicate row "is discarded and no error occurs", so the first version loaded is always the one kept, and other ignored errors become warnings.

Query-time deduplication is the better choice when the raw rows must stay available or the rule for which row to keep may change. Load-time deduplication is better when the table is read far more often than it is written.

Practice: Build One Row per Contact

Using contact_imports, return one row per contact, where two rows are the same contact when LOWER(TRIM(email)) matches. Return email_key (the lowercased, trimmed email), then full_name, company, and day_no taken from the row with the highest day_no; when two rows share that day, use the row with the higher import_id. Add import_count, the number of import rows with that key. Skip rows whose email is NULL or empty after trimming. Sort the result by email_key.

Using This in an Interview

Before writing any SQL, state out loud which rows match and which row you keep. For example: "Rows match on the trimmed, lowercased email, I keep the highest day_no, and the higher import_id breaks a tie." That sentence fixes the partition, the order, and the tiebreaker of the window. Two follow-up questions and short answers:

• "Why not use SELECT DISTINCT?" DISTINCT removes only rows that are equal in every selected column. Ana's three rows differ in email spelling, company, and day, so DISTINCT keeps all three.
• "What happens to rows with no email?" They cannot match on email, so report them separately or match them on a second key such as name plus company. Say that the second key can merge two different people who share a name.

Key Takeaways

• A unique key rejects equal values, but the collation decides what "equal" means, and a UNIQUE index accepts any number of NULLs.
• MySQL's default collation ignores case and counts trailing spaces, while PostgreSQL's unique index is case-sensitive, so build an explicit key such as LOWER(TRIM(email)).
• To keep the newest row per key, number each partition with ROW_NUMBER ordered by the update day plus a unique tiebreaker, and keep row 1 in an outer query.
• MAX per column can combine values from different rows, and a join back on MAX returns every tied row.
• INSERT ... ON DUPLICATE KEY UPDATE with a row alias deduplicates on load, and comparing days in each assignment stops an older row from overwriting a newer one.

A deduplicated contact list counts each person once, which matters whenever a query divides one count of people by another. The next lesson, Retention and Cohort Tables, groups subscribers by the week they signed up and measures what share of each group is still active in later weeks.

Practice Questions

1. A table has UNIQUE KEY (email) with MySQL 8.4's default collation. Four inserts arrive: NULL, NULL, 'li@brightpath.co', and 'li@brightpath.co ' with a trailing space. How many are accepted?

<details> <summary>Show answer</summary>

All four are accepted. A UNIQUE index permits multiple NULL values, so both NULLs go in. The default collation, utf8mb4_0900_ai_ci, treats a trailing space like any other character, so the two addresses are different values. A query that needs them to match must compare TRIM(email).

</details>

2. GROUP BY email puts ana.silva@corvid.io and Ana.Silva@Corvid.io in one group on MySQL. Why is that not enough for a deduplication job?

<details> <summary>Show answer</summary>

The match comes from the collation, not from the query. MySQL's default collation is case-insensitive, so the two values compare as equal. PostgreSQL compares them case-sensitively and would keep two groups. Writing LOWER(TRIM(email)) states the rule in the query, so it gives the same groups on any engine and also handles spaces.

</details>

3. The keep-latest query is written with ORDER BY import_id DESC instead of ORDER BY day_no DESC, import_id DESC. Which of Ana's rows is kept, and why is it wrong?

<details> <summary>Show answer</summary>

Row 6, with Corvid Systems and day 6, is kept. Row 6 has the highest import_id because the partner file was loaded last. Its record, however, was last updated on day 6, three days before the trade-show row 4 on day 9. Load order is not update order, so the query keeps an older company name.

</details>

4. The upsert is written as company = new.company, day_no = new.day_no. The trade-show row (day 9) loads first, then the partner row (day 6). What does contacts hold for Ana afterward?

<details> <summary>Show answer</summary>

Corvid Systems and day 6, which is the older record. Without a comparison, every duplicate key overwrites the stored row, so the last row loaded is the one kept. The version in the lesson compares new.day_no with contacts.day_no in each assignment and keeps Corvid Labs and day 9.

</details>

5. Raj Mehta's rows 2 and 3 are identical except for import_id. Which of the three query-time methods returns him twice, and how is it fixed?

<details> <summary>Show answer</summary>

The join back on MAX(day_no) returns him twice. Both rows have day 4, which equals the maximum, so both match the join. ROW_NUMBER with ORDER BY day_no DESC, import_id DESC gives row 3 number 1 and row 2 number 2, so filtering on number 1 keeps exactly one row.

</details>