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:
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:
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:
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: