This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
