SELECT Statement (select_statement) 

A SELECT statement (select_statement) defines and creates an unnamed results table.

Syntax

<select_statement> ::= <query_expression> [<order_clause>] [<limit_clause>]
                      [<update_clause>] [<lock_option>] [FOR REUSE]

<limit_clause>     ::= LIMIT <row_count>
                     | LIMIT <offset>,<row_count>
<row_count>        ::= <unsigned_integer>
                     | <parameter_name>
<offset>           ::= <unsigned_integer>
                     | <parameter_name>

Examples

SQL Tutorial Data Query

Explanation

The rules specified for the DECLARE CURSOR statement also apply for the SELECT statement select_statement .

The search strategy used determines how the search is carried out. When the SELECT statement is executed, the system searches for all the rows in the results table and generates the results table physically. When a FETCH statement is executed, the system searches for the next relevant row in the results table but does not store it physically. This must be taken into account for the time behavior of FETCH statements.

Updateable Results Table

A results table and its underlying base tables can be updated if the query statement fulfills the following conditions:

●      The QUERY statement consists of a DECLARE CURSOR statement.

●      The QUERY expression (query_expression) must consist of one QUERY specification (query_spec) only.

●      Only one base table or updateable view table may be specified in the FROM clause of the QUERY specification.

●      The key word DISTINCT or a GROUP or HAVING clause may not be specified.

●      Expressions cannot contain a set function (set_function_spec).

ORDER Clause

The ORDER clause (order_clause) specifies a sorting sequence for a results table.

LIMIT Clause

You can use the LIMIT clause (limit_clause) to restrict the maximum number of rows of the result table. You can either specify only the maximum number of rows, or you can also specify at which row the output is supposed to start (offset value). The offset of the initial row is 0. If you do not specify an offset, the rows are ouput starting at the beginning of the result table.

If you use a ROWNO predicate or a TOP syntax element, you cannot use the LIMIT predicate.

If a LIMIT clause and an ORDER clause are specified, then all result rows are sorted and the corresponding number of rows is then output. Usually, the result is different from that which you would get if no ORDER clause was specified.

UPDATE Clause

An UPDATE clause (update_clause) can only be specified for updateable results tables. With updateable results tables, any position within a particular results table always corresponds to a position in the underlying tables and thus, ultimately, to a position in one or more base tables.

If an UPDATE clause was specified, the base tables can be updated using the position in the results table (CURRENT OF <result_table_name>) using an UPDATE statement or a DELETE statement. You use a LOCK statement to request a lock for the relevant rows in each of the base tables concerned.

LOCK Option

The LOCK option (lock_option) determines which locks are to be set for the rows that are read.

FOR REUSE

If the result table is to be specified in the from clause of a subsequent QUERY statement, the table should be specified using the FOR REUSE keywords. If FOR REUSE is not specified, the reusability of the results table depends on internal system strategies.

Since specifying FOR REUSE increases the response times of some query statements, this should only be specified if it is necessary to reuse the results table.