SQL Distinct Queries on Multiple Columns and Performance Optimization

Fundamental Concepts of Multi-Column Distinct Queries

In database operations, it is often necessary to identify unique records based on combinations of multiple columns. Traditionally, developers use the DISTINCT keyword, but a more powerful approach involves the GROUP BY clause. Semantically, SELECT DISTINCT a,b,c FROM t is roughly equivalent to SELECT a,b,c FROM t GROUP BY a,b,c. The latter not only achieves the same deduplication effect but also provides extensibility for further aggregation operations.

Analysis of Practical Application Scenarios

Consider a sales data table with fields such as id, saleprice, saledate, and status. The business requirement is to update the status to 'ACTIVE' for sales records that are unique based on the combination of date and price. This means identifying all records where the (saleprice, saledate) combination appears only once.

Core Implementation Methods

The most straightforward implementation uses a subquery combined with GROUP BY and HAVING clauses:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
)

This query first identifies all (saleprice, saledate) combinations that occur only once in a subquery, then associates them back to the original table via a JOIN operation, and finally updates the status of the corresponding records.

Performance Optimization Strategies

Although the above method is functionally correct, it may encounter performance bottlenecks with large datasets. A more efficient implementation uses an EXISTS semi-join:

UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
   SELECT FROM sales s1
   WHERE s.saleprice = s1.saleprice
   AND s.saledate = s1.saledate
   AND s.id <> s1.id
)
AND s.status IS DISTINCT FROM 'ACTIVE';

The advantage of this approach is that the query engine can stop scanning as soon as it finds the first duplicate record, significantly improving performance for tables with many duplicates. Additionally, the IS DISTINCT FROM condition avoids unnecessary empty updates.

Considerations for NULL Value Handling

In multi-column distinct queries, handling NULL values requires special attention. According to the SQL standard, NULL values are not considered equal, so two identical combinations containing NULLs might be treated as different records. This behavior differs from GROUP BY and DISTINCT, which typically treat NULLs as equal. In practical applications, the appropriate comparison method should be selected based on business requirements.

Practical Advice and Best Practices

For production environments, it is recommended to: ensure tables have appropriate primary keys or unique indexes; validate query results with SELECT before updating; consider using transactions to ensure data consistency; and establish proper indexes to enhance performance for frequently executed queries. Through reasonable query design and optimization, large-scale data deduplication needs can be effectively addressed.