Using UNION

Avoiding Conversions

SQLCore checks the data types of each select list in a UNION query, and determines the result data type. For all select lists that have data types different than the result data type, a conversion is carried out if the types are compatible. Refer to the description of the SELECT statement in the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual for a table showing the kinds of conversions.

The following example requires conversions:

   CREATE TABLE T1 (Item CHAR(40), Price SMALLINT)

   CREATE TABLE T2 (Item CHAR(40), Price INTEGER)

   CREATE TABLE T3 (Item CHAR(40), Price DECIMAL(10,2)

   CREATE TABLE T4 (Item CHAR(40), Price FLOAT)





   SELECT Item, Price

   FROM T1

   UNION

   SELECT Item, Price

   from T2

   UNION

   SELECT Item, Price

   FROM T3

   UNION

   SELECT Item, Price

   FROM T4

Since the result data type in this UNION is FLOAT, sources 1, 2, and 3 require conversion to FLOAT, which is the largest common denominator type. Now if all columns were of the same type--for example, FLOAT--no conversions would be required and the performance of such a query would be faster than the conversion example.

Defining Indexes for UNION Queries

Each source SELECT in a UNION query is optimized individually, and SQLCore tries to pick the best access method for each source. Therefore, you should create indexes on all sources, if possible, to maximize performance. The result of the UNION is not optimized.

If you know that there are no duplicate rows generated by the query, or if you do not need to exclude duplicate rows from the result, the UNION ALL form is faster than UNION, because it does not sort the query result.