select-clause

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram           .-ALL------.
>>-SELECT--+----------+----------------------------------------->
           '-DISTINCT-'
 
>--+-*-------------------------------------------+-------------><
   | .-,---------------------------------------. |
   | V                                         | |
   '---+-expression--+---------------------+-+-+-'
       |             | .-AS-.              | |
       |             '-+----+--column-name-' |
       +-table-name.*------------------------+
       +-view-name.*-------------------------+
       '-correlation-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
Selects all rows of the final result table and does not eliminate duplicates. This is the default.
DISTINCT
Eliminates all but one of each set of duplicate rows of the final result table. Two rows are duplicates of one another only if each value in the first row is equal to the corresponding value in the second row. (For determining duplicate rows, two null values are considered equal.) Sort sequence is also used for determining distinct values.

DISTINCT is not allowed if the select list contains a LOB or DATALINK column.

Select list notation

*
Represents a list of columns of table R in the order the columns are produced by the FROM clause. The list of names is established when the statement containing the SELECT clause is prepared. Therefore, * does not identify any columns that have been added to a table after the statement has been prepared.
expression
Specifies the values of a result column. Each column-name in the expression must unambiguously identify a column of R.
column-name or AS column-name
Names or renames the result column. The name must not be qualified and does not have to be unique.
name.*
Represents a list of columns of name in the order the columns are produced by the FROM clause. The name can be a table name, view name, or correlation name, and must designate an exposed table, view, or correlation name in the FROM clause immediately following the SELECT clause. The first name in the list identifies the first column of the table or view, the second name in the list identifies the second column of the table or view, and so on.

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

Normally, when SQL statements are implicitly rebound, the list of names is not re-established. Therefore, the number of columns returned by the statement does not change. However, there are four cases where the list of names is established again and the number of columns can change:

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 at prepare time), and cannot exceed 8000. The result of a subquery must be a single expression, unless the subquery is used in the EXISTS predicate.

Applying the select list

The results of applying the select list to R depend on whether or not GROUP BY or HAVING is used:

If GROUP BY or HAVING is used
If neither GROUP BY nor HAVING is used

In either case the nth column of the result contains the values specified by applying the nth expression in the operational form of the select list.

Null attributes of result columns

Result columns allow null values if they are derived from:

Names of result columns

Data types of result columns

Each column of the result of SELECT acquires a data type from the expression from which it is derived.

When the expression is: The data type of the result column is:
the name of any numeric column the same as the data type of the column, with the same precision and scale for decimal columns.
an integer constant INTEGER or BIGINT (if the value of the constant is outside the range of INTEGER, but within the range of BIGINT).
a decimal or floating-point constant the same as the data type of the constant, with the same precision and scale for decimal constants.
the name of any numeric variable the same as the data type of the variable, with the same precision and scale for decimal variables. If the data type of the variable is not identical to an SQL data type (for example, DISPLAY SIGN LEADING SEPARATE in COBOL), the result column is decimal.
an expression the same as the data type of the result, with the same precision and scale for decimal results as described under Expressions.
any function the data type of the result of the function. For a built-in function, see Built-in functions to determine the data type of the result. For a user-defined function, the data type of the result is what was defined in the CREATE FUNCTION statement for the function.
the name of any string column the same as the data type of the column, with the same length attribute.
the name of any string variable the same as the data type of the variable, with a length attribute equal to the length of the variable. If the data type of the variable is not identical to an SQL data type (for example, a NUL-terminated string in C), the result column is a varying-length string.
a character-string constant of length n VARCHAR(n)
a graphic-string constant of length n VARGRAPHIC(n)
the name of a datetime column, or an ILE RPG compiler or ILE COBOL compiler datetime host variable the same as the data type of the column or variable.
the name of a datalink column a datalink, with the same length attribute.
the name of a row ID column or a row ID variable ROWID
the name of a distinct type column the same as the distinct type of the column, with the same length, precision, and scale attributes, if any.