Find duplicate records in MySQL

In this post, I am sharing different scripts to find the duplicate records in MySQL.
I found few alternative solutions to find duplicate records in MySQL.
As per your data size, you can choose any one of this solution.

Let’s first create sample duplicate data:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE Employee.tbl_FindDuplicates (     EmpID INTEGER PRIMARY KEY AUTO_INCREMENT     ,FirstName VARCHAR(50)     ,LastName VARCHAR(50) );   INSERT INTO Employee.tbl_FindDuplicates (FirstName,LastName) VALUES ('Anvesh','Patel'),('Neevan','Patel') ,('Anvesh','Patel'),('Nirav','Shah') ,('Dharma','Teja'),('Nirav','Shah');

First solution using Self Join:

1 2 3 4 5 6 7 8

SELECT     FD1.EmpID     ,FD1.FirstName     ,FD1.LastName FROM Employee.tbl_FindDuplicates AS FD1 INNER JOIN Employee.tbl_FindDuplicates AS FD2    ON FD1.FirstName = FD2.FirstName WHERE FD1.EmpID <> FD2.EmpID

Second solution using SUB Queries:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     FD.EmpID     ,FD.FirstName     ,FD.LastName FROM Employee.tbl_FindDuplicates AS FD INNER JOIN (     SELECT FirstName             FROM Employee.tbl_FindDuplicates     GROUP BY FirstName HAVING COUNT(1)>1 ) AS IFD ON IFD.FirstName = FD.FirstName

Third solution using Table Aliases:

1 2 3 4 5 6 7 8

SELECT     FD1.EmpID     ,FD1.FirstName     ,FD1.LastName FROM Employee.tbl_FindDuplicates AS FD1 , Employee.tbl_FindDuplicates AS FD2 WHERE FD1.FirstName = FD2.FirstName    AND FD1.EmpID <> FD2.EmpID

Fourth solution using Group BY:

1 2 3 4 5 6 7

SELECT     EmpID     ,FirstName     ,LastName FROM Employee.tbl_FindDuplicates GROUP BY FirstName,LastName HAVING COUNT(1) >=2;

The above all are different solutions to find duplicate records in MySQL.
As you can choose any one of this but my suggestion is to use the first solution which will perform faster.