fullselect

>>-+-subselect----+---------------------------------------------> '-(fullselect)-' .------------------------------------------. V | >--------+------------------------------------+--+------------->< '--+-UNION-----+---+-subselect----+--' '-UNION ALL-' '-(fullselect)-'

A fullselect specifies a result table. If UNION is not used, the result of the fullselect is the result of the specified subselect.

UNION  or  UNION ALL

Derives a result table by combining two other result tables (R1 and R2). If UNION ALL is specified, the result consists of all rows in R1 and R2. If UNION is specified without the ALL option, the result is the set of all rows in either R1 or R2,

with duplicate rows eliminated. In either case, however, each row of the UNION table is either a row from R1 or a row from R2.

If the nth column of R1 and the nth column of R2 have the same result column name, then the nth column of the result table has that column name. If the nth column of R1 and the nth column of R2 do not have the same name, then the nth column of the result table is unnamed.

Two rows are duplicates if each value in the first is equal to the corresponding value of the second. If a sort sequence other than *HEX is in effect when the statement that contains the UNION keyword is executed and if the result tables contain columns that have SBCS, UCS-2, or mixed data, the comparison for those columns is done using weighted values. The weighted values are derived by applying the sort sequence to each value. (For determining duplicates, two null values are considered equal.)

Both UNION and UNION ALL are associative operations. When you include the UNION ALL operator in the same SQL statement as a UNION operator, the result of the operation depends on the order of evaluation. Where there are no parentheses, evaluation is from left to right. Where parentheses are included, the parenthesized subselect is evaluated first, followed, from left to right, by the other components of the statement.

Rules for columns: R1 and R2 must have the same number of columns, and the data type of the nth column of R1 must be compatible with the data type of the nth column of R2. Character-string values are not compatible with datetime values.

The nth column of the result of UNION and UNION ALL is derived from the nth columns of R1 and R2. The attributes of the result columns are determined using the rules for result columns. For more information see Rules for Result Data Types.

If UNION is specified, no column can be a LOB or DATALINK column.

Examples of a fullselect

Example 1

Select all columns and rows from the EMPLOYEE table.

   SELECT * FROM EMPLOYEE

Example 2

List the employee numbers (EMPNO) of all employees in the EMPLOYEE table whose department number (WORKDEPT) either begins with 'E' or who are assigned to projects in the EMP_ACT table whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'.

   SELECT EMPNO FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
   UNION
   SELECT EMPNO FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')

Example 3

Make the same query as in example 2, only use UNION ALL so that no duplicate rows are eliminated.

   SELECT EMPNO FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
   UNION ALL
   SELECT EMPNO FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')