The UNION clause is used to combine two separate and individual result data sets to produce one single result data set. In order for two result data sets to be combined with a UNION clause, they both must have the same number of columns. In addition, each of those columns must have the exact same data types assigned to them.
For example, suppose a company keeps employee information in a special table that is archived at the end of each year. Just before the table is archived, a new table is created and the records for all employees who are still employed by the company are copied to it. Through out the year, as new employees are hired they are added to the new table. To obtain a list of all employees that were employed by the company in 1996 and 1997, each archived table would have to be queried, and the results would have to be combined. This operation could be performed by using the UNION clause in an SQL statement. For example:
SELECT LastName, EmpID
FROM Employees96
UNION SELECT LastName, EmpID
FROM Employees97
When executed, this SQL statement would produce a result data set containing the last name and the employee ID of all employees that worked for the company in 1996 and 1997.
By default, when two result data sets are combined, all duplicate rows are removed. However, all rows of data in each result data set (including duplicates) will be copied to the combined result data if the keyword ALL follows the UNION clause.