How to find duplicate rows in SQL

Learn how to find duplicate rows in SQL with examples and explanations from SQLPad.

Problem

The task is to identify duplicate rows in a SQL database. A row is considered a duplicate if all its columns have the same values as another row.

Sample Data

Let's assume we have a users table with the following data:

Id	Name	Email
1	John	[email protected]
2	Jane	[email protected]
3	Alice	[email protected]
4	John	[email protected]
5	Bob	[email protected]

MySQL Solution

SELECT Name, Email, COUNT(*)
FROM users
GROUP BY Name, Email
HAVING COUNT(*) > 1;

Explanation for MySQL Solution

In the above SQL query, we are grouping the users table by the Name and Email columns. The COUNT(*) function counts the number of rows in each group. If a group has more than one row (COUNT(*) > 1), it means there are duplicate rows.

PostgreSQL Solution

SELECT Name, Email, COUNT(*)
FROM users
GROUP BY Name, Email
HAVING COUNT(*) > 1;

Explanation for PostgreSQL Solution

The PostgreSQL solution is the same as the MySQL solution, since the SQL syntax for finding duplicate rows is the same in both types of databases. We are grouping the users table by the Name and Email columns, then using the COUNT(*) function to count the number of rows in each group. If a group has more than one row, it means that there are duplicate rows.