fullselect

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-+-subselect----+--------------------------------------------->
   '-(fullselect)-'
 
   .---------------------------------------------------.
   V                                                   |
>----+-----------------------------------------------+-+-------><
     |          .-DISTINCT-.                         |
     '-+-UNION--+----------+-----+--+-subselect----+-'
       |        '-ALL------'     |  '-(fullselect)-'
       |         .-DISTINCT-.    |
       +-EXCEPT--+----------+----+
       |            .-DISTINCT-. |
       '-INTERSECT--+----------+-'
 

The fullselect is a component of the select-statement and the CREATE VIEW statement.

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.

UNION DISTINCT 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 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 DISTINCT
Derives a result table by combining two other result tables (R1 and R2). The result consists of all rows that are only in R1, with duplicate rows in the result of this operation eliminated.
INTERSECT DISTINCT
Derives a result table by combining two other result tables (R1 and R2). The result consists of all rows that are in both R1 and R2, with the duplicate rows eliminated.

If the nth column of R1 and the nth column of R2 have the same result column name, then the nth column of the result table has the result column name. If the nth column of R1 and the nth column of R2 do not have the same names, then the result column is unnamed.

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.)

INTERSECT and EXCEPT are not allowed if the query specifies:

If a sort sequence other than *HEX is in effect when the statement that contains the UNION 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 sort sequence to each value.

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.

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