Comprehensive Techniques for Detecting and Handling Duplicate Records Based on Multiple Fields in SQL

Fundamental Principles of Duplicate Record Detection

In database management, detecting duplicate records is a crucial aspect of data quality management. Identifying duplicates based on combinations of multiple fields requires understanding the core concepts of SQL grouping and aggregation. When we need to detect duplicates in field combinations (field1, field2, field3), the fundamental approach involves grouping and statistical analysis of these fields.

Basic Duplicate Detection Methods

Using GROUP BY clauses combined with HAVING conditions can effectively identify duplicate record combinations. Here is a standard implementation example:

SELECT field1, field2, field3, COUNT(*) as duplicate_count
FROM table_name
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1

This query returns all record groups that have duplicates in the specified field combinations, while also displaying the occurrence count for each combination. The COUNT(*) function calculates the number of records in each group, and the HAVING clause filters out groups with counts greater than 1.

Selecting All Duplicate Records Except the First One

In practical applications, further processing of these duplicate records is often necessary, such as keeping the first record while deleting other duplicates. This requires more sophisticated technical solutions:

WITH numbered_records AS (
    SELECT 
        field1, field2, field3,
        ROW_NUMBER() OVER (
            PARTITION BY field1, field2, field3 
            ORDER BY (SELECT NULL)
        ) as row_num
    FROM table_name
)
SELECT field1, field2, field3
FROM numbered_records
WHERE row_num > 1

This query uses the window function ROW_NUMBER() to assign sequence numbers to records within each duplicate group, then selects records with sequence numbers greater than 1, effectively choosing all duplicate records except the first one.

Duplicate Detection with Specific Field Variations

In certain scenarios, we need to detect cases where field combinations are identical but a specific field differs. For example, as mentioned in reference article 1, identifying records where 20 fields are the same but the PIN field differs:

SELECT {20 fields}, COUNT(DISTINCT PIN) as distinct_pins
FROM table_name
GROUP BY {20 fields}
HAVING COUNT(DISTINCT PIN) > 1

This method identifies record groups where the main field combinations are identical but specific fields show variations.

Implementation Differences Across Database Platforms

SQL Server Implementation

In SQL Server, CTEs (Common Table Expressions) and window functions can be utilized:

WITH DuplicateGroups AS (
    SELECT 
        field1, field2, field3,
        ROW_NUMBER() OVER (
            PARTITION BY field1, field2, field3 
            ORDER BY field1
        ) as rn
    FROM your_table
)
SELECT field1, field2, field3
FROM DuplicateGroups
WHERE rn > 1

MySQL Implementation

MySQL version 8.0 and above supports window functions, with implementation similar to SQL Server. For older versions, variables can simulate row numbers:

SELECT t.field1, t.field2, t.field3
FROM (
    SELECT 
        field1, field2, field3,
        @row_number := CASE 
            WHEN @current_group = CONCAT(field1, field2, field3) 
            THEN @row_number + 1 
            ELSE 1 
        END as rn,
        @current_group := CONCAT(field1, field2, field3)
    FROM your_table
    CROSS JOIN (SELECT @row_number := 0, @current_group := '') as vars
    ORDER BY field1, field2, field3
) t
WHERE t.rn > 1

Performance Optimization Considerations

When dealing with large-scale data, performance optimization becomes critical:

First, creating composite indexes on the fields involved in grouping can significantly improve query performance:

CREATE INDEX idx_duplicate_check ON table_name (field1, field2, field3)

Second, for extremely large datasets, consider batch processing:

-- Using OFFSET and LIMIT for paginated processing
SELECT field1, field2, field3, COUNT(*)
FROM table_name
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1
ORDER BY field1
OFFSET 0 ROWS
FETCH NEXT 1000 ROWS ONLY

Practical Application Cases

The case study from reference article 3 demonstrates detecting duplicates in part number and plant combinations within forecasting data tables. This scenario is common in ERP systems where a part number should theoretically correspond to only one plant, but duplicate assignments may occur in practice.

The solution requires first identifying part numbers with multiple plants, then listing the specific plant information:

WITH DuplicateParts AS (
    SELECT partnumber
    FROM forecasttable
    GROUP BY partnumber
    HAVING COUNT(DISTINCT plant) > 1
)
SELECT 
    ft.partnumber, 
    ft.plant,
    (SELECT COUNT(DISTINCT plant) 
     FROM forecasttable 
     WHERE partnumber = ft.partnumber) as plant_count
FROM forecasttable ft
INNER JOIN DuplicateParts dp ON ft.partnumber = dp.partnumber
ORDER BY ft.partnumber, ft.plant

Error Handling and Edge Cases

When processing duplicate records, various edge cases need consideration:

NULL value handling: NULL values are treated as identical in grouping operations, which may lead to unexpected duplicate detection results. If NULL values need differentiation, use the COALESCE function:

SELECT 
    COALESCE(field1, 'NULL') as field1,
    COALESCE(field2, 'NULL') as field2,
    COALESCE(field3, 'NULL') as field3,
    COUNT(*)
FROM table_name
GROUP BY 
    COALESCE(field1, 'NULL'),
    COALESCE(field2, 'NULL'),
    COALESCE(field3, 'NULL')
HAVING COUNT(*) > 1

Summary and Best Practices

Duplicate record detection based on multiple fields is a common requirement in database management. Through appropriate use of GROUP BY, HAVING clauses, and window functions, duplicate data can be efficiently identified and processed. In practical applications, suitable implementation solutions should be chosen based on specific data characteristics and business requirements, while paying attention to performance optimization and error handling.

It is recommended to validate query correctness in test environments before processing production data, and use transactions to ensure atomicity of data operations. For large-scale data cleansing tasks, adopting batch processing strategies is advised to avoid significant impact on system performance.