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:
FETCH [ fetch_rows ] { ROW | ROWS } ONLY
Example
The following example shows how to use the offset FETCH clause in Standard SQL:
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:
The previous query should fetch the first three rows from the specified table.
Conclusion
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.
