Difference Between Union And Union All In SQL Server

Union Defination
 
There are a couple of operators present in Sql Sever.  Union is one of them. It is used to combine the result set of two or more select statements.There are a couple of points which are important to keep in mind while using Union. The Columns must have similar data types.Every select statement within Union must have the same number of columns. We cannot allow duplicate values in Union. It removes duplicate records, hence it is slower as compared to Union All. Sometimes we select data from multiple table and combile result sets of all select statements.  That's  the purpose of using  Union Operator.
 
Union Syntax
 
select Column_Name from Table1 Union select Column_Name from Table2
 
Employee Table Data
 
 Difference Between Union And Union All In SQL Server
 
Customer Table Data
 
Difference Between Union And Union All In SQL Server 
 
Union Example
 
 Difference Between Union And Union All In SQL Server
 
As you can see the above result excludes duplicate records. It removes all duplicate records.
 
Union All Defination
 
Union All combines results gernerated by multiple sql queries or tables and it returns results into a single result set. Like in Union we cannot allow duplicate values but in Union All we can allow duplicate values. It does not remove duplicate records hence it is faster than Union.
 
Operator.Performance wise Union All is better than Union.
 
Union All Syntax
 
Select Column_Name from Table1 UnionAll select Column_Name from Table2
 
Union All Example
 
Difference Between Union And Union All In SQL Server 
 
In the above output you can see it does not remove duplicate records.
 
Difference between UNION vs UNION ALL
  • Union Operator removes duplicate records.
  • Union All does not remove duplicate records.
  • Union All operator is faster as compared to Union because it does not remove duplicate records.
  • If there are no duplicate values present then that time Union All is better option to use because it works with all data type columns.
  • Union works with the same data types.
  • Union and Union operator work on all Sql Versions.
  • Union and Union All both are the SET operators.
  • Basically Union All is same as Union command except  that Union All selects all values and Union removes the duplicate values.
I hope you understand these Operators clearly. In the next article we will see other important operators in Sql Server.
Next Recommended Reading SQL Server - Union Vs. Union All