How to check duplicates in SQL is a practical database task that helps you protect data quality, keep reports accurate, and maintain clean customer records. When duplicate rows exist in a table, even simple SELECT queries can return the same information multiple times, which affects counts, joins, reports, and analytics. A strong duplicate check gives you a clear way to identify repeated records before you apply UNIQUE constraints or clean the data.
Quick note before moving on.
Why Duplicate Data Can Cause Problems
Duplicate records are more than a minor annoyance. They can distort business metrics, inflate user counts, create billing errors, and make it difficult to trust dashboards. In practice, for example, if a customers table contains two rows for the same person with a small spelling difference, reports may show double the expected audience. If an orders table has repeated entries, inventory and revenue calculations become unreliable That's the part that actually makes a difference..
Quick note before moving on.
Because of this, learning how to check duplicates in SQL is essential for data analysts, backend developers, database administrators, and anyone who works with relational databases.
How to Check Duplicates in SQL
You've got several reliable methods worth knowing here. Each method has strengths depending on the structure of your table and the level of detail you need.
Method 1: GROUP BY and HAVING
The most common approach uses GROUP BY with HAVING COUNT(*) > 1. This groups rows by the columns you choose and then filters groups that contain more than one record Still holds up..
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
If you want to check duplicates across all columns, you can group by every column:
SELECT column1, column2, column3, COUNT(*)
FROM your_table
GROUP BY column1, column2, column3
HAVING COUNT(*) > 1;
This method is efficient for identifying duplicate values in specific columns and works across all major SQL databases, including PostgreSQL, MySQL, SQL Server, and Oracle. Even so, it only shows the duplicated values and their counts—not the full rows. To see the complete duplicate records, you can join the grouped results back to the original table.
### Method 2: ROW_NUMBER() Window Function
Window functions give you more control when you need to inspect entire duplicate rows or assign a rank to each occurrence. The `ROW_NUMBER()` function partitions rows by the columns you suspect contain duplicates and orders them arbitrarily (or by a timestamp or ID).
```sql
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY id
) AS rn
FROM users
) AS ranked
WHERE rn > 1;
This query returns every row that is a duplicate based on the email column, excluding the first occurrence. You can partition by multiple columns to check for duplicates across a combination of fields:
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY first_name, last_name, phone
ORDER BY id
) AS rn
FROM customers
) AS ranked
WHERE rn > 1;
The ROW_NUMBER() approach is especially useful when you need to keep one copy of each duplicate (for example, the earliest record) and delete the rest. You can change the ORDER BY clause to control which row is considered the “first” occurrence.
Method 3: Self-Join
A self-join compares a table to itself to find rows that match on specific columns. This method is straightforward but can be slower on large tables because it may produce a Cartesian product if not carefully filtered The details matter here..
SELECT a.*
FROM users a
JOIN users b
ON a.email = b.email
AND a.id < b.id;
Here, a.id ensures that each duplicate pair is returned only once and avoids matching a row to itself. id < b.Self-joins are handy when you want to see both duplicate rows side by side or when you need to compare additional columns between the duplicates.
Worth pausing on this one.
Method 4: Using DISTINCT and COUNT
Sometimes you only need a quick count of how many duplicate rows exist. You can compare the total row count with the count of distinct values:
SELECT
(SELECT COUNT(*) FROM users) AS total_rows,
(SELECT COUNT(*) FROM (SELECT DISTINCT email FROM users) AS distinct_emails) AS distinct_rows;
If total_rows is greater than distinct_rows, duplicates exist. This method is fast for a high-level check but does not identify which rows are duplicated.
Checking Duplicates Across Multiple Columns
Real-world duplicate checks often involve more than one column. Consider this: for example, a customer might have the same first name and last name but a different email, or the same phone number but a different address. In these cases, you should define a composite key that represents what “duplicate” means for your business logic.
Using GROUP BY with multiple columns:
SELECT first_name, last_name, phone, COUNT(*)
FROM customers
GROUP BY first_name, last_name, phone
HAVING COUNT(*) > 1;
Using ROW_NUMBER() with multiple partition columns:
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY first_name, last_name, phone
ORDER BY created_at
) AS rn
FROM customers
) AS ranked
WHERE rn > 1;
Be mindful of NULL values. In SQL, NULL is not equal to NULL, so GROUP BY treats all NULLs as a single group, while joins and comparisons may behave differently. If your duplicate check involves nullable columns, consider using COALESCE or IS NOT DISTINCT FROM (where supported) to handle them consistently.
Performance Considerations
Duplicate checks can be expensive on large tables. To keep queries fast:
- Index the columns you use in
GROUP BY,PARTITION BY, or join conditions. - Limit the columns you select—avoid
SELECT *when you only need a few fields. - Use
EXISTSorROW_NUMBER()instead of self-joins when possible, as they often perform better. - Run checks during off-peak hours if the table is large and actively used.
- Consider temporary tables or materialized views for repeated duplicate
analysis. If you frequently audit the same tables, materializing the duplicate keys once and referencing that smaller dataset can save significant resources.
Deleting or Merging Duplicates
Identifying duplicates is only half the battle; resolving them requires a strategy aligned with your data integrity requirements.
Soft Deletes and Archiving
Before hard-deleting data, consider a soft-delete pattern (e.g., an is_deleted or archived_at column). This preserves history for auditing and allows recovery if the duplicate logic was flawed Simple, but easy to overlook..
Deleting with ROW_NUMBER()
This is the most common and safest pattern for modern databases (PostgreSQL, SQL Server, Oracle, recent MySQL/MariaDB). It allows you to precisely define which row survives (e.g., the oldest, the newest, or the one with the most complete data).
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, id ASC -- Keep the oldest record
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
Deleting with CTID / Physical Locators (PostgreSQL Specific)
In PostgreSQL, ctid represents the physical location of a row. This can be faster than joining on primary keys for massive deletes because it avoids index lookups, but it is non-standard SQL.
DELETE FROM users
WHERE ctid NOT IN (
SELECT min(ctid)
FROM users
GROUP BY email
);
Merging Data (Upsert Logic)
If duplicates contain fragmented information (e.g., Row A has a phone number, Row B has an address), you may need to merge them into a single "golden record" before deleting the extras. This typically requires an UPDATE statement joining the table to a CTE of aggregated values, followed by the deletion of the now-redundant rows.
Prevention: Constraints and Application Logic
The most efficient duplicate check is the one you never have to run because the database prevents the issue entirely.
- Primary Keys & Unique Constraints: The gold standard.
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);throws an error on insert/update violations instantly. - Partial/Filtered Unique Indexes: Useful for soft-delete patterns.
This allows multiple "deleted" users with the same email but only one "active" one.CREATE UNIQUE INDEX uq_active_email ON users(email) WHERE deleted_at IS NULL; - Application-Level Idempotency: Implement "Insert ... ON CONFLICT DO NOTHING" (PostgreSQL), "INSERT IGNORE" / "ON DUPLICATE KEY UPDATE" (MySQL), or "MERGE" (SQL Server/Oracle) in your data ingestion pipelines. This shifts the burden from a batch cleanup job to the write path.
Conclusion
Duplicate data is a symptom of missing constraints, flawed ingestion logic, or evolving business requirements. By mastering GROUP BY for discovery, window functions for precise identification and deletion, and unique constraints for prevention, you move from reactive cleanup to proactive data governance. Treat duplicate checks not as a one-time script, but as a recurring CI/CD gate or scheduled observability metric. Clean data isn't just about storage efficiency—it is the foundation of trustworthy analytics, reliable machine learning models, and correct business decisions Worth keeping that in mind..