To find duplicates in SQL, group the rows by the columns that should be unique and keep the groups with more than one row. Window functions and self-joins then show you the duplicate rows themselves, so you can decide which copy to keep. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.
How to find duplicates in SQL
GROUP BY puts rows with the same value together, COUNT(*) counts each group, and HAVING keeps the groups with more than one row:
SELECT columns, COUNT(*)
FROM table_name
GROUP BY columns
HAVING COUNT(*) > 1;
For example, to find email addresses used by more than one customer:
-- Input: customers
-- id | name | email
-- ---+----------------+----------------------
-- 1 | Maria Lopez | maria.lopez@gmail.com
-- 2 | Sam Patel | sam@patel.dev
-- 3 | M. Lopez | maria.lopez@gmail.com
-- 4 | Kenji Watanabe | kenji@watanabe.jp
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
-- Output:
-- email | copies
-- ----------------------+-------
-- maria.lopez@gmail.com | 2
To count rows as duplicates only when several columns all match, list every one of them in both SELECT and GROUP BY.
Rules to know
-
Every column you select must be in
GROUP BYor inside an aggregate. Addingnameto the query above doesn’t show the names of the duplicates. It’s an error, except in SQLite, which shows one name per email:
-- Input: the customers table above
SELECT name, email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
-- ERROR: column "customers.name" must appear in the GROUP BY clause or be used in an aggregate function
To see every duplicate row, use one of the queries under “Alternative syntax”.
-
NULLs group together, and case matters. All the NULL emails land in one group, so they look like duplicates of each other, while
Ana@Silva.devandana@silva.devdon’t:
-- Input: users
-- id | email
-- ---+--------------
-- 1 | ana@silva.dev
-- 2 | NULL
-- 3 | Ana@Silva.dev
-- 4 | NULL
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Output:
-- email | copies
-- ------+-------
-- NULL | 2
-- MySQL and SQL Server ignore case by default, so they also return ana@silva.dev | 2
-- Skip the NULLs and ignore case:
SELECT LOWER(email) AS email, COUNT(*) AS copies
FROM users
WHERE email IS NOT NULL
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;
-- Output:
-- email | copies
-- --------------+-------
-- ana@silva.dev | 2
Alternative syntax
GROUP BY returns one row per duplicated value. To get the duplicate rows themselves, count over a window instead. COUNT(*) OVER (PARTITION BY email) puts each email’s count on every row that has it:
-- Input: the customers table above
SELECT id, name, email, copies
FROM (
SELECT id, name, email, COUNT(*) OVER (PARTITION BY email) AS copies
FROM customers
) AS c
WHERE copies > 1
ORDER BY id;
-- Output:
-- id | name | email | copies
-- ---+-------------+-----------------------+-------
-- 1 | Maria Lopez | maria.lopez@gmail.com | 2
-- 3 | M. Lopez | maria.lopez@gmail.com | 2
EXISTS does the same without a window function: keep each row for which another row with the same email exists:
-- Input: the customers table above
SELECT id, name, email
FROM customers c
WHERE EXISTS (
SELECT 1 FROM customers other
WHERE other.email = c.email AND other.id <> c.id
);
-- Output:
-- id | name | email
-- ---+-------------+----------------------
-- 1 | Maria Lopez | maria.lopez@gmail.com
-- 3 | M. Lopez | maria.lopez@gmail.com
When should you look for duplicates?
Duplicates get in through imports, double clicks, retries and missing constraints. These are the four jobs you’ll most often need to do about them.
Check that a column is unique
Before you add a UNIQUE constraint, or when a login or a lookup starts returning two rows, list the values that appear more than once. GROUP BY with HAVING answers this directly.
See every row involved
Knowing which value repeats isn’t enough to fix it: you need the full rows to see how they differ and which one is right. Feed the duplicated values back into a query on the same table.
Mark the extra copies
To clean up, keep one row from each set of duplicates and remove the rest. ROW_NUMBER() numbers the rows within each set, so everything after row 1 is an extra copy.
Catch double submissions
A double-clicked button or a retried payment leaves two rows that match on everything but their ID. Joining the table to itself pairs them up, and t1.id < t2.id lists each pair once.
Real examples of finding duplicates
Find double-booked hotel rooms
A room can only be booked once per night, so a duplicate is the same room and night together. Group by both columns:
-- Input: bookings
-- booking_id | room | night | guest
-- -----------+------+------------+----------
-- 1 | 204 | 2026-11-06 | R. Okafor
-- 2 | 204 | 2026-11-07 | R. Okafor
-- 3 | 310 | 2026-11-06 | L. Chen
-- 4 | 204 | 2026-11-06 | P. Novak
SELECT room, night, COUNT(*) AS bookings
FROM bookings
GROUP BY room, night
HAVING COUNT(*) > 1;
-- Output:
-- room | night | bookings
-- -----+------------+---------
-- 204 | 2026-11-06 | 2
List accounts that share an email address
People sign up twice with the same address in different case. Partition by LOWER(email) to put both accounts side by side:
-- Input: accounts
-- id | username | email
-- ---+----------+----------------
-- 1 | jkim | jin@kim.kr
-- 2 | olivia_r | olivia@reyes.mx
-- 3 | jin.kim | Jin@Kim.kr
-- 4 | noah_b | noah@baker.uk
SELECT id, username, email, accounts_with_email
FROM (
SELECT id, username, email,
COUNT(*) OVER (PARTITION BY LOWER(email)) AS accounts_with_email
FROM accounts
) AS a
WHERE accounts_with_email > 1
ORDER BY id;
-- Output:
-- id | username | email | accounts_with_email
-- ---+----------+------------+--------------------
-- 1 | jkim | jin@kim.kr | 2
-- 3 | jin.kim | Jin@Kim.kr | 2
Flag duplicate people before a cleanup
Treat users with the same first and last name as one person, keep the oldest record, and label the others. A CTE keeps the numbering separate from the labeling:
-- Input: users
-- id | first_name | last_name | email
-- ---+------------+-----------+----------------
-- 1 | Priya | Shah | priya@shah.net
-- 2 | Tom | Becker | tom@becker.de
-- 3 | Priya | Shah | pshah@work.com
-- 4 | Lena | Kowalski | lena@kowal.pl
-- 5 | Tom | Becker | tbecker@mail.de
WITH numbered AS (
SELECT id, first_name, last_name, email,
ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY id) AS copy_number
FROM users
)
SELECT id, first_name, last_name, email,
CASE WHEN copy_number > 1 THEN 'Duplicate' ELSE 'Original' END AS status
FROM numbered
ORDER BY id;
-- Output:
-- id | first_name | last_name | email | status
-- ---+------------+-----------+-----------------+----------
-- 1 | Priya | Shah | priya@shah.net | Original
-- 2 | Tom | Becker | tom@becker.de | Original
-- 3 | Priya | Shah | pshah@work.com | Duplicate
-- 4 | Lena | Kowalski | lena@kowal.pl | Original
-- 5 | Tom | Becker | tbecker@mail.de | Duplicate
The rows marked Duplicate are the ones to merge or delete. The remove duplicates guide shows how to delete them.
Find orders placed twice by mistake
Two orders from the same customer for the same product on the same day are probably one order submitted twice:
-- Input: orders
-- order_id | customer_id | product | order_date
-- ---------+-------------+-----------+-----------
-- 1001 | 42 | Desk Lamp | 2026-03-02
-- 1002 | 42 | Desk Lamp | 2026-03-02
-- 1003 | 42 | Desk Lamp | 2026-03-09
-- 1004 | 57 | Desk Lamp | 2026-03-02
SELECT o1.order_id AS first_order, o2.order_id AS repeat_order,
o1.customer_id, o1.product
FROM orders o1
JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o1.product = o2.product
AND o1.order_date = o2.order_date
AND o1.order_id < o2.order_id;
-- Output:
-- first_order | repeat_order | customer_id | product
-- ------------+--------------+-------------+----------
-- 1001 | 1002 | 42 | Desk Lamp
Further reading about duplicates
On this blog:
- SQL remove duplicates guide: delete the extra copies once you’ve found them
- Detect duplicate rows in a SQL Server table: the same problem in T-SQL
- SQL COUNT DISTINCT guide: count values with the duplicates left out
-
SQL GROUP BY guide: grouping and
HAVINGin depth -
Having vs Where in SQL: why the count filter goes in
HAVING - Creating unique constraints in PostgreSQL: stop new duplicates getting in
- SQL CTE vs subquery guide: the two ways to structure these queries
Official documentation:
- PostgreSQL: GROUP BY and HAVING, window functions
- MySQL: SELECT, including GROUP BY and HAVING, ROW_NUMBER()
- SQLite: SELECT, including GROUP BY and HAVING, window functions
- SQL Server: HAVING, ROW_NUMBER
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."