SQL FETCH

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 in 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 N first rows in a result set before starting to return any rows.

The following shows the syntax of the SQL FETCH clause:

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

In this syntax:

  • The ROW and ROWS, FIRST and NEXT are the synonyms. Therefore, you can use them interchangeably.
  • The offset_rows is an integer number which must be zero or positive. In case the offset_rows is greater than the number of rows in the result set, no rows will be returned.
  • The fetch_rows is also an integer number that determines the number of rows to be returned. The value of fetch_rows is equal to or greater than one.

Because rows are stored 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 OFFSET FETCH clause including Oracle Database 12c+, PostgreSQL 10+, and Microsoft SQL Server 2012+. However, each database system implements the OFFSET FETCH clause differently with some variances.

The OFFSET FETCH clause is typically used in the client or web applications that require pagination. For example, if each page has ten rows, to get the rows of the second page, you can skip the first ten rows and returns the next ten rows.

SQL FETCH examples

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



The following statement returns the first employee who has the highest salary:

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

In this example, first, the ORDER BY clause sort the employees by salary from high to low. The OFFSET clause skips zero rows and the FETCH clause returns the first row.

The following statement sorts the employees by salary, skips the first five employees with the highest salary, and fetches the next five ones.

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

Summary

  • Use the SQL FETCH clause to limit the number of rows returned by a query.
  • The SQL FETCH clause skips N rows in a result set before starting to return any rows.

Popular posts from this blog

SQL Syntax

Image
  SQL Syntax Summary : in this tutorial, we will introduce you to the SQL syntax that helps you understand the details of SQL statements. SQL is a declarative language, therefore, its syntax reads like a natural language. An SQL statement begins with a verb that describes the action, for example, SELECT, INSERT, UPDATE or DELETE. Following the verb are the subject and predicate. A predicate specifies conditions that can be evaluated as true, false, or unknown. See the following SQL statement: SELECT first_name FROM employees WHERE YEAR (hire_date) = 2000 ; Code language: SQL (Structured Query Language) ( sql ) As you see, it reads like a normal sentence. Get the first names of employees who were hired in 2000. The  SELECT first_name ,  FROM employees , and WHERE  are clauses in the SQL statement. Some clauses are mandatory e.g., the  SELECT  and  FROM  clauses whereas others are optional such as the  WHERE  clause. Because SQL was designed specifically for the non-tec
Read more

SQL LIMIT

Image
Skip to SQL LIMIT Summary : in this tutorial, you’ll learn to use the SQL  LIMIT  clause to limit the number of rows returned from a query. Introduction to SQL LIMIT clause To limit the number of rows returned by a  select  statement, you use the  LIMIT  and  OFFSET  clauses. The following shows the syntax of  LIMIT  &  OFFSET  clauses: SELECT column_list FROM table1 ORDER BY column_list LIMIT row_count OFFSET offset ; Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  LIMIT row_count  determines the number of rows ( row_count ) returned by the query. The  OFFSET offset  clause skips the  offset  rows before beginning to return the rows. The  OFFSET  clause is optional. If you omit it, the query will return the row_count rows from the first row returned by the  SELECT  clause. When you use the  LIMIT  clause, it is important to use an  ORDER BY  clause to ensure the order of rows in the result set. Not all database systems support the  LIM
Read more