>>-SELECT--| Select Options |----------------------------------->
 
   .------------------------------------------------.
   V                                                |
>----+--------------------------------------------+-+----------->
     '-UNION--+-----+--SELECT--| Select Options |-'
              '-ALL-'
 
>--+--------------------------+--------------------------------->
   |                     (1)  |
   '-| ORDER BY Clause |------'
 
>--+--------------------------------------------+--------------->
   |  (2)                                       |
   '--------FOR--+-READ ONLY------------------+-'
                 '-UPDATE--+----------------+-'
                           |     .-,------. |
                           |     V        | |
                           '-OF----column-+-'
 
>--+---------------------------------+-------------------------><
   |                       (2)  (3)  |
   '-| INTO Table Clause |-----------'
 
Select Options:
 
|--+---------------------------------------------+-------------->
   |  (2)    (4)                            (5)  |
   '---------------| Optimizer Directives |------'
 
                         (6)
>--| Projection Clause |---------------------------------------->
 
>--+------------------------------------+----------------------->
   |  (7)    (8)                   (9)  |
   '---------------| INTO Clause |------'
 
                   (10)
>--| FROM Clause |--------+------------------------+------------>
                          |                  (11)  |
                          '-| WHERE Clause |-------'
 
>--+---------------------------+--+-------------------------+---|
   |                     (12)  |  |                   (13)  |
   '-| GROUP BY Clause |-------'  '-| HAVING Clause |-------'
 

1. See ORDER BY Clause
2. Informix extension
3. See INTO Table Clauses
4. Dynamic Server only
5. See Optimizer Directives
6. See Projection Clause
7. ESQL/C only
8. Stored Procedure Language only
9. See INTO Clause
10. See FROM Clause
11. See WHERE Clause
12. See GROUP BY Clause
13. See HAVING Clause

Element	Description	Restrictions	Syntax
column	Name of a column that can be updated after a FETCH	Must be in a FROM clause table, but does not need to be in the select list of the Projection clause	Identifier

The SELECT statement can return data from tables in the current database, or in another database of the current database server, or in a database of another database server. Only the SELECT keyword, the Projection clause, and the FROM clause are required specifications.

The SELECT statement can include various basic clauses, which are identified in the following list.

Clause	Page	Effect
Optimizer Directives		Specifies how the query should be implemented
Projection Clause		Specifies a list of items to be read from the database
INTO Clause		Specifies variables to receive the result set
FROM Clause		Specifies the data sources of Projection clause items
Using the ON Clause		Specifies join conditions as pre-join filters
WHERE Clause		Sets conditions on qualifying rows and post-join filters
GROUP BY Clause		Combines groups of rows into summary results
HAVING Clause		Sets conditions on the summary results
ORDER BY Clause		Sorts qualifying rows according to column values
FOR UPDATE Clause		Enables updating of the result set after a FETCH
Using the FOR READ ONLY Clause in Read-Only Mode		Disables updating of the result set after a FETCH
INTO TEMP Clause		Puts the result set into a temporary table
INTO EXTERNAL Clause (XPS)		Stores the result set in an external table
INTO SCRATCH Clause (XPS)		Stores the result set in an unlogged temporary table
UNION Operator		Combines the result sets of two SELECT statements
UNION Operator		Same as UNION ALL, but discards duplicate rows

Sections that follow describe these clauses of the SELECT statement.

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.)

Projection Clause:
 
|--+----------------------------+------------------------------->
   |                   (1) (2)  |
   '-SKIP--+-offset--+----------'
           '-off_var-'
 
>--+-------------------------------------------+---------------->
   |                                      (2)  |
   '-+-+-FIRST------+-+--+-max----------+------'
     | |       (1)  | |  |         (1)  |
     | '-LIMIT------' |  '-max_var------'
     |  (3)           |
     '--------MIDDLE--'
 
                          .-,---------------.
   .-ALL---------------.  V                 |
>--+-+---------------+-+----| Select List |-+-------------------|
     +-DISTINCT------+
     |  (2)          |
     '--------UNIQUE-'
 
Select List:
 
                      (4)
|--+-+-| Expression |------+--+-----------------------+--+------|
   | '-column--------------'  '-+----+--display_label-'  |
   |                            '-AS-'                   |
   +-+----------+--+-column--+-----------------------+-+-+
   | +-table.---+  |         '-+----+--display_label-' | |
   | +-view.----+  |           '-AS-'                  | |
   | +-synonym.-+  '- *--------------------------------' |
   | '-alias.---'                                        |
   |    (3)                                              |
   '-+--------external.-- *--------------------------+---'
     |       (1)                           (5)       |
     '-(--+--------| Collection Subquery |------+--)-'
          '-subquery----------------------------'
 

1. Dynamic Server only
2. Informix extension
3. Extended Parallel Server only
4. See Expression
5. See Collection Subquery

Element	Description	Restrictions	Syntax
alias	Temporary table or view name. See FROM Clause.	Valid only if the FROM clause declares the alias for table or view	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	3Cannot 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	3Same as offset; 3valid in prepared objects and in user-defined routines	Language dependent
subquery	Embedded query	Cannot include the SKIP or FIRST clause nor the ORDER BY clause	SELECT
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.

3The SKIP, FIRST, LIMIT, MIDDLE, DISTINCT, and UNIQUE specifications 3can restrict results to a subset of the qualifying rows, as sections that 3follow explain.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]