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, SET transition-variable statement, SET VARIABLE statement, UPDATE statement, and assignment-statement.
>>-+-subselect---------+----------------------------------------> +-(fullselect)------+ '-| values-clause |-' .--------------------------------------------------------. V | >----+----------------------------------------------------+-+---> | .-DISTINCT-. | '-+-UNION--+----------+-----+--+-subselect---------+-' | '-ALL------' | +-(fullselect)------+ | .-DISTINCT-. | '-| values-clause |-' +-EXCEPT--+----------+----+ | .-DISTINCT-. | '-INTERSECT--+----------+-' >--+-----------------+--+--------------------+----------------->< '-order-by-clause-' '-fetch-first-clause-' values-clause .-,--------------. V | |--VALUES----| values-row |-+-----------------------------------| values-row |--+-+-expression-+-----------+---------------------------------| | '-NULL-------' | | .-,--------------. | | V | | '-(----+-expression-+-+--)-' '-NULL-------'
A fullselect that is enclosed in parenthesis 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.
A fullselect specifies a result table. If UNION, EXCEPT, or INTERSECT is not used, the result of the fullselect is the result of the specified subselect or values-clause.
VALUES (1), (2), (3) - 3 rows of 1 column VALUES 1, 2, 3 - 3 rows of 1 column VALUES (1, 2, 3) - 1 row of 3 columns VALUES (1,21), (2,22), (3,23) - 3 rows of 2 columnsA values-clause that is composed of n specifications of values-row, RE1 to REn, where n is greater than 1 is equivalent to:
RE1 UNION ALL RE2 ... UNION ALL REnThis means that the corresponding expressions of each values-row must be comparable. All result columns in a values-row are unnamed.
If the nth column of R1 is named, then the nth
column of the result table has that result column name. Otherwise,
the result column is unnamed.
If UNION, INTERSECT, or EXCEPT is specified, no column
can be a DATALINK or XML column.
EXCEPT and INTERSECT restrictions: VALUES, INTERSECT, and EXCEPT are not allowed if the query specifies one of the following:
Duplicate rows: Two rows are duplicates if each value in the first is equal to the corresponding value of the second. (For determining duplicates, two null values are considered equal.)
Operator precedence: UNION, UNION ALL, and INTERSECT are associative set operations. However, when UNION, UNION ALL, EXCEPT, and INTERSECT are used in the same statement, the result depends on the order in which the operations are performed. Operations within parenthesis are performed first. When the order is not specified by parentheses, operations are performed in left-to-right order with the exception that all INTERSECT operations are performed before UNION or EXCEPT operations.
Results of set operators: In the following example, the values of tables R1 and R2 are shown on the left. The other headings listed show the values as a result of various set operations on R1 and R2.
| R1 | R2 | UNION ALL | UNION | EXCEPT | INTERSECT |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2 | 1 |
| 1 | 1 | 1 | 2 | 5 | 3 |
| 1 | 3 | 1 | 3 | 4 | |
| 2 | 3 | 1 | 4 | ||
| 2 | 3 | 1 | 5 | ||
| 2 | 3 | 2 | |||
| 3 | 4 | 2 | |||
| 4 | 2 | ||||
| 4 | 3 | ||||
| 5 | 3 | ||||
| 3 | |||||
| 3 | |||||
| 3 | |||||
| 4 | |||||
| 4 | |||||
| 4 | |||||
| 5 |
Collating sequence: If a collating sequence other than *HEX is in effect when the statement that contains the UNION, EXCEPT, or INTERSECT keyword is executed and if the result tables contain columns that are SBCS data, mixed data, or Unicode data, the comparison for those columns is done using weighted values. The weighted values are derived by applying the collating sequence to each value.