DB2 Version 9.7 for Linux, UNIX, and Windows

fullselect

Read syntax diagramSkip visual syntax diagram
>>-+-subselect---------+---------------------------------------->
   +-(fullselect)------+   
   '-| values-clause |-'   

   .----------------------------------------------.   
   V                                              |   
>----+------------------------------------------+-+------------->
     '-+-UNION---------+--+-subselect---------+-'     
       +-UNION ALL-----+  +-(fullselect)------+       
       +-EXCEPT--------+  '-| values-clause |-'       
       +-EXCEPT ALL----+                              
       +-INTERSECT-----+                              
       '-INTERSECT ALL-'                              

>--+-----------------+--+--------------------+------------------>
   '-order-by-clause-'  '-fetch-first-clause-'   

>--+------------------+----------------------------------------><
   '-isolation-clause-'   

values-clause

           .-,--------------.   
           V                |   
|--VALUES----| values-row |-+-----------------------------------|

values-row

|--+-+-expression-----+-------+---------------------------------|
   | +-NULL-----------+       |   
   | '-row-expression-'       |   
   |    .-,--------------.    |   
   |    V                |    |   
   '-(----+-expression-+-+--)-'   
          '-NULL-------'          

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. A fullselect that is a component of a predicate is called a 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 can 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 are examples of 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, RE1 to REn, 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.

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          

Examples of a fullselect

Example 1:  Select all columns and rows from the EMPLOYEE table.
  SELECT * FROM EMPLOYEE
Example 2:  List the employee numbers (EMPNO) of all employees in the EMPLOYEE table whose department number (WORKDEPT) either begins with 'E' or who are assigned to projects in the EMP_ACT table whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'.
  SELECT EMPNO
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
Example 3:  Make the same query as in example 2, and, in addition, "tag" the rows from the EMPLOYEE table with 'emp' and the rows from the EMP_ACT table with 'emp_act'. Unlike the result from example 2, this query may return the same EMPNO more than once, identifying which table it came from by the associated "tag".
  SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act' FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
Example 4:  Make the same query as in example 2, only use UNION ALL so that no duplicate rows are eliminated.
  SELECT EMPNO
      FROM EMPLOYEE
      WHERE WORKDEPT LIKE 'E%'
  UNION ALL
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
Example 5:  Make the same query as in Example 3, only include an additional two employees currently not in any table and tag these rows as "new".
  SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHEREWORKDEPTLIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act'
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
  UNION
     VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')
Example 6:  This example of EXCEPT produces all rows that are in T1 but not in T2.
   (SELECT * FROM T1)
   EXCEPT ALL
   (SELECT * FROM T2)
If no null values are involved, this example returns the same results as
   SELECT ALL *
      FROM T1
      WHERE NOT EXISTS (SELECT * FROM T2
                          WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)
Example 7:  This example of INTERSECT produces all rows that are in both tables T1 and T2, removing duplicates.
   (SELECT * FROM T1)
   INTERSECT
   (SELECT * FROM T2)
If no null values are involved, this example returns the same result as
   SELECT DISTINCT * FROM T1
      WHERE EXISTS (SELECT * FROM T2
                    WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)
where C1, C2, and so on represent the columns of T1 and T2.