Union and union all operator

Working on databases requires multiple tasks and functions to be performed for which different SQL operators are used. One such task is to combine data from different sources such as a table of data.

To combine rows from two or more tables into a single separate result set, SQL provides Union and UnionAll operator. It is somewhat like Join command but while using UNION operator the selected must be of the same data type.

• Some rules for performing union on data are:

1. The number of columns in all queries must be equal.
2. The corresponding columns in the query must have the same data types.
3. The column names of the combined result set are determined by the column name mentioned in the first query.

Union Operator in SQL

union operator is used to combining data from different sources, say tables, into one dataset result. Union operator removes all the duplicate records from the result set. It means that all the entries present in the result dataset will be distinct values.

Syntax:

select column1, column2,… from table1 UNION select column1, column2,… from table2

 For example: consider we have two tables named manager and staff which hold the details of the manager and staff of a particular organization.

Table 1-Manager

Table 2 – Staff

Applying union operation on the two tables :

select name from manager union select name from staff

output:

EXPLANATION:  As we can see the records have been combined with the assurance that no duplicate records will be stored in the result dataset table.

UnionAll Operator in SQL

union all operator is used to combining data from different sources, say tables, into one dataset result. UnionAll operator does not remove any duplicate records from the result set. It means that all the entries present in the result dataset may not be distinct values.

Syntax:

select column1, column2,… from table1 UNION ALL select column1, column2,… from table2

For example: Let us apply the UnionAll operator on the same above mentioned tables.

select name from manager union all select name from staff

output

EXPLANATION:

As we can see the records have been combined with duplicate records also being stored in the result dataset table.