How to Find Duplicates in SQL (and Delete Them Safely)

TL;DR: To find duplicates in SQL, group by the columns that define "the same record" and keep groups with HAVING COUNT(*) > 1. To see the full duplicate rows or delete the extras, number each row inside its group with ROW_NUMBER() and act on every row numbered above 1. Before deleting anything, check whether the duplicates are real or were created by a join.

The fastest query to find duplicates is SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;. It returns one line per duplicated value and how many times it appears. To check for duplicates across several columns, list all of them in both SELECT and GROUP BY. This works the same way in PostgreSQL, MySQL, SQL Server, SQLite, Snowflake and BigQuery. The rest of this guide covers seeing the full rows, deleting duplicates while keeping one, the rules that differ by dialect, and the joins that make data look duplicated when it isn't.

What you need:

• Read access to the table, plus DELETE rights if you plan to clean it up.
• A decision on what "duplicate" means for this table: the same email? The same (customer_id, order_date, amount)? Every column except the primary key? Write that list down first. Every query below depends on it.
• A database version with window functions for the ranking steps (MySQL needs 8.0, per the MySQL docs).

The examples use this table:

CREATE TABLE customers (
  id         BIGINT PRIMARY KEY,
  email      TEXT,
  first_name TEXT,
  created_at TIMESTAMP
);

How do I find duplicate rows in SQL?

Follow these steps in order. Steps 1 to 4 only read data, so they are safe to run on production.

1. Count duplicates per value. Group by the column that should be unique and keep groups that appear more than once:

   SELECT email, COUNT(*) AS copies
   FROM customers
   GROUP BY email
   HAVING COUNT(*) > 1
   ORDER BY copies DESC;

HAVING filters after grouping, which is why WHERE COUNT(*) > 1 raises an error (PostgreSQL SELECT docs).

1. Measure the size of the problem. Count how many extra rows exist in total, not only how many values are affected:

   SELECT COUNT(*) - COUNT(DISTINCT email) AS extra_rows
   FROM customers;

COUNT(DISTINCT email) ignores NULLs, so if the column contains NULLs, run Step 1 with WHERE email IS NOT NULL and handle the NULL rows on their own.

1. See every duplicate row in full. A grouped result hides id and created_at, which you need to decide which copy to keep. A window count keeps every row:

   SELECT *
   FROM (
     SELECT c.*,
            COUNT(*) OVER (PARTITION BY email) AS copies
     FROM customers c
   ) t
   WHERE copies > 1
   ORDER BY email, created_at;

The subquery is required because window functions are evaluated after WHERE, so you cannot filter on them in the same query level (PostgreSQL window tutorial).

1. Mark which copy to keep. Number the rows inside each group. The ORDER BY inside OVER decides the survivor. Here the oldest record wins:

   SELECT id, email, created_at,
          ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
   FROM customers;

Rows with rn = 1 stay. Rows with rn > 1 are the extras. Add id as a tiebreaker so two rows with the same timestamp still get a stable order.

How do I find duplicates across multiple columns?

To find duplicates across multiple columns, put every column that defines a duplicate in both SELECT and GROUP BY. An order counts as duplicated when the same customer placed the same amount on the same day:

SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;

For the window versions, use the same list in PARTITION BY customer_id, order_date, amount.

To treat a row as duplicated when every column except the key matches, list all of those columns. There is no portable GROUP BY * EXCEPT id. In PostgreSQL you can compare whole rows as a shortcut, but in practice an explicit column list is easier to review.

Near-duplicates: case and whitespace

Ann@Example.com and ann@example.com count as different strings in a case-sensitive collation, which is the PostgreSQL default. Normalize inside the grouping expression:

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

MySQL's default collations (utf8mb4_0900_ai_ci in 8.0) are case- and accent-insensitive, so MySQL may already group these together while PostgreSQL won't. The same data can return different duplicate counts on different databases.

How NULLs behave

GROUP BY puts all NULLs in one group (PostgreSQL docs), so 40 customers with no email show up as one "duplicate" value with 40 copies. That usually isn't a real duplicate. Filter it out with WHERE email IS NOT NULL unless missing values matter to you.

