How to Find the Duplicates in MySQL?

The outcomes from this article are:

• How to Find Duplicate Values From a Single Column Using “GROUP BY” Clause in MySQL?
• How to Find Duplicate Values From Multiple Columns Using “GROUP BY” Clause in MySQL?

How to Find Duplicate Values From a Single Column Using “GROUP BY” Clause in MySQL?

To find the duplicate data in MySQL, first, we will create a table and then use the “GROUP BY” clause to retrieve duplicate values. To do so, follow the below-stated steps:

• Open the Command Prompt and access the MySQL server.
• List and change databases.
• Create a new table and insert data.
• Execute the “SELECT <col> COUNT(col) FROM <table-name> GROUP BY <col> HAVING COUNT(col) > 1;” command.

Step 1: Launch Command Prompt

At first, launch the Command Prompt by utilizing the Startup menu:

Step 2: Access MySQL Server

To access the MySQL server, execute the provided command:

Step 3: Display Databases

Now, execute the “SHOW” statement to view all databases:

From the displayed databases, we have selected the “mynewdb” database:

Step 4: Change Database

Then, change the database by running the “USE” statement:

Step 5: Create New Table

Next, create a new table in the current database by executing the below-stated command along with the table name, column names, and their data types:

Here:

• “CREATE” statement is used for generating a new table.
• “TABLE” is the default option in MySQL.
• “employee_table” is the table name that we are creating.

According to the following output, the “Query OK” shows that the query has been executed successfully:

Step 6: List Tables

After that, list the current database tables by running the provided command:

According to the below-stated output, a new “employee_table” table has been created successfully:

Step 7: Insert Data

Execute the “INSERT INTO” statement with the “VALUES” clause to add the column’s values:

Here:

• “INSERT INTO” statement is used to add new records in the table.
• “First_Name”, “Last_Name” and “Email” are the table column names.
• “VALUES” statement that returns a set of one or multiple rows as a table.

According to the given output, data has been added to the table:

Step 8: View Table Data

To view inserted data in the table, use the “SELECT” statement with the “FROM” clause and table name:

Step 9: Sort Table Data

Now, sort the table record in ascending order by utilizing the “ORDER BY” keyword with the “SELECT” statement:

As you can see, the record has been sorted in ascending order:

Step 10: Find Duplicate Value

To retrieve the duplicate values from the table, run the provided command:

Here:

• “SELECT” statement is utilized for selecting the data from MySQL.
• “EMAIL” is the table column name.
• “COUNT()” method is used to get the number of rows returned by a “SELECT” statement.
• “FROM” clause is used to select some records from a table.
• “employee_table” is the table name.
• “GROUP BY” statement with the “HAVING” clause is used to retrieve a particular number of columns that fulfill the condition specified in the “HAVING” clause, such as “> 1”.

It can be seen that the specified column contains three duplicate values:

How to Find Duplicate Values From Multiple Columns Using “GROUP BY” Clause in MySQL?

Moreover, we can also find duplicate values from multiple columns in MySQL by utilizing the “GROUP BY” clause. To do so, use the below-stated command:

In the above-given command, we have specified the:

• “First_Name”, “Last_Name” and “Email” column names.
• “AND” operator is used in the “GROUP BY” query to retrieve a specific number of columns that meet the condition provided in the “HAVING” clause, such as “> 1”.

As a result, the duplicate values from multiple columns have been displayed:

We have compiled the procedure to retrieve the duplicates in MySQL.

Conclusion

To find the duplicate values from the single/multiple columns in MySQL, the “GROUP BY” statement with the “HAVING” clause is used to retrieve a particular number of columns that fulfill the condition specified in the “HAVING” clause. The “SELECT <col> COUNT(col) FROM <table-name> GROUP BY <col> HAVING COUNT(col) > 1;” command can be used. This article demonstrated the way to find duplicates in MySQL.