How To Find Duplicate Values in MySQL

How to Find Duplicate Values in MySQL

Duplicate values in a MySQL table can cause data integrity issues. This guide shows different ways to identify and handle duplicate records effectively.

1. Finding Duplicate Values in a Column

To detect duplicate values in a specific column, use GROUP BY and HAVING COUNT(*) > 1:

SELECT column_name, COUNT(*) AS count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

✅ This query lists duplicate values in column_name and their counts.

Example

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

🔹 Output:

email	count
test@example.com	2
demo@mail.com	3

📌 The a email column has duplicate values.

2. Finding Duplicate Rows Based on Multiple Columns

To find duplicates across multiple columns, compare more than one field:

SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

Example

SELECT first_name, last_name, COUNT(*) AS count
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

✅ Shows duplicate first_name and last_name combinations.

3. Finding All Duplicate Rows

To retrieve all duplicate records instead of just counts, use a JOIN or subquery:

SELECT * FROM users u
JOIN (
    SELECT email FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
) dup ON u.email = dup.email;

✅ Retrieves all duplicate entries from the users table.

4. Finding Duplicate Rows with Row Numbers

Use the ROW_NUMBER() window function (MySQL 8+):

SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
FROM users;

✅ This assigns a row number to each duplicate entry.

5. Deleting Duplicate Records

To delete duplicate rows while keeping the earliest entry:

DELETE u FROM users u
JOIN (
    SELECT MIN(id) AS min_id, email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
) keep ON u.email = keep.email
WHERE u.id > keep.min_id;

✅ Deletes all duplicates except the first occurrence.

6. Preventing Duplicates Using UNIQUE Constraint

To avoid duplicates in the future, enforce a UNIQUE constraint:

ALTER TABLE users ADD UNIQUE (email);

✅ Ensures no duplicate emails can be inserted.

7. Conclusion

• Use GROUP BY + HAVING COUNT() > 1 to find duplicates.
• Use JOINs or ROW_NUMBER() to retrieve full duplicate rows.
• Use DELETE + MIN(id) to remove duplicates while keeping one.
• Prevent duplicates using a UNIQUE constraint.

🚀 Maintain data integrity by finding and removing duplicate records!