The fetch-clause clause limits the number of rows that can be fetched.
The fetch-clause sets a maximum number of rows that can be retrieved. It specifies that an application does not want to retrieve more than fetch-row-count rows, regardless of how many rows there might be in the intermediate result table when this clause is not specified. An attempt to fetch beyond fetch-row-count rows is handled the same way as normal end of data.
- The outermost fullselect of a view
- The definition of a materialized query table
A fetch-clause can also be specified in a SELECT INTO statement.
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 retrieved is unpredictable. If both the fetch-clause and the ORDER BY clause are specified, the fetch-clause is processed on the ordered data.
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 fetched.
- A constant or variable that specifies the maximum number of rows to retrieve. fetch-row-count must be a numeric value that is a positive number or zero. If the value is not BIGINT, the value is cast to a BIGINT value. fetch-row-count must not be the null value.
Use of the fetch-clause with a constant for fetch-row-count that is not greater than the maximum big integer influences query optimization of the subselect or fullselect, based on the fact that, at most, a known number of rows will be retrieved.
Limiting the result table to a specified number of rows can improve performance. In some cases, the database manager will cease processing 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 database manager will also consider the offset value constant in determining when to cease processing.
Row access controls can indirectly affect the fetch-clause because row access controls affect the rows that are accessible to the authorization ID or role of the subselect or fullselect. Column access controls do no affect the fetch-clause.
- Syntax alternatives:
- The keywords FIRST and NEXT can be used interchangeably. The result is unchanged; however, using the keyword NEXT is generally more readable when using the offset-clause.
- The keywords ROW and ROWS can be used interchangeably. The result is unchanged, however, using ROWS is generally more readable when associated with a number of rows other than 1.
- The following alternatives are supported for compatibility with SQL used by other database
products. These alternatives are non-standard and should not be used.
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