Course: MySQL Training
Topic: Finding Duplicate Records Using SQL Queries
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
Duplicate records are rows in a database that contain the same value in one or more columns according to a defined business rule. They can occur when users submit a form multiple times, data is imported repeatedly, applications retry an operation, or database tables do not enforce appropriate uniqueness rules.
Duplicate data can cause problems in reporting, customer management, billing, inventory tracking, and data analysis. For example, if a customer appears twice in a customer list, a report may count that customer more than once. If an order is accidentally inserted twice, total sales calculations may become inaccurate.
MySQL provides several SQL techniques for finding duplicate records, including GROUP BY with HAVING, COUNT(), self-joins, subqueries, window functions, and comparisons using unique identifiers.
The most important step is to define what makes a record a duplicate. Two rows may have the same email address but different customer IDs, or they may have the same product name but different product codes. The correct query depends on the business rule.
A duplicate record is a row that matches another row according to one or more selected columns or a defined business key.
Consider the following customers table:
| customer_id | customer_name | city | |
|---|---|---|---|
| 1 | Amit Sharma | [email protected] | Mumbai |
| 2 | Priya Verma | [email protected] | Pune |
| 3 | Amit Sharma | [email protected] | Mumbai |
| 4 | Rahul Mehta | [email protected] | Nashik |
| 5 | Neha Singh | [email protected] | Pune |
In this example:
Important: A duplicate is not always a completely identical row. It may be a repeated email, phone number, product code, invoice number, or combination of business fields.
Before learning duplicate-detection queries, create a sample database and table that contains some repeated values.
CREATE DATABASE duplicate_practice;
USE duplicate_practice;CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(255),
phone VARCHAR(20),
city VARCHAR(100)
);The customer_id column uniquely identifies each row. However, the table does not yet enforce uniqueness for email or phone, so repeated values can be inserted.
INSERT INTO customers
(customer_name, email, phone, city)
VALUES
('Amit Sharma', '[email protected]', '9876500001', 'Mumbai'),
('Priya Verma', '[email protected]', '9876500002', 'Pune'),
('Amit Sharma', '[email protected]', '9876500001', 'Mumbai'),
('Rahul Mehta', '[email protected]', '9876500003', 'Nashik'),
('Neha Singh', '[email protected]', '9876500005', 'Pune'),
('Karan Patel', '[email protected]', '9876500006', 'Mumbai'),
('Karan Patel', '[email protected]', '9876500006', 'Mumbai');This dataset contains duplicate customer details for Amit Sharma and Karan Patel. The email [email protected] also appears more than once, but the associated customer names differ.
SELECT *
FROM customers
ORDER BY customer_id;Reviewing the original data helps you understand which columns contain repeated values before writing a duplicate-detection query.
GROUP BY is one of the most common techniques for finding duplicates. It groups rows that share the same values in specified columns. COUNT() counts the rows in each group, and HAVING filters the groups based on their counts.
SELECT column_name, COUNT(*) AS occurrence_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;Explanation:
SELECT
customer_name,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY customer_name
HAVING COUNT(*) > 1;Possible result:
| customer_name | occurrence_count |
|---|---|
| Amit Sharma | 2 |
| Karan Patel | 2 |
This query returns names that occur in more than one row. It does not prove that the records represent the same person, because different people can share a name.
SELECT
email,
COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;The WHERE clause excludes NULL emails from this particular duplicate check. The result identifies repeated non-NULL email values.
SELECT
phone,
COUNT(*) AS occurrence_count
FROM customers
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1;This query identifies phone numbers appearing in multiple rows. If phone numbers may be shared by family members or businesses, investigate the results before merging customer records.
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;This query returns cities containing multiple customers. Although it uses the same duplicate-detection pattern, repeated city values are normally expected and do not indicate duplicate customer records.
Sometimes one column is not enough to identify a duplicate. A more reliable business rule may use a combination of fields, such as customer_name and email or product_name and manufacturer_code.
SELECT
customer_name,
email,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY customer_name, email
HAVING COUNT(*) > 1;This query groups records by both customer_name and email. A duplicate group exists when the same combination appears more than once.
SELECT
customer_name,
email,
phone,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY customer_name, email, phone
HAVING COUNT(*) > 1;This is more restrictive than checking only the name. All three selected values must match for records to belong to the same group.
Suppose a products table contains product_id, product_name, manufacturer_code, and price. To find repeated combinations of product name and manufacturer code, use:
SELECT
product_name,
manufacturer_code,
COUNT(*) AS occurrence_count
FROM products
GROUP BY product_name, manufacturer_code
HAVING COUNT(*) > 1;This approach can help identify products that may have been imported more than once. Confirm the business rule before treating repeated product names as duplicates because different products can share similar names.
If an order system should allow only one order per external reference number, repeated references can be checked as follows:
SELECT
external_reference,
COUNT(*) AS occurrence_count
FROM orders
WHERE external_reference IS NOT NULL
GROUP BY external_reference
HAVING COUNT(*) > 1;The external reference must genuinely be intended to identify one logical order. Some systems allow multiple rows with the same reference for valid business reasons, so confirm the rules before cleaning the data.
GROUP BY with HAVING is useful for identifying repeated values, but it usually displays grouped values and counts rather than every original row. To examine all duplicate records, use a subquery or join the duplicate groups back to the original table.
SELECT c.*
FROM customers AS c
JOIN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS duplicate_emails
ON c.email = duplicate_emails.email
ORDER BY c.email, c.customer_id;The inner query finds duplicate email addresses. The outer query retrieves every customer row associated with those email addresses.
SELECT c.*
FROM customers AS c
JOIN (
SELECT customer_name, email
FROM customers
GROUP BY customer_name, email
HAVING COUNT(*) > 1
) AS duplicate_groups
ON c.customer_name = duplicate_groups.customer_name
AND c.email = duplicate_groups.email
ORDER BY c.customer_name, c.customer_id;This returns the original records belonging to groups with repeated name-and-email combinations. If nullable columns are part of the duplicate key, ordinary equality joins do not match NULL to NULL. Use MySQL's NULL-safe equality operator (<=>) for those comparisons when NULL should count as the same grouping value.
SELECT c.*
FROM customers AS c
JOIN (
SELECT phone
FROM customers
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1
) AS duplicate_phones
ON c.phone = duplicate_phones.phone
ORDER BY c.phone, c.customer_id;This is useful when the data-cleaning team needs the customer IDs and other details for each repeated phone number.
A self-join joins a table to itself. It can be used to compare records and identify pairs that share the same values in one or more columns.
SELECT
c1.customer_id AS first_customer_id,
c2.customer_id AS second_customer_id,
c1.email
FROM customers AS c1
JOIN customers AS c2
ON c1.email = c2.email
AND c1.customer_id < c2.customer_id
WHERE c1.email IS NOT NULL
ORDER BY c1.email;Explanation:
SELECT
c1.customer_id AS first_id,
c2.customer_id AS second_id,
c1.customer_name,
c1.email
FROM customers AS c1
JOIN customers AS c2
ON c1.customer_name = c2.customer_name
AND c1.email = c2.email
AND c1.customer_id < c2.customer_id
ORDER BY c1.customer_name;This query identifies pairs of records with matching names and email addresses. It is useful for comparing individual records rather than just receiving a count for each duplicate group.
A subquery is a query nested inside another SQL statement. It can identify repeated values and return the corresponding records from the original table.
SELECT *
FROM customers
WHERE email IN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email, customer_id;The subquery returns email values that appear more than once. The outer query retrieves every customer whose email is in that set.
SELECT c1.*
FROM customers AS c1
WHERE EXISTS (
SELECT 1
FROM customers AS c2
WHERE c2.email = c1.email
AND c2.customer_id <> c1.customer_id
)
AND c1.email IS NOT NULL
ORDER BY c1.email, c1.customer_id;EXISTS checks whether at least one other row has the same email and a different customer ID. If such a row exists, the current customer is returned.
| Method | Best Use | Output |
|---|---|---|
| GROUP BY and HAVING | Finding repeated values and their counts | One row per duplicate group |
| Join to duplicate groups | Retrieving all original records in duplicate groups | Full matching records |
| Self-join | Comparing duplicate record pairs | Pairs of matching records |
| IN subquery | Retrieving records whose key belongs to duplicate groups | Full matching records |
| EXISTS | Checking whether another matching row exists | Rows with at least one matching counterpart |
| ROW_NUMBER() | Numbering rows within duplicate groups | Rows ranked within each group |
MySQL 8.0 and later support window functions, including ROW_NUMBER(). This function assigns a sequential number to each row within a partition. It is particularly useful when you want to identify the first row in a group and distinguish the additional repeated rows.
ROW_NUMBER() OVER (
PARTITION BY column_name
ORDER BY some_column
)PARTITION BY divides the rows into groups. ORDER BY determines the sequence within each group. ROW_NUMBER() starts at 1 for each partition.
WITH ranked_customers AS (
SELECT
customer_id,
customer_name,
email,
phone,
city,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY customer_id
) AS row_num
FROM customers
WHERE email IS NOT NULL
)
SELECT *
FROM ranked_customers
WHERE row_num > 1
ORDER BY email, row_num;This query assigns row numbers to customers sharing the same email. The first row in each group receives row_num = 1. Rows numbered 2 or higher are additional records within that email group.
Important: The query identifies extra rows according to the chosen ordering; it does not determine which record is correct. Choose the ordering based on a business rule, such as retaining the verified record or the earliest valid creation date.
WITH ranked_customers AS (
SELECT
customer_id,
customer_name,
email,
ROW_NUMBER() OVER (
PARTITION BY customer_name, email
ORDER BY customer_id
) AS row_num
FROM customers
)
SELECT *
FROM ranked_customers
WHERE row_num > 1
ORDER BY customer_name, email;This query identifies additional rows that share the same customer name and email combination. NULL values in the partitioning columns are grouped together by the window function, so decide whether that behavior matches your data-cleaning rules.
| Feature | COUNT() with GROUP BY | ROW_NUMBER() |
|---|---|---|
| Main purpose | Counts rows in each group | Numbers rows within each group |
| Typical output | One row per group | One numbered row per source record |
| Identifying extra rows | Requires joining back to the original table or another query | Filter row_num > 1 |
| MySQL support | Available in older MySQL versions as well | Requires MySQL 8.0 or later |
There is an important difference between counting duplicate groups and counting all extra rows that exceed the first record in each group.
SELECT COUNT(*) AS duplicate_email_groups
FROM (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS duplicate_groups;This counts the number of distinct email values that occur more than once.
SELECT COUNT(*) AS rows_in_duplicate_groups
FROM customers
WHERE email IN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
);This counts every record belonging to a repeated email group, including the first record in each group.
SELECT SUM(occurrence_count - 1) AS extra_duplicate_rows
FROM (
SELECT COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS duplicate_counts;For example, if one email appears three times and another appears twice, there are two duplicate groups, five rows in those groups, and three extra rows beyond one record per email.
Sometimes the requirement is to identify rows where all relevant business columns match. The primary key is usually different, so it is excluded from the comparison.
SELECT
customer_name,
email,
phone,
city,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY
customer_name,
email,
phone,
city
HAVING COUNT(*) > 1;This query identifies repeated combinations of customer_name, email, phone, and city. It does not compare customer_id because each row has a distinct primary key.
SELECT c.*
FROM customers AS c
JOIN (
SELECT
customer_name,
email,
phone,
city
FROM customers
GROUP BY
customer_name,
email,
phone,
city
HAVING COUNT(*) > 1
) AS d
ON c.customer_name <=> d.customer_name
AND c.email <=> d.email
AND c.phone <=> d.phone
AND c.city <=> d.city
ORDER BY c.customer_name, c.customer_id;MySQL's NULL-safe equality operator (<=>) treats two NULL values as equal for this comparison. This makes the join consistent with grouping when nullable columns are part of the duplicate definition.
Identical values in the selected columns do not always prove that two records should be merged. Two people may share a name, city, or phone number, and some systems intentionally preserve repeated event records. Always verify the business meaning of the fields.
NULL represents a missing or unknown value. It needs careful handling when finding duplicates because ordinary equality comparisons do not match NULL to NULL.
SELECT email, COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;This is useful when missing email values should not be treated as duplicates.
SELECT
COUNT(*) AS null_email_count
FROM customers
WHERE email IS NULL
HAVING COUNT(*) > 1;This query reports how many rows have a missing email when that count exceeds one. Whether these rows should be considered duplicate records depends on the business rule. Usually, multiple missing email values do not establish that the customers are the same person.
SELECT
c1.customer_id AS first_id,
c2.customer_id AS second_id,
c1.email
FROM customers AS c1
JOIN customers AS c2
ON c1.email <=> c2.email
AND c1.customer_id < c2.customer_id;This query treats two NULL email values as equal. Use it only if the intended duplicate definition explicitly treats missing email values as matching values. Otherwise, exclude NULL emails.
Two values may appear identical to a person but differ in how they are stored. For example, [email protected], [email protected], and [email protected] with a trailing space may not be treated identically in every database configuration.
SELECT
LOWER(TRIM(email)) AS normalized_email,
COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;TRIM() removes leading and trailing spaces, and LOWER() converts text to lowercase. This can help identify likely duplicates caused by inconsistent formatting.
Use normalization carefully. Whether uppercase and lowercase values are equivalent depends on the data rules and collation. For email addresses, domain names are case-insensitive, while the formal specification permits the local part to be case-sensitive. Many applications intentionally adopt case-insensitive email matching, but that should be an explicit application rule.
SELECT
REGEXP_REPLACE(phone, '[^0-9]', '') AS normalized_phone,
COUNT(*) AS occurrence_count
FROM customers
WHERE phone IS NOT NULL
GROUP BY REGEXP_REPLACE(phone, '[^0-9]', '')
HAVING COUNT(*) > 1;REGEXP_REPLACE() is available in MySQL 8.0 and later. This example removes non-digit characters before grouping phone numbers. For international numbers, a robust normalization process should also handle country codes and valid numbering rules rather than simply deleting punctuation.
Duplicate detection is particularly important in order and payment tables because repeated transactions can affect revenue, customer balances, and operational reports.
SELECT
external_reference,
COUNT(*) AS occurrence_count
FROM orders
WHERE external_reference IS NOT NULL
GROUP BY external_reference
HAVING COUNT(*) > 1;If the external reference is supposed to identify one order, repeated values should be reviewed. If a reference is shared across multiple valid order items or transactions, it may not be the correct duplicate key.
SELECT
payment_reference,
COUNT(*) AS occurrence_count
FROM payments
WHERE payment_reference IS NOT NULL
GROUP BY payment_reference
HAVING COUNT(*) > 1;A payment reference may be an appropriate unique key when the payment provider guarantees that it identifies one payment. Before removing records, check the payment provider's reference rules, transaction status, refunds, and retries.
SELECT
customer_id,
order_date,
order_total,
COUNT(*) AS occurrence_count
FROM orders
GROUP BY customer_id, order_date, order_total
HAVING COUNT(*) > 1;This identifies repeated combinations of customer, order date, and total. It only highlights potential duplicates because customers can legitimately place orders for the same amount at the same time.
Duplicate information can exist in separate tables when records are imported from different sources or maintained by different systems.
SELECT a.email
FROM customer_import_a AS a
JOIN customer_import_b AS b
ON a.email = b.email
WHERE a.email IS NOT NULL;This query identifies email values that appear in both tables. If either table contains repeated emails internally, the join can return the same email multiple times.
SELECT DISTINCT a.email
FROM customer_import_a AS a
JOIN customer_import_b AS b
ON a.email = b.email
WHERE a.email IS NOT NULL;DISTINCT removes repeated email values from the output. It does not change the underlying data in either source table.
SELECT
email,
COUNT(*) AS occurrence_count
FROM (
SELECT email
FROM customer_import_a
WHERE email IS NOT NULL
UNION ALL
SELECT email
FROM customer_import_b
WHERE email IS NOT NULL
) AS combined_customers
GROUP BY email
HAVING COUNT(*) > 1;UNION ALL retains all source rows, allowing the outer query to count how often each email appears across both tables. This is a useful approach when checking for overlap between imported datasets.
Finding duplicates and deleting them are different tasks. A duplicate-detection query should be run and reviewed before any destructive operation. A row that looks duplicated may contain important history or represent a legitimate business event.
SELECT c.*
FROM customers AS c
JOIN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS d
ON c.email = d.email
ORDER BY c.email, c.customer_id;This previews all records associated with duplicate non-NULL emails. Investigate the results before deciding which records, if any, should be removed or merged.
In MySQL 8.0 and later, a common approach is to number records within each duplicate group and mark rows after the first as candidates for review.
WITH ranked_customers 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 *
FROM ranked_customers
WHERE row_num > 1;This identifies extra rows according to ascending customer_id. The smallest ID is selected as the first row only because of the specified ordering; it is not automatically the most accurate or valuable record.
Finding duplicates helps identify existing data-quality issues. Preventing future duplicates requires suitable database constraints and reliable application behavior.
If email addresses must be unique, a unique constraint can prevent duplicate non-NULL email values.
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);Important: MySQL unique indexes permit multiple NULL values. Existing duplicate non-NULL emails must be resolved before adding this constraint. If email is mandatory, consider whether the column should also be NOT NULL after existing data has been validated.
Sometimes uniqueness applies to a combination of fields rather than one column.
ALTER TABLE products
ADD CONSTRAINT uq_product_code
UNIQUE (manufacturer_code, product_code);This prevents repeated combinations of manufacturer_code and product_code, provided the column definitions and NULL behavior match the intended business rule.
A primary key ensures that each table row has a unique identifier. However, a primary key alone does not prevent two rows from representing the same customer, product, or transaction if they receive different IDs.
Use primary keys together with suitable business-key constraints when the business requires uniqueness for particular values.
Applications may retry requests when network failures or timeouts occur. An idempotency key or unique external transaction reference can help ensure that a retry does not create another logical transaction.
The design must define how an idempotency key is generated, how long it remains valid, and what happens when a repeated request carries different data.
If duplicate checks frequently group or filter by email, an index on email may help certain queries. The benefit depends on table size, data distribution, and the query plan.
CREATE INDEX idx_customers_email
ON customers (email);If email must be unique, a UNIQUE constraint may provide both the integrity rule and an index. Do not add a separate index without checking whether an appropriate index already exists.
EXPLAIN
SELECT
email,
COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;EXPLAIN helps inspect the query execution plan, including the chosen access method and estimated rows. Test with representative data because index usefulness depends on the actual workload.
When identifying duplicate values, select only the columns required for grouping and counting. Retrieve complete rows in a second step if the investigation requires them.
If the business requirement concerns a specific date range or status, apply appropriate WHERE conditions to limit the records being examined. Be careful: filtering changes the definition of the duplicate search. A query that checks only recent records may miss duplicates elsewhere in the table.
GROUP BY and HAVING are usually straightforward for finding duplicate groups. Self-joins are useful for matching pairs, and window functions are useful for ranking individual records within duplicate groups. Compare execution plans when working with large datasets.
| Mistake | Why It Is a Problem | Recommended Solution |
|---|---|---|
| Grouping by the primary key | Unique IDs normally make every group contain one row | Group by the intended duplicate key. |
| Using WHERE COUNT(*) > 1 | Aggregate functions cannot normally be filtered in WHERE | Use HAVING COUNT(*) > 1. |
| Checking only customer names | Different people can share a name | Use an appropriate business key or multiple fields. |
| Ignoring NULL values | Missing values may be counted or matched differently than expected | Define how NULL should be treated. |
| Using SELECT DISTINCT to find duplicates | DISTINCT removes repeated result rows instead of reporting their counts | Use GROUP BY and HAVING to identify duplicates. |
| Deleting records immediately | Valid records may be lost | Preview, validate, back up, and clean up carefully. |
| Assuming similar names mean the same person | Names are not necessarily unique identifiers | Review additional attributes and business rules. |
| Ignoring formatting differences | Values may look the same but compare differently | Apply documented normalization rules. |
| Adding a unique constraint without checking existing data | The schema change may fail because duplicates already exist | Find and resolve duplicates first. |
An e-commerce company has imported customer data from multiple sources. The customer table may contain repeated emails, repeated phone numbers, and records with identical customer details. The goal is to identify potential duplicates without deleting legitimate records.
SELECT
email,
COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;SELECT c.*
FROM customers AS c
JOIN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS d
ON c.email = d.email
ORDER BY c.email, c.customer_id;SELECT
customer_name,
email,
phone,
city,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY customer_name, email, phone, city
HAVING COUNT(*) > 1;WITH ranked_customers AS (
SELECT
customer_id,
customer_name,
email,
phone,
city,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY customer_id
) AS row_num
FROM customers
WHERE email IS NOT NULL
)
SELECT *
FROM ranked_customers
WHERE row_num > 1
ORDER BY email, row_num;Answer: A duplicate record is a row that repeats another row according to a defined set of columns or a business rule. It does not necessarily need to match every column in the table.
Answer: Use GROUP BY to group the relevant values and HAVING COUNT(*) > 1 to return groups that contain multiple rows.
Answer: WHERE filters individual rows before grouping. HAVING filters groups after aggregation and can evaluate aggregate expressions such as COUNT(*).
Answer: Group by email and use HAVING COUNT(*) > 1. Exclude NULL emails if missing values should not count as duplicate email addresses.
Answer: Identify duplicate keys in a subquery and join those keys back to the original table, or use a suitable IN or EXISTS query.
Answer: A self-join joins a table to itself. It can compare records and return pairs that share specified values.
Answer: It numbers rows within each duplicate group. Rows with a number greater than one can be identified as additional records according to the selected ordering.
Answer: No. A primary key prevents repeated primary-key values, but two rows can still represent the same customer or product while having different IDs.
Answer: Use suitable UNIQUE constraints, validate incoming data, implement idempotent application operations, and apply consistent import procedures.
Answer: Confirm the duplicate rule, preview affected rows, back up the data, inspect dependencies, select the correct record to retain, test the cleanup, and verify the outcome.
Answer: GROUP BY groups NULL values together. Ordinary equality joins do not match NULL to NULL, so a NULL-safe comparison may be required when matching grouped records back to the original table.
Answer: Group by the duplicate key, calculate COUNT(*) for each group, filter groups with more than one row, and sum COUNT(*) minus one for each group.
Answer: Use a join on the duplicate key, or combine the key values with UNION ALL and group the combined rows to find repeated values across both sources.
Answer: DISTINCT removes duplicate rows from a query result. It does not show how many times a value occurred, so GROUP BY and HAVING are usually better for identifying duplicate groups.
Answer: Not automatically. Different people can share a name. Use a business key and additional attributes to decide whether the records represent the same entity.
Create a students table with student_id, student_name, email, and city. Insert repeated student names and write a query to find names that occur more than once.
Write a query to identify email addresses appearing in multiple customer records. Display the email and occurrence count.
Find records that share the same product_name and manufacturer_code. Explain why checking the name alone might produce false positives.
Use a subquery and join to return every customer record associated with a duplicate phone number.
Write a self-join query that returns duplicate customer email pairs without matching a record to itself or returning both pair directions.
Use ROW_NUMBER() to number rows sharing an email address. Display only records whose row number is greater than one.
Write a query that counts how many extra customer rows exist beyond one record per email group.
After reviewing the data, write an ALTER TABLE statement to add a unique constraint to an appropriate business key. Explain what must be done if duplicate values already exist.
Finding duplicate records is an important part of maintaining data quality in MySQL databases. Duplicate values can affect reporting, customer records, transactions, and business decisions. SQL provides multiple techniques for identifying them, including GROUP BY with HAVING, self-joins, subqueries, EXISTS, and window functions.
The correct technique depends on whether you need duplicate values, duplicate groups, complete matching records, or individual rows to review. Equally important is defining what makes two records duplicates. A repeated name does not necessarily identify the same person, and repeated transaction details may represent legitimate activity.
For long-term data quality, use a combination of duplicate detection, careful data cleanup, appropriate unique constraints, application validation, and reliable import processes. Always review the affected records and preserve important relationships and history before making destructive changes.
Continue Learning: Explore the MySQL Training Course to improve your SQL querying and database-management skills, or register for a course demo to learn more about the training.
