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. - Equal number of columns should be in the queries.
- Data type should be matched.
- Order of the column and data type should be same.
- 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