Microsoft Access Databases: Introduction to Unions

	Introduction to Unions

Introduction

A union is the operation that consists of combining the values from two tables. In the structured query language (SQL), the operation is performed using the UNION operator. Let’s consider the following table of employees:

Employees Table

And the following table of contractors:

Contractors Table

You may know already that you can create a query that includes records of both tables. Here is an example:

Contractors Table

The SQL code of this query is:

SELECT Employees.EmployeeNumber,
       Employees.FirstName, 
       Employees.LastName, 
       Employees.Title, 
       Contractors.ContractorCode, 
       Contractors.FName, 
       Contractors.LName, 
       Contractors.Position
FROM Employees, Contractors;

And the result is:

Creating a Union

In order to create a union of two tables, the attributes of the tables involved must follow two rules:

Both tables must have the same number of fields (columns)
The order of columns in one table must be compatible with the corresponding columns in the other table but the columns do not have to use the same names. That is, if the column in position P_n of the first table uses strings, the corresponding column in position P_n of the other table must also use characters; if the column in position P_n+1 of the first table uses natural numbers, the corresponding column in position P_n+1 of the other must also use a natural number, and so on

Obviously before performing a union on two tables, it must be verified that the tables have the same number of columns and the columns are compatible in each position. If this is established, the tables are said to be compatible.

A union is performed by creating a query that contains the records from the first table followed by the records of the second table, using the respective positions of their columns.

The basic formula to create a union is:

SELECT Fields FROM FirstTable
UNION
SELECT Fields FROM Contractors;

A union of the above tables is:

SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName,
       Employees.Title
FROM Employees
UNION
SELECT Contractors.ContractorCode,
       Contractors.FName,
       Contractors.LName,
       Contractors.Position
FROM Contractors;

A union of the above tables would produce:

A Union of Two Tables