Appending Tables with UNION

From InterBase
Jump to: navigation, search

Go Up to Understanding Data Retrieval with SELECT


Sometimes two or more tables in a database are identically structured, or have columns that contain similar data. Where table structures overlap, information from those tables can be combined to produce a single results table that returns a projection for every qualifying row in both tables. The UNION clause retrieves all rows from each table, appends one table to the end of another, and eliminates duplicate rows.

Unions are commonly used to perform aggregate operations on tables.

The syntax for UNION is:

UNION SELECT col [, col ...] | * FROM <tableref> [, <tableref> ...]

For example, three tables, CITIES, COUNTRIES, and NATIONAL_PARKS, each contain the names of cities. Assuming triggers have not been created that ensure that a city entered in one table is also entered in the others to which it also applies, UNION can be used to retrieve the names of all cities that appear in any of these tables.

EXEC SQL
DECLARE ALLCITIES CURSOR FOR
SELECT CIT.CITY FROM CITIES CIT
UNION SELECT COU.CAPITAL FROM COUNTRIES COU
UNION SELECT N.PARKCITY FROM NATIONAL_PARKS N;
Tip: If two or more tables share entirely identical structures – similarly named columns, identical data types, and similar data values in each column—UNION can return all rows for each table by substituting an asterisk (*) for specific column names in the SELECT clauses of the UNION.