AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 1
SQL ยท Lesson 95 of 126

Finding Duplicates

GROUP BY with HAVING.

โฑ 2 min readโ–ถ Video at 2:15:11๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Find the duplicate emails in the customers table. It's quick to write, and it hides a little trap.

Finding Duplicates
From the video: Finding Duplicates
  • 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