Javatpoint Logo
Javatpoint Logo

MySQL Find Duplicate Records

MySQL is a database application that stores data in tables in the form of rows and columns. This database application can store duplicate records in the table, which can impact the performance of the database in MySQL. However, data duplication occurs due to various reasons and finding the duplicate values in the table is an important task while working with a database in MySQL.

Generally, it is a good idea to always use unique constraints on a table to store data that prevent having duplicate rows. However, sometimes working with a database, we can find many duplicate rows due to human error, uncleaned data from external sources, or a bug in the application. In this article, we are going to learn how we can find duplicate values in a MySQL database.

Let us understand it with the help of an example. First, we will create a table named "student_contacts" using the following statement:

Next, we will fill the record into the table using the INSERT statement as follows:

Execute the SELECT statement to verify the record:

MySQL Find Duplicate Records

In this table, we can see that there are various rows available with duplicate values. Let's learn how we can find them using the SQL query.

Find Duplicate Data in a Single Column

We can find the duplicate entries in a table using the below steps:

  1. First, we will use the GROUP BY clause for grouping all rows based on the desired column. The desired column is the column based on which we will check duplicate records.
  2. Second, we will use the COUNT() function in the HAVING clause that checks the group, which has more than one element.

The following syntax explains the above steps:

With the help of the above syntax, we can use the below statement to find rows that have duplicate names in the student_contacts table:

After executing the above statements, we will get the below output that shows the duplicates names and emails:

MySQL Find Duplicate Records

Find Duplicate Data in Multiple Columns

Sometimes we need to find duplicate values based on multiple columns. In that case, we can use the syntax as follows:

We should note that while finding duplicates in multiple columns, the rows are duplicated only when the combination of columns is duplicated. Therefore, we need to use the AND operator in the HAVING clause.

For example, if we want to find rows in the student_contacts table that contains duplicate values in name, state, and email columns, the following query can be used:

After executing the above statements, we will get the below output that shows the duplicates name, state, and emails columns:

MySQL Find Duplicate Records




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA