How to Find and Retrieve All Duplicate Records in MySQL?

How to Identify Duplicate Records in MySQL

The task of identifying duplicate records in a MySQL database is typically addressed using the following query:

SELECT address, count(id) as cnt 
FROM list
GROUP BY address 
HAVING cnt > 1

However, this query provides only a count of the duplicate records, not the actual data for each duplicate row. To retrieve the individual duplicate records, a slightly different approach is necessary.

The key is to rewrite the original query as a subquery:

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;

This subquery identifies the duplicate addresses, and the outer query uses that information to join the list table and retrieve all the associated data for each duplicate record.

The above is the detailed content of How to Find and Retrieve All Duplicate Records in MySQL?. For more information, please follow other related articles on the PHP Chinese website!