SQL SERVER – Remove Duplicate Rows Using UNION Operator

In my earlier post on SQL SERVER – Delete Duplicate Rows, I showed you a method of removing duplicate rows with the help of ROW_NUMBER() function and COMMON TABLE EXPRESSION. In this post, I am going to show you a tricky method of removing duplicate rows using traditional UNION operator.

As you know UNION is used to combine the multiple result sets into a single result set by removing duplicates.

Let us create the same dataset created in the earlier post.

USE TEMPDB
GO
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The following query will return all seven rows from the table

SELECT col1,col2
FROM DuplicateRcordTable

Now to remove the duplicate rows, use the same table and UNION it with an empty result set returned by the same table as shown below

SELECT col1,col2
FROM DuplicateRcordTable
UNION 
SELECT col1,col2
FROM DuplicateRcordTable WHERE 1=0

The result is shown below

The second part of the UNION query uses the dummy WHERE condition 1=0 to return the empty result set. This empty result set is combined with the first part of the UNION part. The UNION operator simply returns the resultset by removing the duplicates.

Let me know if you have any other trick to remove duplicate rows. I will be happy to publish it on the blog with due credit to you. Please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)