SQL Standard

SQL Fetch Statement

This post will explore how to use the FETCH statement in Standard SQL. This statement allows you to limit the number of records returned by a specific query.

I’m sure you are familiar with the LIMIT clause that performs a similar operation. So, what is the difference between LIMIT and FETCH?

The simple answer is there is not much difference. However, the LIMIT clause is not supported in Standard SQL. It was adopted from the FETCH clause by database vendors. Therefore, it is widely adopted by almost all major SQL database engines.

In some rare cases, you may encounter an instance where your database vendor does not support the LIMIT clause. Or you are using Standard SQL in your environment.

Clause Syntax

The following shows the syntax of the FETCH clause:

OFFSET offset_rows { ROW | ROWS }
FETCH [ fetch_rows ] { ROW | ROWS } ONLY



The following example shows how to use the offset FETCH clause in Standard SQL:

Create database zero_day;
use zero_day;
create table records (
    id int not null auto_increment primary key,
    report_name varchar(255) not null,
    report_date date not null,
    report_author varchar(100) default 'anonymous',
    severity int not null,
    check(severity > 0)

insert into records(report_name, report_date, severity)
values ('DESC-100', '2022-01-20', 6),
       ('DESC-200', '2022-01-21', 5),
       ('DESC-300', '2022-01-22', 10),
       ('DESC-400', '2022-01-23', 8),
       ('DESC-500', '2022-01-24', 4);

We can use the SELECT statement and the OFFSET FETCH clause, as shown below:

select * from records offset 0 rows fetch next 3 rows only;

The previous query should fetch the first three rows from the specified table.


In this post, we discussed the basics of using the offset FETCH statement in Standard SQL. In addition, we provided examples to highlight the clause syntax.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list