Union

CIS4365: Database Applications
Fall, 2017

Union

Understanding the concept:

Conceptually, a relational union is the concatenation of two recordsets. It's more or less the relational version of addition. The result of the union of recordset A with recordset B is the same as actually adding all the records in A to B.

As an example, say you need a list of all the names and addresses known to the database for a mass mailing. The Northwind database Customers and Employees recordsets both have addresses and so can easily be combined in a union operation. In this case, we'd use the UNION statement, as follows:

SELECT CompanyName AS Name, Address, City, PostalCode

FROM Customers

UNION SELECT [FirstName] & " " & [LastName] AS Name,

    Address, City, PostalCode

FROM Employees

ORDER BY name;

Note that the CompanyName field is renamed "Name" and the FirstName and LastName fields from the Employees table are concatenated. The resulting field is also "Name." The union query doesn't require that the fields in the <fieldList> of each SELECT statement have the same name, but there must be the same number of them and they must have the same (or compatible) types.