Select list notation
- *
- Represents a list of columns of table R in the order the columns are produced by the FROM clause. Any columns defined with the hidden attribute will not be included. 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. Any columns defined with
the hidden attribute are not included. 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 reestablished. 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:
- When an SQL program or SQL package is saved and then restored on a IBM® i product that is not the same release as the system from which it was saved.
- When SQL naming is specified for an SQL program or package and the owner of the program has changed since the SQL program or package was created.
- When an SQL statement is executed for the first time after the install of a more recent release of the IBM i operating system.
- When the SELECT * occurs in the fullselect of an INSERT statement or in a fullselect within a predicate, and a table or view referenced in the fullselect has been deleted and recreated with additional columns.
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.