fullselect

The fullselect is a component of the select-statement, ALTER TABLE statement for the definition of a materialized query table, CREATE TABLE statement, CREATE VIEW statement, DECLARE GLOBAL TEMPORARY TABLE statement, INSERT statement, UPDATE statement, and MERGE statement.


>>-+-subselect-------------+------------------------------------>
   +-(fullselect)----------+   
   |                   (1) |   
   '-| values-clause |-----'   

   .---------------------------------------------------.   
   V                                                   |   
>----+-----------------------------------------------+-+-------->
     |                .-DISTINCT-.                   |     
     '-+-UNION-----+--+----------+--+-subselect----+-'     
       +-EXCEPT----+  '-ALL------'  '-(fullselect)-'       
       '-INTERSECT-'                                       

>--+-----------------+--+--------------------+-----------------><
   '-order-by-clause-'  '-fetch-first-clause-'   

values-clause

|--VALUES--+-sequence-reference---------+-----------------------|
           |   .-,------------------.   |   
           |   V                    |   |   
           '-(---sequence-reference-+-)-'

Notes:

If values-clause is specified, UNION, EXCEPT, INTERSECT,order-by-clause, or fetch-first-clause must not also be specified. If fullselect contains a values-clause, the fullselect must only be specified in a select-statement that is referenced by statement-name in a PREPARE statement.

A fullselect that is enclosed in parentheses is called a subquery. For example, a subquery can be used in a search condition.

A scalar-fullselect is a fullselect, enclosed in parentheses, that returns a single result row and a single result column. If the result of the fullselect is no rows, then the null value is returned. An error is returned if there is more than one row in the result. For example, a scalar-fullselect can be used in the assignment clause of the DELETE, UPDATE and MERGE statements.

A row-fullselect is a fullselect that returns a single row. An error is returned if there is more than one row in the result. For example, a row-fullselect can be used in the assignment clause of the DELETE and UPDATE statements.

UNION, EXCEPT, or INTERSECT

The set operators, UNION, EXCEPT, and INTERSECT, correspond to the relational operators union, difference, and intersection. A fullselect specifies a result table. If a set operator is not used, the result of the fullselect is the result of the specified subselect. Otherwise, the result table is derived by combining the two other result tables (R1 and R2) subject to the specified set operator.

UNION DISTINCT or UNION ALL

If UNION ALL is specified, the result consists of all rows in R1 and R2. With UNION DISTINCT, the result is the set of all rows in either R1 or R2 with the redundant duplicate rows eliminated. In either case, each row of the result table of the union is either a row from R1 or a row from R2.

Start of change Column masks cannot be applied to the select lists that derive the final result table of set operations if any of the set operators that are used to derive the final result table is UNION ALL or UNION DISTINCT. End of change

Start of change For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT. End of change

EXCEPT DISTINCT or EXCEPT ALL

If EXCEPT ALL is specified, the result consists of all rows from only R1, including significant redundant duplicate rows. With EXCEPT DISTINCT, the result consists of all rows that are only in R1, with redundant duplicate rows eliminated. In either case, each row in the result table of the difference is a row from R1 that does not have a matching row in R2.

Start of change For compatibility with other SQL implementations, MINUS can be specified as a synonym for EXCEPT, and UNIQUE can be specified as a synonym for DISTINCT. End of change

INTERSECT DISTINCT or INTERSECT ALL

If INTERSECT ALL is specified, the result consists of all rows that are both in R1 and R2, including significant redundant duplicate rows. With INTERSECT DISTINCT, the result consists of all rows that are in both R1 and R2, with redundant duplicate rows eliminated. In either case each row of the result table of the intersection is a row that exists in both R1 and R2.

Start of change For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT. End of change

values-clause

Derives a result table by specifying the actual values, using sequence expressions, for each column of a row in the result table.

A values-clause is specified by:

A single sequence expression for a single-column result table.
n sequence expressions, separated by commas and enclosed in parentheses. n is the number of columns in the result table.

All result columns in a values-clause are unnamed.

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.
The nth column of the result of a set operator is derived from the nth columns of R1 and R2. The attributes of the result columns are determined using the rules for result columns.
R1 and R2 must not include columns having a data type of CLOB, BLOB, DBCLOB, XML, or a distinct type that is based on any of these types. However, this rule is not applicable when UNION ALL is used with the set operator.
If the nth column of R1 and the nth column of R2 have the same result column name, the nth column of the result table of the set operation has the same result column name. Otherwise, the nth column of the result table of the set operation is unnamed.
Qualified column names cannot be used in the ORDER BY clause when the set operators are specified.

