LeetCode 182. Duplicate Emails SQL Solution
Problem
LeetCode SQL Problem
- Duplicate Emails
Person table
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | joe@test.com |
| 3 | a@b.com |
| 4 | joe@test.com |
| 5 | a@b.com |
| 6 | bob@test.com |
Solution - Group By + Having
- MySQL
- TSQL
Find Duplicate Emails
-- Group by 'Email' and return 'Email' shared by more than 1 person Id
SELECT P.Email
FROM Person AS P
GROUP BY P.Email
HAVING count(P.Id) > 1
Find Duplicate Emails
-- Group by 'Email' and return 'Email' shared by more than 1 person Id
SELECT P.Email
FROM Person AS P
GROUP BY P.Email
HAVING count(P.Id) > 1
Query Output
| a@b.com |
| joe@test.com |
Solution - Self Join Person Table
- MySQL
- TSQL
Find Duplicate Emails
-- Self Join 'Person' table to return combinations that have different person Id, but the same Email
SELECT DISTINCT P1.Email
FROM Person AS P1
INNER JOIN Person AS P2 ON P1.Id <> P2.Id
AND P1.Email = P2.Email
Find Duplicate Emails
-- Self Join 'Person' table to return combinations that have different person Id, but the same Email
SELECT DISTINCT P1.Email
FROM Person AS P1
INNER JOIN Person AS P2 ON P1.Id <> P2.Id
AND P1.Email = P2.Email
Query Output
| a@b.com |
| joe@test.com |