Delete Duplicate Emails | Leetcode 196 | Crack SQL Interviews in 50 Qs

Welcome to Question 46 of the SQL Interview Series. This chapter covers a classic database cleanup problem: deleting duplicate rows from a table while keeping the single best representative row based on a specific business rule (the smallest ID).

1. Why Do We Need This? (Problem Statement & Context)

In real-world database administration and data engineering pipelines, duplicate records frequently creep in due to concurrent user registrations, missing unique constraints, or faulty data migrations.

• The Status Quo: Leaving duplicates in production tables wastes storage, skews analytical aggregations, and can cause logic bugs in downstream applications.
• The Core Constraint: Unlike standard querying problems where we use SELECT to fetch data, this problem requires a DELETE statement. We must purge duplicate records directly from the database table.

2. Table Schema & Input Structure

We are given a single database table named Person containing user records.

Schema Definition

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

Table Characteristics

• id: Serves as the primary key (unique identifier for each row, integer data type).
• email: Stores user email addresses (string data type). Every row contains an email, and all letters are strictly lower-case.

3. Core Mental Model & Real-Life Analogy

Real-Life Analogy: Cleaning Up Duplicate Contacts in a Phone Book

Imagine you have a physical contact notebook where a friend's email address (john@example.com) was accidentally written down on three different pages over time (e.g., on Page 1, Page 3, and Page 7).

• The Goal: Keep only one entry for John so your contact list is clean and unambiguous.
• The Rule: Always keep the entry that appeared first (the one with the smallest page number or ID) and erase all subsequent duplicate entries.

To achieve this in SQL without a secondary staging table, we use a Self-Join strategy, comparing every row in the table against every other row in the exact same table.

4. Understanding the Example Data

Let's examine how sample data looks before and after applying our deletion rule.

Input Table (Person)

Expected Output Table (After Deletion)

Walkthrough of the Output:

• john@example.com appears twice (at id = 1 and id = 3). Since 1 < 3, we retain the record with id = 1 and delete the record with id = 3.
• bob@example.com appears only once (id = 2), so it remains untouched.

5. Naive vs. Better Approach

The Naive Subquery Approach (Performance Trap)

A common beginner instinct is to use a subquery with NOT IN or MIN(id):

-- Naive / Subquery approach (can trigger dialect-specific errors in MySQL)
DELETE FROM Person
WHERE id NOT IN (
    SELECT MIN(id) 
    FROM Person 
    GROUP BY email
);

Note: In MySQL, running a DELETE query that references the same table in a nested SELECT subquery throws an error: You can't specify target table 'Person' for update in FROM clause. To bypass this restriction, engineers use a self-join.

The Professional Approach: Self-Join with DELETE

Instead of nesting queries, we alias the Person table twice (p1 and p2) and join them together to compare rows side by side.

6. Step-by-Step Evolution of the SQL Query

Let's build the self-join query step by step.

Step 1: Performing the Self-Join

We join the Person table with itself so that every email record is paired against every other email record.

SELECT *
FROM Person p1
INNER JOIN Person p2
  ON p1.email = p2.email;

Step 2: Adding the Greater ID Condition

We want to target rows where the email addresses match, but one record has a strictly greater ID than another.

SELECT *
FROM Person p1
INNER JOIN Person p2
  ON p1.email = p2.email
WHERE p1.id > p2.id;

If john@example.com exists at id = 1 (p2) and id = 3 (p1), this condition evaluates to true because 3 > 1. Therefore, p1 represents the duplicate row we want to delete.

Step 3: Converting to a DELETE Statement

We swap out SELECT * and specify DELETE p1 to remove the higher-ID rows.

DELETE p1
FROM Person p1
INNER JOIN Person p2
  ON p1.email = p2.email
WHERE p1.id > p2.id;

7. Complete Flow Walkthrough

Here is how the execution flows through the database engine:

8. Alternative Perspective: Swapping Aliases

Can we invert the comparison logic? Yes! The creator notes that you can achieve the exact same result by swapping the alias targets if you change the comparison operator:

DELETE p2
FROM Person p1
INNER JOIN Person p2
  ON p1.email = p2.email
WHERE p1.id < p2.id;

• Explanation: Here, we specify p1.id < p2.id, meaning p2 now represents the larger ID. By instructing the engine to DELETE p2, we achieve the exact same outcome—preserving the smaller ID (p1) and purging the larger duplicate ID (p2).

9. Summary and Key Takeaways

• No SELECT Needed: This problem demonstrates a direct data-modification query (DELETE) without returning a result set to the user client.
• Avoiding MySQL Subquery Limitations: MySQL prevents updating a table while simultaneously selecting from it in a subquery; self-joins provide an elegant workaround.
• Table Aliasing (p1 and p2): Self-joining allows you to compare rows within the same table as if they belonged to two separate data sources.
• Duplicate Identification Logic: Matching on identical attributes (p1.email = p2.email) paired with an inequality (p1.id > p2.id) cleanly isolates redundant higher-ID rows.
• Precision Deletion: Specifying DELETE p1 ensures only the designated duplicate rows are removed while foundational records (MIN(id)) remain intact.