For information on the valid combinations of operand columns and the data type of the result column, see Rules for result data types.

Duplicate rows: Two rows are duplicates if the value in each column in the first row is equal to the corresponding value of the second row. For determining duplicates, two null values are considered equal.

The DECFLOAT data type allows for multiple bit representations of the same number. For example 2.00 and 2.0 are two numbers with the same coefficient, but different exponent values. See Numeric comparisons section for more information. So if the result table of UNION contains a DECFLOAT column and multiple bit representations of the same number exist, the one returned is unpredictable.

Operator precedence: When multiple set operations are combined in an expression, set operations within parentheses are performed first. If the order is not specified by parentheses, set operations are performed from left to right with the exception that all INTERSECT operations are performed before any UNION or any EXCEPT operations.

Results of set operators: The following table illustrates the results of all set operations, with rows from result table R1 and R2 as the first two columns and the result of each operation on R1 and R2 under the corresponding column heading.

Table 1. Example of UNION, EXCEPT, and INTERSECT set operations on result tables R1 and R2.
Rows in R1	Rows in R2	Result of UNION ALL	Result of UNION DISTINCT	Result of EXCEPT ALL	Result of EXCEPT DISTINCT	Result of INTERSECT ALL	Result of INTERSECT DISTINCT
1	1	1	1	1	2	1	1
1	1	1	2	2	5	1	3
1	3	1	3	2		3	4
2	3	1	4	2		4
2	3	1	5	4
2	3	2		5
3	4	2
4		2
4		3
5		3
		3
		3
		3
		4
		4
		4
		5

Examples of fullselects

Example 1: A query specifies the union of result tables R1 and R2. A column in R1 has the data type CHAR(10) and the subtype BIT. The corresponding column in R2 has the data type CHAR(15) and the subtype SBCS. Hence, the column in the union has the data type CHAR(15) and the subtype BIT. Values from the first column are converted to CHAR(15) by adding five trailing blanks.

Example 2: Show all the rows from DSN8A10.EMP.

   SELECT * FROM DSN8A10.EMP;

Example 3: Using sample tables DSN8A10.EMP and DSN8A10.EMPPROJACT, list the employee numbers of all employees for which either of the following statements are true:

Their department numbers begin with 'D'.
They are assigned to projects whose project numbers begin with 'AD'.

   SELECT EMPNO FROM DSN8A10.EMP
     WHERE WORKDEPT LIKE 'D%'
     UNION
   SELECT EMPNO FROM DSN8A10.EMPPROJACT
     WHERE PROJNO LIKE 'AD%';

The result is the union of two result tables, one formed from the sample table DSN8A10.EMP, the other formed from the sample table DSN8A10.EMPPROJACT. The result—a one-column table—is a list of employee numbers. Because UNION, rather than UNION ALL, was used, the entries in the list are distinct. If instead UNION ALL were used, certain employee numbers would appear in the list more than once. These would be the numbers for employees in departments that begin with 'D' while their projects begin with 'AD'.

Example 4: Specify a series of unions and order the results by the first column of the final result table.

SELECT * FROM T1
UNION
SELECT * FROM T2
UNION 
SELECT * FROM T3
ORDER BY 1;

Example 5: Specify a series of unions and order the results by the first column of the final result table. The first ORDER BY clause order the rows of the result of the first union by the first column of that result table. The second ORDER BY clause is applied as part of the outer fullselect and it causes the rows of the final result table to be ordered by the first column of the final result table.

(SELECT * FROM T1
 UNION 
 SELECT * FROM T2
 ORDER BY 1)
UNION
SELECT * FROM T3
ORDER BY 1;

Example 6: Assume that tables T1 and T2 exist and each contain the same number of columns named C1, C2, and so on. This example of the EXCEPT operator produces all rows that are in T1 but not in T2, with duplicate rows removed:

(SELECT * FROM T1)
  EXCEPT DISTINCT 
(SELECT * FROM T2);

Example 7: Assume that tables T1 and T2 exist and each contain the same number of columns named C1, C2, and so on. This example of the INTERSECT operator produces all rows that are in both table T1 and table T2, with duplicate rows removed:

(SELECT * FROM T1)
  INTERSECT DISTINCT 
(SELECT * FROM T2);

Example 8: Retrieve the most recently generated value for the sequence MYSEQ1:

VALUES PREVIOUS VALUE FOR MYSEQ1;

Example 9: Retrieve the next value for the sequence MYSEQ1:

VALUES NEXT VALUE FOR MYSEQ1;