Problem Description
Table: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table contains an email. The emails will not contain uppercase letters.
Write an SQL query to report all the duplicate emails.
Return the result table in any order.
Examples
Example 1:
Input: Person table: +----+---------+ | id | email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ Output: +---------+ | Email | +---------+ | a@b.com | +---------+ Explanation: a@b.com is repeated two times.
SQL Solution
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;Alternative Solution (using self join):
SELECT DISTINCT p1.email AS Email
FROM Person p1
JOIN Person p2 ON p1.email = p2.email
WHERE p1.id != p2.id;Solution Explanation
This problem can be solved in two ways:
- Using GROUP BY:
- Group by email
- Count occurrences
- Filter with HAVING
- Using Self Join:
- Join table with itself
- Match emails
- Different IDs
Key points:
- Handle case sensitivity
- Remove duplicates
- Count occurrences
- Result formatting
Important considerations:
- GROUP BY vs JOIN
- Performance impact
- DISTINCT requirement
- Column aliasing