SELECT statement

Use the SELECT statement to retrieve values from a database or from an SPL or Informix® ESQL/C collection variable. A SELECT operation is called a query.

Rows or values that satisfy the specified search criteria of the query are called qualifying rows or values. What the query retrieves to its calling context, after applying any additional logical conditions, is called the result set of the query. This result set can be empty.

Syntax


>>-SELECT--| Select Options |----------------------------------->

   .--------------------------------------------------------.   
   V                                                        |   
>----+----------------------------------------------------+-+--->
     |                    (1)                             |     
     '-+-UNION--+-----+-+------SELECT--| Select Options |-'     
       |        '-ALL-' |                                       
       +-INTERSECT------+                                       
       '-+-MINUS--+-----'                                       
         '-EXCEPT-'                                             

>--+-------------------------+--+----------------------+-------->
   |                     (2) |  |                  (3) |   
   '-| ORDER BY Clause |-----'  '-| LIMIT Clause |-----'   

>--+----------------------------------------------------+------->
   |  (4)                  (5)                          |   
   '-------+-FOR READ ONLY----------------------------+-'   
           |  (6)                                 (7) |     
           '-------FOR UPDATE--+----------------+-----'     
                               |     .-,------. |           
                               |     V        | |           
                               '-OF----column-+-'           

>--+---------------------------------+-------------------------><
   |  (4)                        (8) |   
   '-------| INTO Table Clause |-----'   

Select options

|--+------------------------------------+----------------------->
   |  (4)                           (9) |   
   '-------| Optimizer Directives |-----'   

                         (10)   
>--| Projection Clause |---------------------------------------->

                                                    (13)   
>--+-----------------------------+--| FROM Clause |------------->
   |  (11)                  (12) |                         
   '--------| INTO Clause |------'                         

>--+----------------------------+------------------------------->
   |  (4)                  (14) |   
   '-------| GRID Clause |------'   

>--+-----------------------+--+------------------------------+-->
   |                  (15) |  |                         (16) |   
   '-| WHERE Clause |------'  '-| Hierarchical Clause |------'   

>--+--------------------------+--+------------------------+-----|
   |                     (17) |  |                   (18) |   
   '-| GROUP BY Clause |------'  '-| HAVING Clause |------'

Notes:

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

Usage

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.

For hierarchical queries that include the CONNECT BY clause, the FROM clause can specify only a single table that must reside in the local database of the Informix database server instance to which the current session is connected.

For queries that include the GRID clause, the instances of each table that the FROM clause specifies must have the same schema, the same database locale, and the same code set on every node that the GRID clause specifies.

The SELECT statement can reference no more than one external table that the CREATE EXTERNAL TABLE statement has defined. In complex queries, this external table can be specified only in the outermost query. You cannot reference an external table in a subquery.

You need the Connect access privilege on the database to execute a query, as well as the Select privilege on the table objects from which the query retrieves rows.

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

Clause		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
Aliases for Tables or Views		Temporary names for tables or columns in a query
Table expressions		Define derived tables as query data sources
Lateral derived tables		Define correlated table references in a query
The ONLY Keyword		Excludes child tables as data sources in queries of typed tables
Iterator Functions		Functions repeatedly returning values as a data source
ANSI Joins		Join queries compliant with ISO/ANSI syntax standards
Informix-Extension Outer Joins		Query syntax based on implicit LEFT OUTER joins
GRID clause		Specifies the nodes that store the tables of a grid query
Using the ON Clause		Specifies join conditions as pre-join filters
WHERE clause of SELECT		Sets conditions on qualifying rows and post-join filters
Hierarchical Clause		Sets conditions for queries of hierarchical data
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
ORDER SIBLINGS BY Clause		Sorts hierarchical data for siblings at every level
LIMIT Clause		Limits how many qualifying rows can be returned
FOR UPDATE Clause		Enables updating of the result set after a FETCH
FOR READ ONLY Clause		Disables updating of the result set after a FETCH
INTO TEMP clause		Puts the result set into a temporary table
INTO EXTERNAL clause		Stores the query result set in an external table
INTO STANDARD and INTO RAW Clauses		Stores the query result set in a permanent database table
UNION Operator		Combines the result sets of two SELECT statements and optionally discards duplicate rows
INTERSECT Operator		Returns distinct common rows from two query result sets
MINUS operator		Returns distinct rows that only the first of two queries return.