Appending Tables with UNION
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 SELECT col [, col ...] | * FROM <tableref> [, <tableref> ...]
For example, three tables,
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—
UNIONcan return all rows for each table by substituting an asterisk (*) for specific column names in the
SELECTclauses of the