SQL: Union Clause

The Union clause allows you to combine the result sets of 2 or more individual queries. Union differs from subqueries in that it is made up of queries that are independent from each other. UNION combines the output of these individual Selects and lists them as a part of a single output table.

The syntax for a Union clause is as follows:

SELECT column1, ... column_n FROM derived_table UNION [ALL] SELECT column1, ... column_n FROM derived_table

Each SQL statement within the Union query must have the same number of fields in the result sets with similar data types. Also, when using Union, only distinct values are selected (similar to Select Distinct).

Example #1

SELECT Item FROM Antiques UNION SELECT ItemDesired FROM Orders

In this example, if an item appeared in both the Antiques and Orders table, it would appear once in your result set.

Example #2

SELECT FirstName, LastName FROM EmployeeAddressTable WHERE City IN ('Losantiville','Paris') UNION SELECT OwnerFirstName,OwnerLastName FROM AntiqueOwners WHERE City IN ('Losantiville','Paris','San Diego')

This example takes certain selected fields from the 'AntiqueOwners' table and joins them onto the end of the same number of selected fields from the 'EmployeeAddressTable' table. The query will return all employees living in Losantiville or Paris and all AntiqueOwners living in Losantiville, Paris or San Diego.