fetch-clause

The fetch-clause sets a maximum number of rows that can be retrieved.

Read syntax diagramSkip visual syntax diagram FETCH NEXT 1fetch-row-count ROWROWS ONLY

The fetch-clause sets a maximum number of rows that can be retrieved. Use this clause to communicate to the database manager that the application is designed in such a way that it is not to retrieve more than fetch-row-count rows, regardless of how many rows there are in the intermediate result table. An attempt to fetch beyond fetch-row-count rows is handled the same way as normal end of data.

Determining a predictable set of rows to retrieve requires the specification of an ORDER BY clause with sort keys that would uniquely identify the sort order of each row in the intermediate result table. If the intermediate result table includes duplicate sort keys for some rows, the order of these rows is not deterministic. If there is no ORDER BY clause, the intermediate result table is not in a deterministic order. If the order of the intermediate result table is not deterministic, the set of rows that are retrieved is unpredictable.

fetch-row-count
An expression that specifies the maximum number of rows to retrieve. The expression must not contain a column reference, a scalar-fullselect, a function that is not deterministic, a function that has an external action, or a sequence reference (SQLSTATE 428H7). The numeric value must be a positive number or zero (SQLSTATE 2201W). If the data type of the expression is not BIGINT, the result of the expression is cast to a BIGINT value. When fetch-row-count is omitted, it is equivalent to 1.
Use of the fetch-clause with a constant for fetch-row-count that is not greater than the maximum large integer influences query optimization of the subselect or fullselect. This influence on query optimization is based on the fact that, at most, a known number of rows will be retrieved. The database manager uses the integer from the optimize-for-clause to influence query optimization of the outermost fullselect if the following clauses are specified:
  • The fetch-clause is specified in the outermost fullselect
  • The optimize-for-clause is specified for the select statement

Limiting the result table to a specified number of rows can improve performance. In some cases, the database manager ceases to process the query when it has determined the specified number of rows. If the offset-clause is also specified with a constant for offset-row-count, the constant offset value is also considered when a determination is made to cease processing.

If the fullselect contains an SQL data change statement in the FROM clause, all the rows are modified regardless of the limit on the number of rows to fetch.

Notes

  • The keywords FIRST and NEXT can be used interchangeably. The result is unchanged; however, using the keyword NEXT is more readable when the offset-clause is used.
  • The keywords ROW and ROWS can be used interchangeably. The result is unchanged, however using ROWS is more readable when associated with a number of rows other than 1.
  • Syntax alternatives: The following are supported for compatibility with SQL used by other database products. These alternatives are non-standard and should not be used.
    Table 1. Syntax alternatives
    Alternative syntax Equivalent syntax
    LIMIT x FETCH FIRST x ROWS ONLY
    LIMIT x OFFSET y OFFSET y ROWS FETCH NEXT x ROWS ONLY
    LIMIT y, x OFFSET y ROWS FETCH NEXT x ROWS ONLY