How do I delete duplicates but keep one row?

To delete duplicates but keep one row, number the rows with ROW_NUMBER() and delete every row where the number is above 1, matching on the primary key. The syntax depends on the database.

Always run it as BEGIN; ... ; SELECT ...; COMMIT; (or ROLLBACK; if the count looks wrong).

PostgreSQL:

BEGIN;
DELETE FROM customers
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);
-- check, then COMMIT or ROLLBACK

Without a primary key, PostgreSQL's ctid (the row's physical location) can stand in for id. It changes after UPDATE or VACUUM FULL, so only use it inside a single statement (PostgreSQL system columns). DELETE ... USING is the join form (PostgreSQL DELETE):

DELETE FROM customers a
USING customers b
WHERE a.email = b.email
  AND (a.created_at, a.id) > (b.created_at, b.id);

MySQL 8.0: writing DELETE FROM customers WHERE id IN (SELECT ... FROM customers ...) fails with ERROR 1093 (HY000): You can't specify target table 'customers' for update in FROM clause (MySQL subquery restrictions). Join to a derived table instead:

DELETE c
FROM customers c
JOIN (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
  FROM customers
) d ON d.id = c.id
WHERE d.rn > 1;

MySQL 5.7 uses the multi-table DELETE with a self-join (MySQL DELETE). This keeps the lowest id per email:

DELETE c1
FROM customers c1
JOIN customers c2
  ON c1.email = c2.email
 AND c1.id > c2.id;

SQL Server: a CTE that reads from one table can be the target of a DELETE, which removes the underlying rows (Microsoft Learn: WITH common_table_expression):

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
  FROM customers
)
DELETE FROM ranked WHERE rn > 1;

If you only need a de-duplicated result and don't want to change the table, PostgreSQL's SELECT DISTINCT ON (email) * FROM customers ORDER BY email, created_at; keeps the first row of each group (DISTINCT ON).

How do I check the cleanup worked and prevent repeats?

Verify

• Rerun the Step 1 query. It should return zero rows.
• Compare row counts: before - after should equal extra_rows from Step 2.
• Check that foreign keys still resolve. If orders.customer_id pointed at a deleted duplicate, re-point those rows to the survivor before the delete, or the delete will fail (with a FK constraint) or leave orphans (without one).

Prevent

Add a unique constraint once the table is clean:

CREATE UNIQUE INDEX customers_email_uq ON customers (LOWER(email));

Two things to know about NULLs. A standard UNIQUE constraint allows any number of NULLs because NULLs count as distinct. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to change that (PostgreSQL 15 CREATE TABLE). Then make your inserts idempotent with INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE / ON DUPLICATE KEY UPDATE (MySQL).

Why does my join return duplicate rows?

A join returns duplicate rows when one side has more than one matching row. Each customers row is repeated once for every matching row in orders, so a customer with three orders appears three times. The table isn't duplicated. The relationship is one-to-many, and the query treated it as one-to-one. Metabase's debugging guide lists this as the most common cause of "duplicated" results.

To tell the two apart:

1. Run Step 1 against each source table on its own. If the counts are clean, the duplication comes from the join.
2. Check the join keys. Count rows per key on the "many" side: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;
3. Fix the query, not the data. Aggregate the many side first and then join:

WITH order_totals AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
)
SELECT c.email, o.orders, o.revenue
FROM customers c
LEFT JOIN order_totals o ON o.customer_id = c.id;

Adding DISTINCT to cover up join duplicates is a common mistake. The row count looks right, but any SUM computed before the DISTINCT is still inflated. Our SQL join glossary entry explains the join types.

Finding duplicates without writing the query

Most of the work here is deciding what counts as a duplicate and remembering each dialect's rules. If you would rather ask "which customers signed up twice with the same email, and which record is older?" in plain English, AI for Database connects to PostgreSQL, MySQL, SQL Server and other databases, and writes the SQL for your schema. Try AI for Database free at aifordatabase.com. Sample data is included. More dialect notes are on our PostgreSQL and MySQL pages.