Unions with the BASIS SQL Engine

The BASIS SQL Engine and the BASIS ODBC Driver support relational unions. A relational union provides the ability to combine results from more than one query into a single result set.

Unions are enabled by specifying them in a SELECT statement. A simplified syntax for a SELECT statement is:

selstmt
 : SELECT setquantifier sellist FROM tbllist whereclause union orderclause
 ;
/* sub selects for union have no order by clause */
union
 : UNION [ALL] SELECT setquantifier sellist FROM tbllist whereclause union
 | empty  
 ;

An example of a union is:

select cust_num, total_orders from march97_order where (total_orders>30) union select cust_num, total_orders from april97_order where (total_orders>30)

Assuming sales information is stored in monthly history files, this query will produce the combined results from March 1997 and April 1997.

To order the results of a union, place the ORDER BY clause on the end of the statement:

select cust_num, total_orders, 'March 97' from march96_order where (total_orders>30) union select cust_num, total_orders, 'April 97' from april97_order where (total_orders>30) order by cust_num

Optimizing Queries with Unions

Unions can also be very powerful for optimizing single table queries. Assume that information on customer Jonesor customer Smith needs to be obtained from a customer master file that had hundreds of thousands of records. The typical query would look like:

select cust_num, last_name, curr_bal from customer where
last_name = 'Jones' or last_name = 'Smith'

Even if an index exists on LAST_NAME, the SQL Engine will have to read the entire file to ensure that it found records for both Smith and Jones. The OR conjunction necessitates such an exhaustive search.

For example, looking at only the last name Jones or Smith, the SQL Engine would have been able to use the LAST_NAME index and optimize the query so that very few reads would be required. If the original query was broken into two optimizable queries, selecting the union of the results would provide faster results:

select cust_num, last_name, curr_bal from customer where
(last_name = 'Jones') union select cust_num, last_name, curr_bal
from customer where (last_name = 'Smith')

Each SELECT statement is optimized individually, resulting in the highest possible performance.

Union Compatibility

The select list arguments in each SELECT statement in a union must be union compatible. This means that fields must be of the same type and size. Assume that LAST_NAME is a fixed length character field that is 30 characters long. Assume that FIRST_NAME is a fixed length character field that is 20 characters long. The following union has select statements that are not type compatible:

select last_name as name from customer union select first_name as name from customer

LAST_NAME and FIRST_NAME are both character fields but do not have the same length. However, it is possible to turn the select arguments into expressions (instead of direct column references) to overcome this form of union incompatibility:

select str(last_name) as name from customer union select STR(first_name) as name from customer

By making the arguments expressions, the SQL Engine assigns the same length to them. As a rule, expressions are given 200 bytes each.

Union ALL

By default, unions only return unique rows. Duplicate rows are discarded. If duplicate rows are required, use the ALL quantifier:

select city from customer union all select city from salesman

Because using the ALL quantifier is almost always faster, it is recommended that it be specified when necessary.