|
<< Click to Display Table of Contents >> UNION join |
  
|
|
<< Click to Display Table of Contents >> UNION join |
|
Concatenates the rows of one table to the end of another table.
SELECT col_1 [, col_2, ... col_n]
FROM table_reference
UNION [ALL]
SELECT col_1 [, col_2, ... col_n]
FROM table_reference
Use the UNION join to add the rows of one table to the end of another similarly structured SELECT query result sets. The SELECT statement for the source and joining tables must include the same number of columns for them to be UNION compatible. The table structures themselve need not be the same as long as those column included in the SELECT statements are.
SELECT CustNo, Company
FROM Customers
UNION
SELECT CustNo, Company
FROM Old_Customers
Matching data types for a column is not always mandatory for data retrieved by the UNION across the multiple tables. If there is a data type difference between two tables for a given column, an error occurs if the same column from the second (or subsequent) table would lose data. For example, if the first table’s column is of type DATE and the second table’s of type TIMESTAMP, part of the TIMESTAMP value would be lost when put into a lesser DATE type column. A “Type mismatch in expression” error is generated for these situations. In general, when there are column differences between the tables, use the CAST function to convert the columns to a compatible type.
SELECT S.ID, CAST(S.Date_Field AS TIMESTAMP)
FROM Source S
UNION ALL
SELECT J.ID, J.Timestamp_Field
FROM Joiner J
Matching names is not mandatory for result set columns retrieved by the UNION across the multiple tables. Column name differences between the multiple source tables are automatically handled. If the first column of two tables has a different name, the first column in the UNION result set will use that from the first SELECT statement.
By default, non-distinct rows are aggregated into single rows in a UNION join. Use ALL to retain non-distinct rows.
To join two tables with UNION where one table does not have a column included by another, a compatible literal or expression may be used instead in the SELECT statement missing the column. For example, if there is no column in the JOINING table corresponding to the NAME column in SOURCE an expression is used to provide a value for a pseudo JOINING.NAME column. Assuming SOURCE.NAME is of type CHAR(10), the CAST function is used to convert an empty character string to CHAR(10).
SELECT S.ID, S.Name
FROM Source S
UNION ALL
SELECT J.ID, CAST("" AS CHAR(10))
FROM Joiner J
