SQL Server Remove Duplicate Records From Table Using UNION And EXCEPT Operator

watch_later Wednesday, December 26, 2018

Introduction


In this article, I am going to explain how to remove duplicate records from the table and get only unique/distinct Result Sets in SQL server and also show you how you can merge result set in SQL server using UNION and EXCEPT Operator. In SQL server there is several methods are available for removing duplicate/dummy records/rows such as with the help of ROW_NUMBER() function and COMMON TABLE EXPRESSION and etc.

What is UNION in SQLServer?


In SQL Server UNION operator is used to merge/combine two or more result set into a single result set. UNION operator removes the duplicate rows between the various result sets.

Note: Each and Every result sets within the UNION operator must have the same number of columns with similar data types in the result sets.

What is EXCEPT in SQL Server?


In SQL Server EXCEPT operator is used to merging/combine result sets. EXCEPT operator is returns rows from the first result sets that are not returned by the second result sets, in short EXCEPT returns only rows, which are not available in the second result sets.

In my previous articles, I showed how to create a stored procedure and how to Split Comma Separated String in SQL Server as well as create and execute parameterized stored procedure and also showed you how to create a dynamic stored procedure in SQL server that you might like to read.

So, Let's Start with an example for demonstration purpose.

First of all, we will create/declare a temporary table and also we will insert some dummy records in the table.
/* Declate temporary table */
DECLARE @Student AS TABLE
(
Id INT,
Name NVARCHAR(50),
Branch NVARCHAR(50),
University NVARCHAR(50)
)
 
/* Insert records in created temporary table */
 
INSERT INTO @Student
         ( Id, Name, Branch, University )
VALUES  ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 2, N'Hiren Dobariya', N'B.Tech', N'RK University, Rajkot'),
         ( 5, N'Krupa Patel', N'PHD', N'MS University, Baroda'),
         ( 2, N'Hiren Dobariya', N'B.Tech', N'RK University, Rajkot'),
         ( 3, N'Vivek Ghadiya', N'B.Tech', N'RK University, Rajkot'),
         ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 4, N'Shreya Patel', N'M.Tech', N'Nirma University, Ahmedabad'),
         ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 3, N'Vivek Ghadiya', N'B.Tech', N'RK University, Rajkot'),
         ( 6, N'Nikunj Ladani', N'B.E', N'Maliba Engineering College,  
Bardoli')
Now, if you execute the created table using a select statement then this table will return 10 records/rows with duplicate records/rows.
SQL Server Remove Duplicate Records
SQL Server Remove Duplicate Records

Now, to remove/delete duplicate rows/records from the result set, use the same table and UNION it with an empty result set returned by the same table and for that write following query is shown as below.
SELECT * FROM  @Student
UNION
SELECT * FROM  @Student WHERE 1=0
If you analyzed then the second part of the above query this statement uses the WHERE condition 1=0 to return the empty result set and this result set is merged with the first part of the UNION operator and in SQL server UNION operator simply returns the distinct/unique result set and will remove the duplicate records from result sets.

In SQL Server you also can remove/delete duplicate rows/records from the result set, by using EXCEPT operator same as UNION operator.
SELECT * FROM  @Student
EXCEPT
SELECT * FROM  @Student WHERE 1=0
If you analyzed above SQL statement the second part of the above query uses where condition something like 1=0 to return the empty result set and this result set will merge/combine with the first part of EXCEPT operator and simply, returns the result set and removing the records/rows of the second result set that is empty and will return distinct records/rows.

Output

SQL Server Remove Duplicate Records From Table Using UNION And EXCEPT Operator
SQL Server Remove Duplicate Records From Table

Summary


This article explains how to remove duplicate records from the table and get only unique/distinct Result Sets in SQL server using UNION and EXCEPT Operator.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon