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.


Projection Clause

|--+-----------------------+------------------------------------>
   |                   (1) |   
   '-SKIP--+-offset--+-----'   
           '-off_var-'         

>--+--------------------------------+--------------------------->
   |                            (1) |   
   '---+-FIRST-+----+-max-----+-----'   
       '-LIMIT-'    '-max_var-'         

                             .-,---------------.   
   .-ALL------------- (2) .  V                 |   
>--+-+--------------+-----+----| Select List |-+----------------|
     +-DISTINCT-----+                              
     |  (1)         |                              
     '-------UNIQUE-'                              

Select List

                                  (3)                                  
|--+-+-| OLAP Window expression |-----+--+-----------------------+-+--|
   | |                (4)             |  '-+----+--display_label-' |   
   | +-| Expression |-----------------+    '-AS-'                  |   
   | '-column-------------------------'                            |   
   +-+----------+--+-column--+---------------------------+-+-------+   
   | +-table.---+  |         '-+----+--+-column_alias--+-' |       |   
   | +-view.----+  |           '-AS-'  '-display_label-'   |       |   
   | +-synonym.-+  '- * -----------------------------------'       |   
   | '-alias.---'                                                  |   
   '-+-external.-- * ------------------------+---------------------'   
     |                              (5)      |                         
     '-(--+-| Collection Subquery |-----+--)-'                         
          '-subquery--------------------'

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.