4 Ways to Find Duplicate Rows in MySQL

To find duplicate rows in MySQL, first choose the columns that define a duplicate. For example, if each customer should have a unique email address, group by email; if an order line is identified by both order_id and product_id, group by those two columns. Do not include a unique primary key in the duplicate key, or every row will form its own group.

The examples use this table:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(255),
    customer_name VARCHAR(100)
);

Assume customer IDs 1 and 3 share the same email, while ID 2 has a different email.

1. Find duplicate values with GROUP BY and HAVING

Group rows by the columns that should be unique, then keep groups with more than one row:

SELECT email, COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

email              occurrences
-----------------  -----------
[email protected]    2

This returns duplicate email values and their counts, not the full customer records. HAVING filters grouped results, so use it for the COUNT(*) condition. MySQL documents this grouping behavior in its GROUP BY reference.

For a composite key, group by every column that defines uniqueness:

SELECT order_id, product_id, COUNT(*) AS occurrences
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

2. Return every row that belongs to a duplicate group

Join the duplicate keys back to the original table to see all columns for each matching row:

SELECT c.customer_id, c.email, c.customer_name
FROM customers AS c
JOIN (
    SELECT email
    FROM customers
    WHERE email IS NOT NULL
    GROUP BY email
    HAVING COUNT(*) > 1
) AS duplicates ON duplicates.email = c.email
ORDER BY c.email, c.customer_id;

The inner query finds duplicate emails. The outer query retrieves each customer’s full row for those emails.

The examples filter out NULL emails because a missing email is not treated as a duplicate address here. Remove WHERE email IS NOT NULL if you want MySQL to group and report repeated NULL values too; MySQL treats NULL values as equal for GROUP BY (NULL handling).

3. Show the duplicate count beside each row

MySQL 8.0 and later support aggregate window functions. COUNT(*) OVER (PARTITION BY email) keeps individual rows while displaying the number of rows that share each email:

SELECT
    customer_id,
    email,
    COUNT(*) OVER (PARTITION BY email) AS occurrences
FROM customers
WHERE email IS NOT NULL
ORDER BY email, customer_id;

To return only duplicate rows, put the window calculation in a common table expression and filter it in the outer query:

WITH counted AS (
    SELECT
        customer_id,
        email,
        COUNT(*) OVER (PARTITION BY email) AS occurrences
    FROM customers
    WHERE email IS NOT NULL
)
SELECT customer_id, email, occurrences
FROM counted
WHERE occurrences > 1
ORDER BY email, customer_id;

Window functions run after WHERE filtering in the query block, so the outer query is where this result can be filtered. See MySQL’s window function syntax.

4. Mark all but one row with ROW_NUMBER()

Use ROW_NUMBER() when you need to distinguish one row to keep from the extra rows in each duplicate group. This example keeps the lowest customer_id and labels later IDs as duplicates:

WITH ranked AS (
    SELECT
        customer_id,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS row_num
    FROM customers
    WHERE email IS NOT NULL
)
SELECT customer_id, email, row_num
FROM ranked
WHERE row_num > 1
ORDER BY email, row_num;

Choose the ORDER BY columns to reflect which record should be kept, and include a unique tie-breaker for deterministic numbering. MySQL assigns row numbers within each partition; without an ORDER BY, their order is nondeterministic. See the ROW_NUMBER() reference.

For more detail about the function, see SQLiz’s MySQL ROW_NUMBER() reference.

This query only identifies rows; it does not delete them. Review the results and choose a retention rule before removing any records.