Duplicate Emails - Problem

You have a table Person with the following structure:

  • id (int): Primary key, unique identifier for each person
  • email (varchar): Email address (guaranteed not NULL, no uppercase letters)

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

Return the result in any order.

Table Schema

Person
Column Name Type Description
id PK int Primary key, unique identifier
email varchar Email address (no NULL values)
Primary Key: id
Note: Each row represents a person with their email. Multiple people can have the same email.

Input & Output

Example 1 — Basic Duplicate Detection
Input Table:
id email
1 [email protected]
2 [email protected]
3 [email protected]
Output:
email
[email protected]
💡 Note:

The email [email protected] appears twice (id 1 and id 3), so it's returned as a duplicate. The email [email protected] appears only once, so it's not included in the result.
Example 2 — Multiple Duplicates
Input Table:
id email
1 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
Output:
email
[email protected]
[email protected]
💡 Note:

Both [email protected] and [email protected] appear twice, so both are returned as duplicates. [email protected] appears only once and is not included.
Example 3 — No Duplicates
Input Table:
id email
1 [email protected]
2 [email protected]
3 [email protected]
Output:
email
💡 Note:

All emails are unique, so no duplicates are found. The result is an empty table.

Constraints

  • 1 ≤ Person.id ≤ 1000
  • email is guaranteed to be not NULL
  • email contains no uppercase letters

Visualization

Tap to expand
Duplicate Emails - SQL Solution INPUT: Person Table id email 1 [email protected] 2 [email protected] 3 [email protected] 4 [email protected] 5 [email protected] Duplicate: [email protected] Duplicate: [email protected] Find emails appearing more than once ALGORITHM STEPS 1 GROUP BY email Group all rows by email 2 COUNT(*) Count occurrences per email 3 HAVING count > 1 Filter groups with count > 1 4 SELECT email Return duplicate emails SELECT email FROM Person GROUP BY email HAVING COUNT(*) > 1; Optimal SQL Query FINAL RESULT email [email protected] [email protected] OK - 2 Duplicates Email Count Summary: [email protected] 2x [email protected] 2x [email protected] 1x Only count > 1 returned Key Insight: Using GROUP BY with HAVING is the optimal approach for finding duplicates in SQL. GROUP BY aggregates rows by email, COUNT(*) counts each group, and HAVING filters groups after aggregation. This is more efficient than self-joins or subqueries. Time: O(n) TutorialsPoint - Duplicate Emails | Optimal SQL Solution using GROUP BY and HAVING

Related Problems

Asked in
Amazon 15 Facebook 12 Google 8
128.0K Views
High Frequency
~8 min Avg. Time
2.5K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen