How to remove duplicate rows in MySQL?

Problem

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

Input

first_name	last_name	email
John	Doe	[email protected]
Jane	Smith	[email protected]
John	Doe	[email protected]
Bob	Johnson	[email protected]
Alice	Williams	[email protected]
Jane	Smith	[email protected]

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

first_name	last_name	email
John	Doe	[email protected]
Jane	Smith	[email protected]
Bob	Johnson	[email protected]
Alice	Williams	[email protected]

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:

id	first_name	last_name	email
3	John	Doe	[email protected]
4	Bob	Johnson	[email protected]
5	Alice	Williams	[email protected]
6	Jane	Smith	[email protected]

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?