MySQL Find Duplicate Records

Duplicate rows are a common problem, for example the same email saved two times. This query finds them. We use a users table where the email should be unique, but it is not.

Find the duplicate emails

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Show the full duplicate rows

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email;

This shows rows 1, 3, 2 and 5, so you can see which records are repeated.

Related: GROUP BY and HAVING and Subqueries.

• Previous Code
• Next Code

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.