Union Operator

Union operator allows the user to combine the result sets from multiple queries into one.  To use this operator, certain rules have to be followed:

• If two result sets are being combined using union operator then both the result sets must contain same number of columns.
• The data type of the corresponding columns in both the result sets must be same. If not then it must be converted so that both have the same type.
• Final result set uses the column names from the first result set.
• Order by clause is used at the end of the last select statement.
•  Order by clause uses the column name(s) from the first select statement.
•  Each select statement should include its own filtering criterion.

e.g.

select customerID as cid from sales.customer

where customerID < 50

union

select employeeid as eid from humanresources.employee

where employeeID < 50

order by cid

“Union all” is used to return all rows including duplicate ones too.