How to find duplicate values in MySQL?

In any relational database, data integrity matters. Duplicate values can skew analytics, inflate data storage costs, and create confusion in reporting. Whether you need to identify accidental duplicates in user emails or repeated transaction entries, MySQL’s rich SQL features make it relatively straightforward to pinpoint these problematic rows. Below, we’ll explore several methods to find duplicates and offer best practices to help keep your data pristine.

1. Using `GROUP BY` and `HAVING` to Identify Duplicates

A classic approach to spotting duplicates is leveraging an aggregate function (like COUNT(*)) with the GROUP BY clause, then filtering groups that occur more than once via the HAVING clause.

SELECT 
  column_name, 
  COUNT(*) AS occurrence
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

GROUP BY column_name: Groups rows with the same value in column_name.
HAVING COUNT(*) > 1: Filters out groups that only have a single occurrence.

Example

If you suspect the email column has duplicates:

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

Any email address with an occurrence count above 1 signals a duplicate entry.

2. Finding Multi-Column Duplicates

Often, you need to check for duplicates across more than one column—like (first_name, last_name) or (order_id, product_id). You can group by multiple columns similarly:

SELECT 
  columnA, 
  columnB, 
  COUNT(*) AS occurrence
FROM your_table
GROUP BY columnA, columnB
HAVING COUNT(*) > 1;

This ensures that both columnA and columnB together are treated as a single unique combination.

3. Locating Duplicate Rows and Their IDs

Sometimes you want specific row IDs of all duplicate records. One approach is using a subquery that isolates duplicates, then joining it back to your table:

SELECT t.*
FROM your_table t
JOIN (
  SELECT 
    column_name,
    COUNT(*) as occurrence
  FROM your_table
  GROUP BY column_name
  HAVING COUNT(*) > 1
) dup ON t.column_name = dup.column_name;

Now you’ll have the entire row for each duplicated value, helping you decide which duplicates to remove or modify.

4. Strategies for Dealing with Duplicates

Use a Temporary or Helper Table: Store duplicate rows in a separate table for manual inspection before deciding to delete or update them.
Add a Unique Constraint: Prevent future duplicates by applying a UNIQUE constraint on the relevant column(s). Example:
```
ALTER TABLE your_table
ADD CONSTRAINT unique_column_name
UNIQUE (column_name);
```
Clean as You Go: Set up triggers or application-level checks to avoid inserting duplicates in the first place.

5. Best Practices

Backup Before Removing: Always make a backup before deleting or modifying large numbers of duplicate rows.
Minimize Downtime: If you’re removing duplicates in a production environment, consider doing it during off-peak hours.
Monitor Growth: Regularly check for duplicates if you’re working with user-generated content or large ETL pipelines.

6. Elevate Your SQL Game

Finding and cleaning duplicates is just one element of effective SQL and database management. If you’re ready to take on more advanced queries or optimize your database performance, check out these courses by DesignGurus.io:

Grokking SQL for Tech Interviews
Learn real-world SQL patterns, advanced query-building, and interview-focused problem-solving.
Grokking Database Fundamentals for Tech Interviews
Dive deeper into indexing, normalization, and transaction management—essential concepts for scalable database systems.

7. Practice with Mock Interviews

If you’re preparing for a role where SQL is pivotal, hone your interview skills with Mock Interviews from DesignGurus.io. You’ll get personalized feedback from ex-FAANG engineers, boosting your confidence and sharpening your ability to tackle queries, including those involving duplicate data scenarios.

Conclusion

Uncovering duplicate values in MySQL typically involves combining a GROUP BY clause with HAVING to zero in on repeated entries. Whether you’re dealing with single-column repeats or multi-column duplicates, MySQL provides the flexibility to locate, inspect, and remove them efficiently. Combine these techniques with robust schema design (via unique constraints) and ongoing maintenance practices to keep your database both high-performing and accurate.

Keep exploring SQL’s powerful features and best practices to master data integrity. As you do, consider leveraging the resources and mock interview services at DesignGurus.io to take your skills to the next level!

CONTRIBUTOR

TechGrind