How to Find Duplicate Rows in SQL

Find duplicate values in one column

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

HAVING filters groups after they are counted, which is why it works here where WHERE cannot.

Find duplicates across multiple columns

SELECT first_name, last_name, COUNT(*) AS total
FROM users
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

List the actual duplicate rows

SELECT * FROM users
WHERE email IN (
  SELECT email FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

Delete duplicates and keep one

DELETE u1 FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

This keeps the row with the lowest id and removes the rest. Always back up your table before running a delete like this.

Frequently asked questions

Why use HAVING instead of WHERE?

WHERE filters individual rows before grouping, so it cannot see a count. HAVING runs after the rows are grouped and counted, which is exactly when you know how many duplicates exist.

How do I count how many duplicates there are?

The COUNT(*) AS total column in the first query tells you how many times each value appears.

Want to get comfortable with GROUP BY and joins? Our free SQL course has a live, in-browser query runner.