---
title: "How to Find Duplicates in SQL (and Delete Them Safely)"
description: "To find duplicates in SQL, group by the columns that define \"the same record\" and keep groups with HAVING COUNT(*) > 1."
canonical: "https://www.aifordatabase.com/blog/find-duplicates-sql-query/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-10-23
updated: 2026-10-23
---

# 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](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html)).

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](https://www.postgresql.org/docs/current/sql-select.html)).

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](https://www.postgresql.org/docs/current/tutorial-window.html)).

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](https://www.postgresql.org/docs/current/sql-select.html)), 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.

| Database | Method | Notes |
| --- | --- | --- |
| PostgreSQL | `DELETE ... WHERE id IN (subquery with ROW_NUMBER)` or `DELETE ... USING` | `ctid` works if there is no key |
| MySQL 8.0+ | `DELETE` joined to a derived table with `ROW_NUMBER()` | A direct subquery on the same table raises error 1093 |
| MySQL 5.7 | Self-join `DELETE t1 FROM t t1 JOIN t t2` | No window functions |
| SQL Server | `DELETE` from a CTE | The CTE is updatable |
| SQLite 3.25+ | `DELETE ... WHERE rowid IN (...)` | Use `rowid` if there is no key |

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](https://www.postgresql.org/docs/current/ddl-system-columns.html)). `DELETE ... USING` is the join form ([PostgreSQL DELETE](https://www.postgresql.org/docs/current/sql-delete.html)):

```
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](https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html)). 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](https://dev.mysql.com/doc/refman/8.0/en/delete.html)). 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](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql)):

```
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](https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT)).

## 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](https://www.postgresql.org/docs/15/sql-createtable.html)). 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](https://www.metabase.com/learn/sql/debugging-sql/sql-logic-duplicated-data) 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](https://www.aifordatabase.com/glossary/join/) 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](https://app.aifordatabase.com/signup). Sample data is included. More dialect notes are on our [PostgreSQL](https://www.aifordatabase.com/databases/postgresql/) and [MySQL](https://www.aifordatabase.com/databases/mysql/) pages.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Does DISTINCT remove duplicates from the table?

No. DISTINCT only changes what a query returns; stored rows stay untouched. Removing them takes a DELETE or a rebuild into a new table.

### Is checking for duplicates slow on large tables?

Grouping scans the whole table and sorts or hashes the grouped columns. On tens of millions of rows an index on the checked column lets the planner skip the sort. Run it on a read replica if one exists.

### Can a primary key stop duplicate records?

Only for the key itself. An auto-increment id makes every row technically unique, which is why business duplicates (same email, same order) slip through. A unique index on the business columns is what blocks them. Related: \[what SQL is\](https://www.aifordatabase.com/glossary/sql/), \[SQL Server on AI for Database\](https://www.aifordatabase.com/databases/sql-server/).

Read next

*   [POSTGRESQL GETDATE() EQUIVALENT: NOW() AND SYSDATE →](https://www.aifordatabase.com/blog/postgresql-getdate/)
*   [SQL PIVOT TABLE: PIVOT, CROSSTAB AND CASE EXAMPLES →](https://www.aifordatabase.com/blog/sql-pivot-table/)
*   [SQL PARTITION BY: EXAMPLES, VS GROUP BY, PITFALLS →](https://www.aifordatabase.com/blog/sql-partition-by/)

Source: https://www.aifordatabase.com/blog/find-duplicates-sql-query/