SQL implementation of LeetCode (182. Duplicate mailboxes)

[LeetCode] 182.Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email |
+---------+
| [email protected] |
+---------+

Note : All emails are in lowercase.

This question asks us to find duplicate email addresses, so the most direct way is to use the fixed combination of Group by...Having Count(*)..., the code is as follows:

Solution 1:

SELECT Email FROM Person GROUP BY Email
HAVING COUNT(*) > 1;

We can also use internal intersection to do this, using Email to internally intersect the two tables, and then return rows with different IDs, which means that two different people use the same email address:

Solution 2:

SELECT DISTINCT p1.Email FROM Person p1
JOIN Person p2 ON p1.Email = p2.Email
WHERE p1.Id <> p2.Id;

References:

https://leetcode.com/discuss/53206/a-solution-using-a-group-by-and-another-one-using-a-self-join

This is the end of this article about SQL implementation of LeetCode (182. Duplicate mailboxes). For more relevant SQL implementation of duplicate mailboxes, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!