Get all users with duplicate email addresses

Question

I am writing a MySQL query where I can get all the records from a user table with duplicate email addresses.

This is the request that I still have .. my PHPMyAdmin continues to load:

select personal_email, userid from user where personal_email in ( select personal_email from user group by personal_email having count(personal_email) > 1); 

If I use the following query, I receive all double letters only once:

 select personal_email, count(personal_email) from user group by personal_email having count(personal_email) > 1 

The goal is to get all records with duplicate emails.

Answer 1

Try executing the query using JOIN instead of IN :

 SELECT user.personal_email, user.userid FROM user INNER JOIN ( SELECT personal_email FROM User GROUP BY personal_email HAVING COUNT(*) > 1 ) dupe ON dupe.personal_email = user.personal_email; 

MySQL often optimizes INNER JOINs much better.

Answer 2

Perhaps the most efficient way to do this in MySQL is to use the exists clause:

 select u.* from user u where exists (select 1 from user u2 where u.personal_email = u2.personal_email and u.userid <> u2.userid ); 

For optimal performance, create an index on user(personal_email, userid) .

Answer 3

You can simply use this result as selection criteria.

 select * from user where personal_email IN ( select personal_email from user group by personal_email having count(personal_email) > 1 ); 

A sqlfiddle is for you .

Answer 4

You can simply attach the table to yourself by email:

 select distinct u1.personal_email, u1.userid from user u1 join user u2 on u2.personal_email = u2.personal_email and u2.userid != u1,userid 

The key to this is adding u2.userid != u1,userid to the join condition to prevent line joins.