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

table

Other table is tblEmployeeB

table

Apply union and union all commands on the tables, given above.
  1. SELECT Name,Address,salary FROM tblEmployeeA
  2. UNION
  3. SELECT Name,Address,salary FROM tblEmployeeB
table
  1. SELECT Name,Address,salary FROM tblEmployeeA
  2. UNION ALL
  3. SELECT Name,Address,salary FROM tblEmployeeB
table

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

  1. SELECT Name,Address,salary FROM tblEmployeeA
  2. UNION
  3. SELECT Name ,Address,salary FROM tblEmployeeB
Data type should be matched
  1. SELECT Name,Address,salary FROM tblEmployeeA
  2. UNION
  3. SELECT Address,Name,salary FROM tblEmployeeB
table

Order of column should be same and Data type
  1. SELECT Name,salary,Address FROM tblEmployeeA
  2. UNION
  3. 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
  1. select Name,Address,salary from tblIndEmployee
  2. UNION
  3. select Name,Address,salary from tblukEmployee
  4. order by name,salary
table