|
|
|
|
|
The UNION clause concatenates the rows of one table to the end of another table. The syntax is as follows:
UNION [ALL]
SELECT....
Use the UNION clause to add the rows of one query result set to the end of another similarly structured SELECT query result set. The SELECT statement for the source and destination query result sets must include the same number of fields for them to be UNION compatible. The source table structures themselves need not be the same as long as those fields included in the SELECT statements are. For example:
SELECT CustomerCode, CustomerName FROM ARCUST
UNION
SELECT ProductCode, Description FROM ICPROD
The data types for all fields retrieved by the UNION across the multiple query result sets must be identical. If there is a data type difference between two query result sets for a given field, an error will occur. You can use the CAST Function to avoid these errors:
SELECT CustomerCode, CAST(DateOfLastSale AS DATE) FROM ARCUST
UNION ALL
SELECT ProductCode, DateOfLastSale FROM ICPROD
Matching names is not mandatory for result set fields retrieved by the UNION across the multiple query result sets. Field name differences between the multiple query result sets are handled. If the first field of two query result sets has a different name, the first field in the UNION result set will use Expression.
By default, non-distinct rows are aggregated into single rows in a UNION join. Use ALL to retain non-distinct rows.
To join two query result sets with UNION where one query does not have a field included by another, a compatible literal or expression can be used instead in the SELECT statement missing the field. For example, if there is no field in the joining table corresponding to the CustomerName field in ARCUST, an expression is used to provide a value for a pseudo joining Name field. Assuming ARCUST.CustomerName is of type CHAR(40), the CAST function is used to convert an empty character string to CHAR(40):
SELECT CustomerCode, CustomerName FROM ARCUST
UNION ALL
SELECT ProductCode, CAST('' AS CHAR(40)) FROM ICPROD
If you are using an ORDER BY clause, this clause must be specified after the last SELECT statement being joined with a UNION clause. The WHERE, GROUP BY, and HAVING clauses can be specified for all or some of the individual SELECT statements being joined with a UNION clause. The following example shows how you could join two SELECT statements with a UNION clause and order the final joined result set:
SELECT CustomerCode FROM ARCUST
UNION
SELECT ProductCode FROM ICPROD
ORDER BY CustomerCode
When referring to actual field names in the ORDER BY clause you must use the field name of the first SELECT statement being joined with the UNION clause.