Home

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

 

Employees Table

And the following table of contractors:

Contractors Table

 

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:

	  Query Example
 
 
 

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 Pn of the first table uses strings, the corresponding column in position Pn of the other table must also use characters; if the column in position Pn+1 of the first table uses natural numbers, the corresponding column in position Pn+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

 
 
   
 

Home Copyright © 2011 FunctionX, Inc. Home