# fullselect

The *fullselect* is a component of the select-statement,
the INSERT statement, and the CREATE VIEW statement. It is also a
component of certain predicates which, in turn, are components of
a statement.

*subquery*, and a fullselect that is enclosed in parentheses is sometimes called a subquery.

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 or values-clause.

The authorization for a *fullselect* is described in the Authorization
section in "SQL queries".

- values-clause
- Derives a result table by specifying the
actual values, using expressions or row expressions, for each column of a row in the result table.
Multiple rows may be specified. If multiple rows are specified, the extended
indicator variable values of DEFAULT and UNASSIGNED must not be used (SQLSTATE 22539).
The result type of any expression in the values-clause cannot be a row
type (SQLSTATE 428H2).
NULL can only be used with multiple specifications of values-row, either as the column value of a single column result table or within a row-expression, and at least one row in the same column must not be NULL (SQLSTATE 42608).

A values-row is specified by:- A single expression for a single column result table
- n expressions (or NULL) separated by commas and enclosed in parentheses, where n is the number of columns in the result table or, a row expression for a multiple column result table.

A multiple row VALUES clause must have the same number of columns in each values-row (SQLSTATE 42826).

The following examples show values-clause and their meaning.`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 columns`

A values-clause that is composed of n specifications of values-row, RE_{1}to RE_{n}, where n is greater than 1, is equivalent to:`RE`

_{1}UNION ALL RE_{2}... UNION ALL RE_{n}This means that the corresponding columns of each values-row must be comparable (SQLSTATE 42825).

- 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 the duplicate rows eliminated. In either case, however, each row of the UNION table is either a row from R1 or a row from R2.
- EXCEPT or EXCEPT ALL
- Derives a result table by combining two other result tables (R1 and R2). If EXCEPT ALL is
specified, the result consists of all rows that do not have a corresponding row in R2, where
duplicate rows are significant. If EXCEPT is specified without the ALL option, the result consists
of all rows that are only in R1, with duplicate rows in the result of this operation
eliminated.
For compatibility with other SQL implementations, MINUS can be specified as a synonym for EXCEPT.

- INTERSECT or INTERSECT ALL
- Derives a result table by combining two other result tables (R1 and R2). If INTERSECT ALL is specified, the result consists of all rows that are in both R1 and R2. If INTERSECT is specified without the ALL option, the result consists of all rows that are in both R1 and R2, with the duplicate rows eliminated.

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 set operation can be derived from the masked values in R1 or R2.

- 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. The opposite is also true.
- 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 set 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.

- order-by-clause
- Refer to subselect for details of the
order-by-clause. A fullselect that contains an ORDER BY clause cannot be
specified in (SQLSTATE 428FJ):
- A materialized query table
- The outermost fullselect of a view

Note: An ORDER BY clause in a fullselect does not affect the order of the rows that are returned by a query. An ORDER BY clause affects only the order of the rows that are returned if it is specified in the outermost fullselect. Specify an order-by-clause to ensure a predictable order for determining the set of rows from the fullselect if the offset-clause or fetch-clause are specified. - offset-clause
- Refer to subselect for details of the
offset-clause. A fullselect that contains an OFFSET clause cannot be
specified in the following situations (SQLSTATE 428FJ):
- A materialized query table
- The outermost fullselect of a view

- fetch-clause
- Refer to subselect for details of the
fetch-clause. A fullselect that contains a FETCH clause cannot be
specified in the following situations (SQLSTATE 428FJ):
- A materialized query table
- The outermost fullselect of a view

- isolation-clause
- Refer to subselect for details of the
isolation-clause. If isolation-clause is
specified for a fullselect and it could apply equally to a subselect of the fullselect,
isolation-clause is applied to the fullselect. For example, consider the
following
query.

Even though the isolation clause WITH UR could apply only to the subselect SELECT NAME FROM CATALOG, it is applied to the whole fullselect.**SELECT**NAME**FROM**PRODUCT**UNION****SELECT**NAME**FROM**CATALOG**WITH UR**

The number of columns in the result tables R1 and R2 must be the same (SQLSTATE 42826). If the ALL keyword is not specified, R1 and R2 must not include any columns having a data type of CLOB, DBCLOB, BLOB, distinct type on any of these types, or structured type (SQLSTATE 42907).

The column
name of the *n*th column of the result table is the name of the *n*th column of R1 if it
is named. Otherwise, the *n*th column of the result table is unnamed. If the fullselect is used
as a select-statement, a generated name is provided when the statement is described. The generated
name cannot be used in other parts of the SQL statement such as the ORDER BY clause or the UPDATE
clause. The generated name can be determined by performing a DESCRIBE of the SQL statement and
consulting the SQLNAME field.

**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,
and two decimal floating-point representations of the same number are considered equal. For example,
2.00 and 2.0 have the same value (2.00 and 2.0 compare as equal) but have different exponents, which
allows you to represent both 2.00 and 2.0. So, for example, if the result table of a UNION operation
contains a decimal floating-point column and multiple representations of the same number exist, the
one that is returned (for example, 2.00 or 2.0) is unpredictable. For
more information, see Numeric comparisons.

When multiple operations are combined in an expression, operations within parentheses are performed first. If there are no parentheses, the operations are performed from left to right with the exception that all INTERSECT operations are performed before UNION or EXCEPT operations.

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 ALL | EXCEPT | INTER- SECT ALL | INTER- SECT |
---|---|---|---|---|---|---|---|

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 |