Union

A union operation uses the UNION operator to combine two queries into a single compound query. You can use the UNION operator between two or more SELECT statements to produce a temporary table that contains rows that exist in any or all of the original tables. You can also use the UNION operator in the definition of a view.

You cannot use the UNION operator inside a subquery in the following contexts

in the Projection clause of the SELECT statement
in the WHERE clause of the SELECT, INSERT, DELETE, or UPDATE statement.

The UNION operator is valid, however, in a subquery in the FROM clause of the SELECT statement, as in the following example:

SELECT * FROM (SELECT col1 FROM tab1 WHERE col1 = 100) AS vtab1(c1),
   (SELECT col1 FROM tab2 WHERE col1 = 10 
    UNION ALL 
    SELECT col1 FROM tab1 WHERE col1 < 50 ) AS vtab2(vc1);

HCL OneDB™ does not support ordering on ROW types. Because a UNION operation requires a sort to remove duplicate values, you cannot use a UNION operator when either query in the union operation includes ROW type data. However, the database server does support UNION ALL with ROW type data, because this type of operation does not require a sort.

The following figure illustrates the UNION set operation.

This figure is described in the surrounding text. — Figure 1: The Union set operation

The UNION keyword selects all rows from the two queries, removes duplicates, and returns what is left. Because the results of the queries are combined into a single result, the projection list in each query must have the same number of columns. Also, the corresponding columns that are selected from each table must contain compatible data types (CHARACTER data type columns must be the same length), and these corresponding columns must all allow or all disallow NULL values.

For the complete syntax of the SELECT statement and the UNION operator, see the HCL OneDB Guide to SQL: Syntax. For information specific to the product and any limitations that involve the INTO clause and compound queries, see the HCL OneDB ESQL/C Programmer's Manual.

The following query performs a union on the stock_num and manu_code columns in the stock and items tables.

Figure 2: Query

SELECT DISTINCT stock_num, manu_code FROM stock
   WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code FROM items
   WHERE quantity > 3;

The query selects those items that have a unit price of less than $25.00 or that have been ordered in quantities greater than three and lists their stock_num and manu_code, as the result shows.

Figure 3: Query result

stock_num manu_code

        5 ANZ
        5 NRG
        5 SMT
        9 ANZ
      103 PRC
      106 PRC
      201 NKL
      301 KAR
      302 HRO
      302 KAR