SQL Query to Get Distinct Records Without Using Distinct Keyword

Retrieving distinct records is a common task when working with databases. While the DISTINCT clause is the standard approach to fetch unique rows, there are scenarios where you may need to achieve the same result without using it.

In this article, we explain various alternative methods to retrieve distinct records from a Microsoft SQL Server database table. These methods include using GROUP BY, UNION, INTERSECT, and CTE with the ROW_NUMBER() function, accompanied by detailed explanations and practical examples.

Example of Finding Distinct Records Without Using Distinct Keyword

Let’s use the following dup_table in the geeks database to demonstrate the various methods for retrieving distinct records. This table contains duplicate rows that we aim to filter out using alternative techniques, ensuring a clear and unique dataset. These methods are practical and flexible for handling deduplication in real-world scenarios.

Method 1: Using GROUP BY Clause

The GROUP BY clause groups rows with the same values into aggregated rows, effectively removing duplicates when no aggregation function is used.

Query:

SELECT dup_id, dup_name FROM dup_table
GROUP BY dup_id, dup_name;

Output

Method 2: Using UNION Operator

The UNION operator combines the result sets of two queries and removes duplicates. It ensures that the final output contains unique rows from both result sets, making it a powerful tool for deduplication and merging datasets.

Query:

SELECT dup_id, dup_name FROM dup_table
UNION
SELECT dup_id, dup_name FROM dup_table;

Output

Method 3: Using INTERSECT Operator

The INTERSECT operator returns only the rows that appear in both result sets, ensuring distinct records. It is particularly useful when comparing two datasets or when deduplication needs to be achieved based on overlapping values.

Query:

SELECT dup_id, dup_name FROM dup_table
INTERSECT
SELECT dup_id, dup_name FROM dup_table;

Output

Method 4: Using CTE and ROW_NUMBER() Function

A Common Table Expression (CTE) with the ROW_NUMBER() function can be used to assign a unique row number to each duplicate group. Filtering for rows with ROW_NUMBER = 1 eliminates duplicates.

Query:

WITH cte (dup_id, dup_name, dup_count)
AS
(SELECT dup_id, dup_name,
 row_number() over (partition BY dup_id,
 dup_name ORDER BY dup_id) AS dup_count
 FROM dup_table)
SELECT * FROM cte WHERE dup_count = 1;

Output

Conclusion

Retrieving distinct records without using the DISTINCT clause is both possible and practical in SQL. Techniques like GROUP BY, UNION, INTERSECT, and CTE with ROW_NUMBER() offer flexibility and cater to different use cases. By understanding these methods, we can optimise queries, handle database limitations, and perform advanced data manipulation tasks. Use the method that best suits our specific requirements and database environment to ensure accurate and efficient results.