SQL Server Union And Union All

If we want to combine two or more result sets into one result set, we use Union and unite all commands.
There are some differences between Union and Union all.

• Union All will not eliminate the duplicate records.
  Union removes the duplicate records.
  
  
• Union All does not use distinct sort, i.e. it is faster than union.
  Union uses a distinct sort. This is the reason it is slower than union all.

If the columns contains a null in a row, it also appears in union and the union all result set.

Let’s do an example,

We have table: tblEmployeeA



Other table is tblEmployeeB



Apply union and union all commands on the tables, given above.

SELECT Name,Address,salary FROM tblEmployeeA
UNION
SELECT Name,Address,salary FROM tblEmployeeB

SELECT Name,Address,salary FROM tblEmployeeA
UNION ALL
SELECT Name,Address,salary FROM tblEmployeeB

Some steps to be followed while writing union and union all query. 

1. Equal number of columns should be in the queries.
2. Data type should be matched.
3. Order of the column and data type should be same.
4. Order by clause needs to be there at the end of the query.

Equal number of column

SELECT Name,Address,salary FROM tblEmployeeA
UNION
SELECT Name ,Address,salary FROM tblEmployeeB

Data type should be matched

SELECT Name,Address,salary FROM tblEmployeeA
UNION
SELECT Address,Name,salary FROM tblEmployeeB

Order of column should be same and Data type

SELECT Name,salary,Address FROM tblEmployeeA
UNION
SELECT Name,Address,salary FROM tblEmployeeB

Conversion fails, when converting the varchar value 'India' to data type int.

Order by clause at the end of query

select Name,Address,salary from tblIndEmployee
UNION
select Name,Address,salary from tblukEmployee
order by name,salary