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,col2FROM 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)
14 Comments. Leave new
SELECT col1,col2
FROM DuplicateRcordTable
GROUP BY col1, col2
Great! Love it!
Why not use the DISTINCT keyword? It’s simpler and has well understood behaviour. For example:
select distinct * from DuplicateRcordTable
just another method.
More info please. Performance vs distinct vs Union vs other. SQL versions where Except is available. Where does it make sense to use this?
i always trust query plan cost.
How about the simpler SELECT DISTINCT col1, col2 FROM DuplicateRcordTable ? Also in this instance you could use a standard grouping query
Are there any performance advantages in using the EXCEPT method?
check query plan. cost is same for both. just another method.
Hi Calvin,
There are no performance advantages of using EXCEPT. It implicitly does “SELECT DISTINCT” on the result set. I think Pinal wants to convey that we can use EXCEPT to return DISTINCT results (new learning), but there is no practical usage and also there is no performance improvement.
Thanks,
Srini
Very interesting…
In essence, a ‘side-effect’ of using EXCEPT is to remove duplicates (unfortunately, the article doesn’t express this clearly enough). I think it would more understandable to most developers, if EXCEPT was replaced with UNION – since most developers know that UNION removes duplicates.
Thanks
Ian
;With Temp
AS
(
SELECT col1,col2,
ROW_NUMBER() OVER (PARTITION By col1,col2 ORDER BY col1,col2 ) AS RowNo
FROM DuplicateRcordTable
)
SELECT report_city, report_state FROM Temp WHERE RowNo = 1
SELECT col1,col2
FROM DuplicateRcordTable
Union
SELECT top (1) col1,col2
FROM DuplicateRcordTable
You do not need top 1. Just use where 1=0
SELECT col1,col2
FROM DuplicateRcordTable
Union
SELECT col1,col2
FROM DuplicateRcordTable where 1=0