SQL ยท Lesson 95 of 126
Finding Duplicates
GROUP BY with HAVING.
Find the duplicate emails in the customers table. It's quick to write, and it hides a little trap.

- Find duplicate emails
- Quick to write
- With a little trap
GROUP BY and HAVING
Group by email, and keep the groups with more than one row. Run it. Interesting. The only duplicate is a blank, thirty one times. Those are the customers with no email. Group by puts all nulls into one group. So in an interview, mention it, and add where email is not null.
query.sqlsql
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;Output
โโโโโโโโโฌโโโโโโโโโ โ email โ copies โ โโโโโโโโโผโโโโโโโโโค โ โ 31 โ โโโโโโโโโดโโโโโโโโโ
Say it out loud
In an interview, talking matters as much as typing. Say what you're doing. Mention edge cases, like nulls, ties, and empty results. And check your answer with a quick count. Interviewers want to see how you think, not just the final query.
Explainsay what you're doing
Edge casesNULLs, ties, empty results
Checka quick COUNT
Showhow you think
Summary
Group by plus having finds duplicates, and nulls form their own group. Next, the trap that catches the most people.
Key takeaways- GROUP BY ... HAVING COUNT(*) > 1
- NULLs group together
- Mention edge cases
Next up: The NOT IN Trap โ NULL breaks NOT IN