SQL Reference

subselect

>>-select-clause-from-clause-+--------------+------------------->
                             '-where-clause-'
 
>----+-----------------+--+---------------+--------------------><
     '-group-by-clause-'  '-having-clause-'
 

The subselect is a component of the fullselect.

A subselect specifies a result table derived from the tables, views or nicknames identified in the FROM clause. The derivation can be described as a sequence of operations in which the result of each operation is input for the next. (This is only a way of describing the subselect. The method used to perform the derivation may be quite different from this description.)

The clauses of the subselect are processed in the following sequence:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause.

select-clause

             .-ALL------.
>>-SELECT----+----------+--------------------------------------->
             '-DISTINCT-'
 
>-----+-*--------------------------------------------------+---><
      |  .-,---------------------------------------------. |
      |  V                                               | |
      '----+-expression--+--------------------------+-+--+-'
           |             | .-AS-.                   | |
           |             '-+----+--new-column-name--' |
           '-exposed-name.*---------------------------'
 

The SELECT clause specifies the columns of the final result table. The column values are produced by the application of the select list to R. The select list is the names or expressions specified in the SELECT clause, and R is the result of the previous operation of the subselect. For example, if the only clauses specified are SELECT, FROM, and WHERE, R is the result of that WHERE clause.

ALL

Retains all rows of the final result table, and does not eliminate redundant duplicates. This is the default.

DISTINCT

Eliminates all but one of each set of duplicate rows of the final result table. If DISTINCT is used, no string column of the result table can have a maximum length that is greater than 255 bytes, and no column can be a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB type, distinct type on any of these types, or structured type. DISTINCT may be used more than once in a subselect. This includes SELECT DISTINCT, the use of DISTINCT in a column function of the select list or HAVING clause, and subqueries of the subselect.

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

Select List Notation:

*

Represents a list of names that identify the columns of table R. The first name in the list identifies the first column of R, the second name identifies the second column of R, and so on.

The list of names is established when the program containing the SELECT clause is bound. Hence, * (the asterisk) does not identify any columns that have been added to a table after the statement containing the table reference has been bound.

expression

Specifies the values of a result column. May be any expression of the type described in "Language Elements", but commonly the expressions used include column names. Each column name used in the select list must unambiguously identify a column of R.

new-column-name  or  AS new-column-name

Names or renames the result column. The name must not be qualified and does not have to be unique. Subsequent usage of column-name is limited as follows:

• A new-column-name specified in the AS clause can be used in the order-by-clause, provided the name is unique.
• A new-column-name specified in the AS clause of the select list cannot be used in any other clause within the subselect (where-clause, group-by-clause or having-clause).
• A new-column-name specified in the AS clause cannot be used in the update-clause.
• A new-column-name specified in the AS clause is known outside the fullselect of nested table expressions, common table expressions and CREATE VIEW.

name.*

Represents the list of names that identify the columns of the result table identified by exposed-name. The exposed-name may be a table name, view name, nickname, or correlation name, and must designate a table, view or nickname named in the FROM clause. The first name in the list identifies the first column of the table, view or nickname, the second name in the list identifies the second column of the table, view or nickname, and so on.

The list of names is established when the statement containing the SELECT clause is bound. Therefore, * does not identify any columns that have been added to a table after the statement has been bound.

The number of columns in the result of SELECT is the same as the number of expressions in the operational form of the select list (that is, the list established when the statement is prepared) and cannot exceed 500.

Limitations on String Columns

For limitations on the select list, see Restrictions Using Varying-Length Character Strings.

Applying the Select List

Some of the results of applying the select list to R depend on whether or not GROUP BY or HAVING is used. The results are described in two separate lists: