SQL Reference

fullselect

>>-+-subselect---------+---------------------------------------->
   +-(fullselect)------+
   '-| values-clause |-'
 
      .---------------------------------------------------.
      V                                                   |
>--------+---------------------------------------------+--+----><
         '--+-UNION---------+---+-subselect---------+--'
            +-UNION ALL-----+   +-(fullselect)------+
            +-EXCEPT--------+   '-| values-clause |-'
            +-EXCEPT ALL----+
            +-INTERSECT-----+
            '-INTERSECT ALL-'
 
values-clause
 
              .-,-----------------.
              V                   |
|---VALUES-------| values-row |---+-----------------------------|
 
values-row
 
|---+--+-expression-+---------------+---------------------------|
    |  '-NULL-------'               |
    |    .-,-----------------.      |
    |    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. 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.

values-clause

Derives a result table by specifying the actual values, using expressions, for each column of a row in the result table. Multiple rows may be specified.

NULL can only be used with multiple values-rows and at least one row in the same column must not be NULL (SQLSTATE 42826).

A values-row is specified by:

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

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

The following are examples of values-clauses 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 values-rows, RE₁ to RE_n, where n is greater than 1, is equivalent to

   RE₁ UNION ALL RE₂ ... UNION ALL RE_n

This means that the corresponding expressions of each values-row must be comparable (SQLSTATE 42825) and the resulting data type is based on Rules for Result Data Types.

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.

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 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 string columns declared larger than 255 bytes or having a data type of LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, distinct type on any of these types, or structured type (SQLSTATE 42907).

The columns of the result are named as follows:

If the nth column of R1 and the nth column of R2 have the same result column name, then the nth column of R has the result column name.
If the nth column of R1 and the nth column of R2 have different result column names, a name is generated. This name cannot be used as the column name in an ORDER BY or UPDATE clause.

The generated name can be determined by performing a DESCRIBE of the SQL statement and consulting the SQLNAME field.

Two rows are duplicates of one another if each value in the first is equal to the corresponding value of the second. (For determining duplicates, two null values are considered equal.)

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

For the rules on how the data types of the result columns are determined, see Rules for Result Data Types.

For the rules on how conversions of string columns are handled, see Rules for String Conversions.

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.

[ Top of Page | Previous Page | Next Page ]