SQL Server – Find Distinct Result Sets Using EXCEPT Operator

In my earlier post on “SQL SERVER – Remove Duplicate Rows Using UNION Operator“, I showed a different method of removing duplicate rows from the result set using a UNION operator. Here is another similar method using EXCEPT operator to find distinct results.

Let us create the same dataset used in the above blog 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

[sqlSELECT col1,col2
FROM DuplicateRcordTable[/sql]

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

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

The result is shown below

The second part of the EXCEPT 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 EXCEPT part. The EXCEPT operators simply returns the resultset by removing the records of the second result set (which is empty in this case) and return the distinct result set.

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.

Also, find more details about EXCEPT operator
SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle 
SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle 

Have you ever used this method to find distinct records?

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