The SELECT clause specifies the columns of the final result table.
.-ALL------. >>-SELECT--+----------+-----------------------------------------> '-DISTINCT-' >--+-*-----------------------------------------------+--------->< | .-,-------------------------------------------. | | V | | '---+-expression--+-------------------------+-+-+-' | | .-AS-. | | | '-+----+--new-column-name-' | '-exposed-name.*--------------------------'
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.
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.
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.
For restrictions using varying-length character strings on the select list, see Character strings.
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.
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.
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. |