Union

The output from a series of SELECT statements may be joined together by the use of the UNION keyword. The syntax of its use is:

SELECT statement-l
UNION
SELECT statement-2 UNION
.
.
.
SELECT statement-n
[ORDER BY column position, ...];

Logically, this executes each SELECT separately and combines the result. In practice, the system will optimise the enquiry and will often be able to execute the statements i especially if the data comes from the same tables in both parts of the UNION.

The select-list from each SELECT statement must contain the same number of columns and the columns in the same position must be of compatible data types. For example SMALLINT in a certain position in one SELECT clause can be paired with an INT in another. Note: A NULL value may be used to match any data type, if there is no equivalent in a SELECT statement.

The column names or display labels are taken from the first SELECT statement only. If an ORDER BY clause is included in the query, it must follow the last SELECT statement and must be referred to by the number of the item selected, not its identifier.

For example, consider a company's database that contains a table called personnel another called customers. If the company wished to mail Christmas cards to all it of staff and their customers, names and addresses will have to be obtained from hot' If all the details are to be sorted by town and postcode, two methods may be used

Method 1 - Using a Temporary Table

SELECT c0004title, c0004initials, c0004surname, c0004street, c0004town, c0004county, c0004postcode
FROM t0004personnel
INTO TEMP xmas_list;

INSERT INTO xmas_list
SELECT c0001title, c0001initials, c0001surname, c0001street, c0001town, c0001county, c0001postcode
FROM c0001customer;

SELECT DISTINCT *
FROM xmas_list
ORDER BY c0004town, c0004postcode;

DROP TABLE xmas list;

Method 2-Using a UNION:

SELECT c0004title, c0004initials, c0004surname, c0004street, c0004town, c0004county, c0004postcode
FROM t0004personnel
UNION
SELECT c0001title, c0001initials, c0001surname, c0001street, c0001town, c0001county, c0001postcode
FROM t0001customer
ORDER BY 5, 7;

The second method is by far the faster of the two, primarily because it does not require the creation of the temporary table.

One place where UNION formulation of this query is vital is inside a report. The SELECT section of a report cannot use the INSERT statement, so there is no way of writing one report to do this job without using the UNION statement.

Note: Duplicate rows from a series of UNION'd SELECT statements are removed automatically. If the duplicates are required, use UNION ALL.