SQL — Unions with the BASIS SQL Engine

The BASIS SQL Engine and the BASIS DBMS 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 1

A column number is now required in queries that have ORDER BY after a UNION (it does not work with the column name as in the ODBC/JDBC Driver). In the example above, 1 is the column number.

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.