How to remove duplicate rows in MySQL?

Problem

Given a table in MySQL, how do you remove duplicate rows?

Input

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

Solution 1:

Using SELECT DISTINCT

    CREATE TABLE employees_no_duplicates AS
    SELECT DISTINCT *
    FROM employees;

Explanation:

To remove duplicate rows from the employees table, you can use the DISTINCT keyword in a SELECT statement to identify unique rows and then insert the results into a new table.

Alternately you can use the DELETE statement to remove duplicates from the original table. Let’s see how to do that.

Solution 2:

Using a SELF JOIN

This approach works if there is an id field which does not have the values duplicated.

    DELETE e1
    FROM employees e1
    JOIN employees e2
    ON  e1.id < e2.id
    AND e1.first_name = e2.first_name
    AND e1.last_name = e2.last_name
    AND e1.email = e2.email;

Output:

Try Hands-On: Fiddle

Explanation:

This approach uses a DELETE statement with a self-join to remove duplicate rows from the original table.

It directly modifies the existing table, so be cautious when using this approach, as it permanently removes data.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to split values to multiple rows in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?