Appending Tables with UNION
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 theSELECT
clauses of theUNION
.