select-clause

The SELECT clause specifies the columns of the final result table.

The column values are produced by the application of the select list to the final result table, 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 be a LOB type, distinct type based on LOB, or structured type. DISTINCT can be used more than once in a subselect. This includes SELECT DISTINCT, the use of DISTINCT in an aggregate 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 in the second. For determining duplicates, two null values are considered equal, and two different decimal floating-point representations of the same number are considered equal. For example, -0 is equal to +0 and 2.0 is equal to 2.00. Each of the decimal floating-point special values are also considered equal: -NAN equals -NAN, -SNAN equals -SNAN, -INFINITY equals -INFINITY, INFINITY equals INFINITY, SNAN equals SNAN, and NAN equals NAN.

When the data type of a column is decimal floating-point, and multiple representations of the same number exist in the column, the particular value that is returned for a SELECT DISTINCT can be any one of the representations in the column. For more information, see Numeric comparisons.

For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT.

Select list notation

*

Represents a list of names that identify the columns of table R, excluding any columns defined as IMPLICITLY HIDDEN. 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. Can be any expression that is a valid SQL language element, but commonly includes column names. Each column name used in the select list must unambiguously identify a column of R. The result type of the expression cannot be a row type (SQLSTATE 428H2).

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.

exposed-name.*

Represents the list of names that identify the columns of the result table identified by exposed-name, excluding any columns defined as IMPLICITLY HIDDEN. The exposed-name can 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 for a 4K page size or 1012 for an 8K, 16K, or 32K page size.

Limitations on string columns

For restrictions using varying-length character strings on the select list, see Character strings.

Applying the select list

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

If GROUP BY or HAVING is used

An expression X (not an aggregate function) used in the select list must have a GROUP BY clause with:
- a grouping-expression in which each expression or column-name unambiguously identifies a column of R (see group-by-clause) or
- each column of R referenced in X as a separate grouping-expression.
The select list is applied to each group of R, and the result contains as many rows as there are groups in R. When the select list is applied to a group of R, that group is the source of the arguments of the aggregate functions in the select list.

If neither GROUP BY nor HAVING is used

Either the select list must not include any aggregate functions, or each column-name in the select list must be specified within an aggregate function or must be a correlated column reference.
If the select does not include aggregate functions, then the select list is applied to each row of R and the result contains as many rows as there are rows in R.
If the select list is a list of aggregate functions, then R is the source of the arguments of the functions and the result of applying the select list is one row.

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 do not allow null values if they are derived from:

A column that does not allow null values
A constant
The COUNT or COUNT_BIG function
A host variable that does not have an indicator variable
A scalar function or expression that does not include an operand where nulls are allowed
An expression with a result type that is a weakly typed distinct type defined with a NOT NULL data type constraint

Result columns allow null values if they are derived from:

Any aggregate function except COUNT or COUNT_BIG
A column where null values are allowed
A scalar function or expression that includes an operand where nulls are allowed
A NULLIF function with arguments containing equal values
A host variable that has an indicator variable, an SQL parameter, an SQL variable, or a global variable
A result of a set operation if at least one of the corresponding items in the select list is nullable
An arithmetic expression or view column that is derived from an arithmetic expression and the database is configured with dft_sqlmathwarn set to Yes
A scalar subselect
A dereference operation
A GROUPING SETS grouping-expression

Names of result columns

If the AS clause is specified, the name of the result column is the name specified on the AS clause.
If the AS clause is not specified and if a column list is specified in the correlation clause, the name of the result column is the corresponding name in the correlation column list.
If neither an AS clause nor a column list in the correlation clause is specified and if the result column is derived only from a single column (without any functions or operators), the name of the result column is the unqualified name of that column.
If neither an AS clause nor a column list in the correlation clause is specified and if the result column is derived only from a single SQL variable or SQL parameter (without any functions or operators), the name of the result column is the unqualified name of that SQL variable or SQL parameter.
If neither an AS clause nor a column list in the correlation clause is specified and if the result column is derived using a dereference operation, the name of the result column is the unqualified name of the target column of the dereference operation.
All other result columns are unnamed. The system assigns temporary numbers (as character strings) to these 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, or the same precision for DECFLOAT columns.
a constant	the same as the data type of the constant.
the name of any numeric variable	the same as the data type of the variable, with the same precision and scale for DECIMAL variables, or the same precision for DECFLOAT variables.
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 the same length attribute; 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.
the name of a datetime column	the same as the data type of the column.
the name of a user-defined type column	the same as the data type of the column.
the name of a reference type column	the same as the data type of the column.