How To Find Duplicate Values in MySQL

Data duplication happens because of many reasons. Finding duplicate values is one of the important tasks that you must deal with when working with the databases.

Setting up a sample table

First, create a table named contacts with four columns: id, first_name, last_name, and email.

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Second, inserts rows into the contacts table:

INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

Third, query data from the contacts table:

SELECT * FROM contacts
ORDER BY email;

In the contacts table, we have some rows that have duplicate values in the first_name, last_name, and email columns. Let’s learn how to find them.

Find duplicate values in one column

The find duplicate values in on one column of a table, you use follow these steps:

  1. First, use the GROUP BY clause to group all rows by the target column, which is the column that you want to check duplicate.
  2. Then, use the COUNT() function in the HAVING clause to check if any group have more than 1 element. These groups are duplicate.

The following query illustrates the idea:

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

By using this query template, you can to find rows that have duplicate emails in the contacts table as follows:

SELECT 
    email, 
    COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;

This picture shows the output of the query that shows the duplicate emails:

Find duplicate values in multiple columns

Sometimes, you want to find duplicate rows based on multiple columns instead of one. In this case, you can use the following query:

SELECT 
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...

FROM
    table_name
GROUP BY 
    col1, 
    col2, ...
HAVING 
       (COUNT(col1) > 1) AND 
       (COUNT(col2) > 1) AND 
       ...

Rows are considered duplicate only when the combination of columns are duplicate therefore we used the AND operator in the HAVING clause.

For example, to find rows in the contacts table with duplicate values in first_name, last_name, and email column, you use the following query:

SELECT 
    first_name, COUNT(first_name),
    last_name,  COUNT(last_name),
    email,      COUNT(email)
FROM
    contacts
GROUP BY 
    first_name , 
    last_name , 
    email
HAVING  COUNT(first_name) > 1
    AND COUNT(last_name) > 1
    AND COUNT(email) > 1;

The following illustrates the output of the query:

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Managing Hierarchical Data in MySQL Using the Adjacency List Model

Hierarchical data is everywhere. It can be blog categories, product hierarchies, or organizational structures. There are many ways to manage hierarchical data in MySQL and the adjacency list model...

MySQL Row Count: How to Get Row Count in MySQL

Getting MySQL row count of a single table To get the row count of a single table, you use the COUNT(*) in a SELECT statement as follows: SELECT COUNT(*) FROM table_name; For example,...

MySQL Insert

Introduction to the MySQL INSERT statement The INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement: INSERT INTO...

MySQL DELETE

Introduction to MySQL DELETE statement To delete data from a table, you use the MySQL DELETE statement. The following illustrates the syntax of the DELETE statement: DELETE FROM table_name WHERE...

MySQL Compare Two Tables

In data migration, we often have to compare two tables to identify a record in one table that have no corresponding record in another table. For example, we have a new database whose schema is...