SQL FETCH

Summary: in this tutorial, you will learn how to use the SQL FETCH clause to limit the number of rows returned by a query.

Introduction to SQL FETCH clause #

To limit the number of rows returned by a query, you use the LIMIT clause. The LIMIT clause is widely supported by many database systems such as MySQL, H2, and HSQLDB. However, the LIMIT clause is not SQL-standard.

SQL:2008 introduced the OFFSET FETCH clause which has a similar function to the LIMIT clause. The OFFSET FETCH clause allows you to skip the first N rows in a result set before starting to return rows.

Here’s the syntax of the FETCH clause:

OFFSET rows_to_skip { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLYCode language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify rows_to_skip in the OFFSET clause. The rows_to_skip is an integer that specifies the number of rows to skip before the query begins to return the next rows. The rows_to_skip can be zero or positive. If the rows_to_skip is greater than the number of rows in the result set, the query will return no rows. The OFFSET clause is optional. If you omit it, the query does not skip any rows.
Second, specify the number of rows to return (row_count) in the FETCH clause.

The ROW and ROWS, FIRST and NEXT are synonyms, respectively. Therefore, you can use them interchangeably.

Since the database system may store rows in the table in an unspecified order, you should always use the FETCH clause with the ORDER BY clause to get consistent output.

Many database systems support the FETCH clause including Oracle Database 12c+, PostgreSQL 10+, and Microsoft SQL Server 2012+. However, each database system implements the FETCH clause differently with some variances.

In practice, you can find FETCH clause helpful for pagination. For example, if the application shows five rows per page, to retrieve rows for the second page, you can skip the first five rows and return the next five.

SQL FETCH clause examples #

We will use the employees table in the sample database for the demonstration.

Fetching the first five employees with the highest salary #

The following statement uses the FETCH clause to get the first five employees who have the highest salary:

SELECT
  first_name,
  salary
FROM
  employees
ORDER BY
  salary DESC
FETCH FIRST 5 ROWS ONLY;

Try it

Output:

 first_name |  salary
------------+----------
 Steven     | 24000.00
 Lex        | 17000.00
 Neena      | 17000.00
 John       | 14000.00
 Karen      | 13500.00

How the query works:

First, the ORDER BY clause sorts the employees by salary from high to low.
Second, the FETCH clause returns the first five rows.

Fetching newest employees based on hired dates #

The following query uses the FETCH clause to retrieve the three newest employees:

SELECT
  first_name,
  hire_date
FROM
  employees
ORDER BY
  hire_date DESC
FETCH FIRST 3 ROWS ONLY;

Try it

Output:

 first_name | hire_date
------------+------------
 Charles    | 2000-01-04
 Luis       | 1999-12-07
 Karen      | 1999-08-10

How the query works:

First, the ORDER BY clause sorts the employees by hired dates from latest to earliest.
Second, the FETCH clause returns the first three rows.

Using FETCH OFFSET for pagination #

The following query fetches the next five employees after skipping the first five:

SELECT
  first_name,
  last_name,
FROM
  employees
ORDER BY
  first_name,
   last_name
OFFSET
  5 ROWS
FETCH NEXT
  5 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name
------------+-----------
 Charles    | Johnson
 Daniel     | Faviet
 David      | Austin
 Den        | Raphaely
 Diana      | Lorentz

How the query works:

First, the ORDER BY clause sorts the employees by first and last names in alphabetical order.
Second, the OFFSET clause skips the first five rows.
Third, the FETCH clause returns the next five rows.

Summary #

Use the FETCH clause to limit the number of rows returned by a query.
Use the OFFSET clause to skip N rows before starting to return the number of rows specified in the FETCH clause.

Databases #

Was this tutorial helpful ?