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
Customer Table Data
Union Example
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
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.