Find rows that have the same value on a column in MySQL

sql

join

dataframe

performance

byNikita Barsukov·Jan 20, 2025

To quickly spot duplicate values in a MySQL column, harness the power of the GROUP BY and HAVING clauses. This combination ensures you only encounter records with recurring values.

-- I'm like your SQL detective, finding those duplicates for you!
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

This displays every instance of column_name occurring multiple times in table_name, alongside its count.

Full Row Data for Duplicates

Maybe you need all data, not just duplicate values. Join your table to its subquery:

-- Full row of suspects, not just the usual culprits!
SELECT a.*
FROM table_name a
JOIN (
  SELECT column_name
  FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 1
) b ON a.column_name = b.column_name;

Here, you receive all columns for rows sharing a common column_name.

Discarding Null Values and Adding Unique Constraints

To crop out empty values in your search:

-- “Ghost values” can’t hide here!
SELECT column_name, COUNT(*)
FROM table_name
WHERE column_name IS NOT NULL
GROUP BY column_name
HAVING COUNT(*) > 1;

Prevent future duplicates by establishing unique constraint:

-- Put the "unique" in "unique column"!
ALTER TABLE table_name
ADD UNIQUE (column_name);

Query Optimization for Big Datasets

Big datasets can benefit from indexed columns:

-- Speed up the detective work!
CREATE INDEX idx_column_name ON table_name (column_name);

Further refine the process with temporary tables or narrowing the search scope.

Find those sneaky duplicate emails

Multiple users sharing the same email address can wreak havoc in your data. Here's a way to identify them:

-- "Spammy" emails? We find them here!
SELECT email, COUNT(email) as frequency
FROM users
GROUP BY email
HAVING COUNT(email) > 1
ORDER BY frequency DESC;

You should run regular checks for duplicates to maintain data integrity.

Using Nested Queries to Extract Associated Data

Associated user IDs and other data linked with duplicates can be unearthed with nested queries:

-- More info means better crime-solving!
SELECT a.user_id, a.email
FROM users a
WHERE EXISTS (
  SELECT 1
  FROM users b
  WHERE a.email = b.email
  AND a.user_id != b.user_id
);

MySQL Specificities and Database Considerations

Some methods are MySQL-specific. Always account for MySQL compatibility:

-- Just your friendly MySQL helper!
SELECT column_name, COUNT(column_name) as count
FROM table_name
WHERE column_name IS NOT NULL
GROUP BY column_name
HAVING COUNT(column_name) > 1;