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.

Authorization

See Authorization for queries.

Syntax

values-clause

Notes:

¹ If values-clause is specified, UNION, EXCEPT, INTERSECT, order-by-clause, or fetch-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.

Description

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.

The expression that corresponds to the nth column in R1 and R2 can reference columns with column masks. The nth column of the result of the union can be derived from the masked values in R1 or R2.

With UNION DISTINCT, the elimination of the duplicate rows is based on the unmasked values in R1 and R2. Because all rows are from R1 or R2, the output values in the result table of the union may vary when one or more of the following conditions occur:

The expression corresponding to the nth column in R1 references columns with column masks, but the expression corresponding to the nth column in R2 does not, or vise versa.
The expressions corresponding to the nth column in R1 and R2 reference columns with different column masks.
The column mask definition references columns that are not the same target column for which the column mask is defined, and those columns are not part of the UNION DISTINCT operation. It is recommended that the column mask definition does not reference other columns from the target table.

For example, a row in R1 is derived from the masked value, and a row in R2 is derived from the unmasked value. If the row in the result table is from R1, the masked value is returned. If the row in the result table is from R2, the unmasked value is returned.

EXCEPT and INTERSECT can be intermixed with UNION if the rows in R1 and R2 for EXCEPT and INTERSECT do not reference columns with column masks.

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

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.

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 EXCEPT ALL or EXCEPT DISTINCT.

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.

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.

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 INTERSECT ALL or INTERSECT DISTINCT.

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

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.

Start of change values-clause must only be specified in the outer fullselect of select-statement for a PREPARE statement. End of change

order-by-clause

See subselect for details of the order-by-clause. A fullselect that contains an order-by-clause cannot be specified in the following contexts:

The outermost fullselect of a view
Note: An ORDER BY clause in a fullselect might not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned in a query if it is specified in the outermost fullselect. An order-by-clause should be specified to ensure a predictable order for determining the set of rows from the fullselect if the offset-clause or the fetch-clause are specified.
In the outer fullselect in the RETURN statement of an SQL table function.

offset-clause

See offset-clause for details of the offset-clause. A fullselect that contains an offset-clause cannot be specified in the following contexts:

The definition of a view
The definition of a materialized query table
In the RETURN statement of an SQL table function
The definition of a row permission
The definition of a column mask
The outermost fullselect for a sensitive dynamic cursor

fetch-clause

See fetch-clause for details of the fetch-clause. A fullselect that contains a fetch-clause cannot be specified in the following contexts:

The definition of a materialized query table
The outermost fullselect of the definition of a view
The outer fullselect in the RETURN statement of an SQL table function
The outermost fullselect for a sensitive dynamic cursor

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 for `fullselect`

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 DSN8C10.EMP.

   SELECT * FROM DSN8C10.EMP;

Example 3

Using sample tables DSN8C10.EMP and DSN8C10.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 DSN8C10.EMP
     WHERE WORKDEPT LIKE 'D%'
     UNION
   SELECT EMPNO FROM DSN8C10.EMPPROJACT
     WHERE PROJNO LIKE 'AD%';

The result is the union of two result tables, one formed from the sample table DSN8C10.EMP, the other formed from the sample table DSN8C10.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;

fullselect

Authorization

Syntax

Description

Examples for fullselect

Examples for `fullselect`