fetch clause in SQL

To constrain the number of rows returned by a query, you often use the LIMIT clause. The LIMIT clause is widely used by many relational database management systems such as MySQL, H2, and HSQLDB. However, the LIMIT clause is not a SQL-standard. To conform with the SQL standard, PostgreSQL supports the FETCH clause to retrieve a number of rows returned by a query. Note that the FETCH clause was introduced in SQL:2008.
The following illustrates the syntax of the SQL FETCH clause:
```
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY
```
In this syntax:
* ROW is the synonym for ROWS, FIRST is the synonym for NEXT . SO you can use them interchangeably
* The start is an integer that must be zero or positive. By default, it is zero if the OFFSET clause is not specified. In case the start is greater than the number of rows in the result set, no rows are returned;
* The row_count is 1 or greater. By default, the default value of row_count is 1 if you do not specify it explicitly.

Because the order of rows stored in the table is unspecified, you should always use the FETCH clause with the ORDER BY clause to make the order of rows in the returned result set consistent.
Note that the OFFSET clause must come before the FETCH clause in SQL:2008. However, OFFSET and FETCH clauses can appear in any order in PostgreSQL.
```
SELECT
film_id,
title
FROM
film
ORDER BY
title
OFFSET 5 ROWS
FETCH FIRST 5 ROW ONLY;
```
***FETCH vs. LIMIT***
The FETCH clause is functionally equivalent to the LIMIT clause. If you plan to make your application compatible with other database systems, you should use the FETCH clause because it follows the standard SQL.

Home