In a previous blog post, we discussed a method for removing duplicate rows from a result set using the UNION operator. In this article, we will explore another similar method using the EXCEPT operator to find distinct results.
Let’s start by creating a sample dataset:
USE TEMPDB
GO
CREATE TABLE DuplicateRecordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRecordTable
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
GOThe above code creates a table called “DuplicateRecordTable” and inserts some duplicate rows into it.
To remove the duplicate rows, we can use the EXCEPT operator with an empty result set returned by the same table:
SELECT Col1, Col2
FROM DuplicateRecordTable
EXCEPT
SELECT Col1, Col2
FROM DuplicateRecordTable WHERE 1=0The result of this query will be the distinct rows from the “DuplicateRecordTable”. The second part of the EXCEPT query uses the dummy WHERE condition “1=0” to return an empty result set. This empty result set is then combined with the first part of the EXCEPT query, resulting in the distinct rows.
Have you ever used this method to find distinct records? Let me know in the comments below. If you have any other tricks for removing duplicate rows, I would be happy to publish them on the blog with due credit to you.
For more details about the EXCEPT operator, you can refer to the following articles:
- 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