SELECT statement

Use the SELECT statement to retrieve values from a database or from an SPL or 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.


Select options

1?  4 %Optimizer Directives9  %Projection Clause10? 11  %INTO Clause12  %FROM Clause13? 4 %GRID Clause14 ?  %WHERE Clause15?  %Hierarchical Clause16?  %GROUP BY Clause17?  %HAVING Clause18
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.

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 HCL OneDB 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
HCL OneDB-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.