Notes from a LeetCode problem showing different ways to solve it.
- Duplicate Emails
Table
mysql> select * from Person;
+------+---------+
| id | email |
+------+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+------+---------+
Using HAVING clause
select Email
from Person
group by Email
having count(Email) > 1;
Subquery
select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1
;
Self join
select distinct a.email from Person as a
join Person as b
on a.email = b.email
where a.Id <> b.Id