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.

SQL Server - Find Distinct Result Sets Using EXCEPT Operator distinct

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]

SQL Server - Find Distinct Result Sets Using EXCEPT Operator dup_result11

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

SQL Server - Find Distinct Result Sets Using EXCEPT Operator dup_result22

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)

Quest

SQL Operator, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Performing IF…THEN In T-SQL With IIF Expression
Next Post
SQL SERVER – How to Count a Particular Value Across All Columns?

Related Posts

14 Comments. Leave new

  • SELECT col1,col2
    FROM DuplicateRcordTable
    GROUP BY col1, col2

    Reply
  • Why not use the DISTINCT keyword? It’s simpler and has well understood behaviour. For example:

    select distinct * from DuplicateRcordTable

    Reply
  • Nick A Stonebraker
    December 27, 2018 12:38 am

    More info please. Performance vs distinct vs Union vs other. SQL versions where Except is available. Where does it make sense to use this?

    Reply
  • Andrew Houghton
    December 27, 2018 2:47 am

    How about the simpler SELECT DISTINCT col1, col2 FROM DuplicateRcordTable ? Also in this instance you could use a standard grouping query

    Reply
  • Are there any performance advantages in using the EXCEPT method?

    Reply
  • 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

    Reply
  • 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

    Reply
  • ;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

    Reply
  • Mohammed Ibrahim Abd Elhalim
    June 17, 2019 4:50 pm

    SELECT col1,col2
    FROM DuplicateRcordTable
    Union
    SELECT top (1) col1,col2
    FROM DuplicateRcordTable

    Reply
    • 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

      Reply

Leave a Reply