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

(explicit id sel003) sel003 (explicit id sel004) sel004 (explicit id sel006) sel006 (explicit id sel007) sel007 (explicit id sel008) sel008 (explicit id sel009) sel009 (explicit id sel010) sel010 (explicit id sel011) sel011 (explicit id sel012) sel012 (explicit id sel013) sel013 (explicit id sel014) sel014 (explicit id sel015) sel015 (explicit id sel016) sel016 (explicit id sel017) sel017 (explicit id sel018) sel018 (explicit id sel019) sel019 (explicit id sel020) sel020 (explicit id sel021) sel021 SELECT <Select Options> [ { UNION [ALL] | INTERSECT | { MINUS | EXCEPT } } [] SELECT <Select Options> ] [ <ORDER BY Clause> [] ] [ <LIMIT Clause> [] ] [ [] { FOR READ ONLY [] | [] FOR UPDATE [ OF column ] [] } ] [ [] <INTO Table 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

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.
1 Informix® extension
2 See Set operators in combined queries
3 Informix® ESQL/C only
4 See Optimizer Directives
5 See Projection Clause
6 See INTO Clause
7 See FROM Clause
8 See WHERE clause of SELECT
9 See GROUP BY Clause
10 See HAVING Clause
11 See ORDER BY Clause
12 See INTO table clauses
13 See Hierarchical Clause
14 Informix® ESQL/C and SPL routines only
15 See GRID clause
16 See LIMIT Clause
17 See FOR READ ONLY Clause
18 See FOR UPDATE Clause