Projection Clause

The Projection clause (sometimes called the Select clause) specifies a list of database objects or expressions to retrieve, and can set restrictions on qualifying rows. (The select list is sometimes also called the projection list.)

This syntax fragment is part of the SELECT statement.
(1)
Projection Clause

1?  SKIP
2.1 offset
2.1 off_var1? 
2.2.1 FIRST
2.2.1  LIMIT
2.1 max
2.1  max_var1
1! ALL
2.1 DISTINCT
2.1 1 UNIQUE2
2 + , %Select List
Select List

1 
2.1  %OLAP Window expression3
2.1  %Expression4
2.1 column
1? ?  AS display_label
1? 
2.1 table.
2.1 view.
2.1 synonym.
2.1 alias.
2.1  column? ?  AS
2.2.1 column_alias
2.2.1 display_label
2.1  * 
2.1   external.  * 
2.1  (
2.2.1  %Collection Subquery5
2.2.1 subquery
2.1 )
Notes:
Element Description Restrictions Syntax
alias Temporary table or view name. See Declaring a Column Alias. Valid only if the FROM clause declares the alias for table or view. See FROM Clause. Identifier
column_alias Temporary identifier that you declare here for a column Must be unique among columns and column_alias names in this query. Only the GROUP BY clause can reference a column_alias. Identifier
column Column from which to retrieve data Must exist in a data source that the FROM clause references Identifier
display _label Temporary name declared here for a column or for an expression See Declaring a Display Label Identifier
external External table from which to retrieve data Must exist Database Object Name
max Integer (> 0) specifying maximum number of rows to return If max > number of qualifying rows then all matching rows are returned Literal Number
max_var Host variable or local SPL variable storing the value of max Same as max; valid in prepared objects and in SPL routines Language dependent
offset Integer (> 0) specifying how many qualifying rows to exclude before the first row of the result set Cannot be negative. If offset > (number of qualifying rows), then no rows are returned Literal Number
off_var Host variable or local SPL variable storing the value of offset Same as offset; valid in prepared objects and in user-defined routines Language dependent
subquery Embedded query A subquery within the Projection clause cannot include the SKIP, FIRST, INTO TEMP, or the ORDER BY clause. SELECT statement
table, view, synonym Name of a table, view, or synonym from which to retrieve data The synonym and the table or view to which it points must exist Database Object Name

The asterisk ( * ) specifies all columns in the table or view in their defined order. To retrieve all columns in another order, or a subset of columns, you must specify individual column names explicitly. A solitary asterisk ( * ) can be a valid Projection clause if the FROM clause specifies only a single data source.

To execute a query, the database server constructs a query plan and retrieves all qualifying rows that match the WHERE clause conditions. (Here a row refers to one set of values, as specified in the select list, from a single record in the table or joined tables that the FROM clause specifies.) If the query has no ORDER BY clause, the qualifying rows are sequenced in the order of their retrieval, which might vary with each execution; otherwise, their sequence follows the ORDER BY specification.