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.