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 sets in SQL server using UNION and EXCEPT Operator. In SQL server there are several methods are available for removing duplicate/dummy records/rows such as with the help of the 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 sets 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 merge/combine result sets. EXCEPT operator 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 purposes.
First of all, we will create/declare a temporary table and also we will insert some dummy records into 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 |
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 the following query shown 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 the 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 the UNION operator.
SELECT * FROM @Student EXCEPT SELECT * FROM @Student WHERE 1=0
If you analyzed the 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 |
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.
