fullselect

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₁ to RE_n, where n is greater than 1, is equivalent to:

   RE1 UNION ALL RE2 ... UNION ALL REn

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.

order-by-clause

See "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 returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.

fetch-first-clause

See "subselect" for details of the fetch-first-clause. A fullselect that contains a FETCH FIRST clause cannot be specified in (SQLSTATE 428FJ):

• A materialized query table
• The outermost fullselect of a view

Note: A FETCH FIRST clause in a fullselect does not affect the number of rows returned by a query. A FETCH FIRST clause only affects the number of rows returned if it is specified in the outermost fullselect.

isolation-clause

See "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.

   SELECT NAME FROM PRODUCT
   UNION
   SELECT NAME FROM CATALOG
   WITH UR

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

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 nth column of the result table is the name of the nth column of R1 if it is named. Otherwise, the nth 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.