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
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

FROM Antiques
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.



Leave a Reply

Your email address will not be published.