Using SQL row limiting clause

In this post we will see how to limit or omit a set of rows in Oracle using the offset and fetch clauses. In versions prior to Oracle 12c, much use was made of the ROWNUM clause, however, when using it with the ORDER BY clause, the developer might not have the expected return. These new clauses are available from Oracle 12c.

We will use as an example, the EMPLOYEES table of schema HR - which in our example will have only 10 records and some columns.

Table HR.Employees

Offset

Syntax: offset { integer-literal | ? } { row | rows }

The OFFSET clause is used to restrict/ignore the first N lines of a data set, returning the lines from N + 1. The literal value must be greater than or equal to zero (>= 0). By default, its value is 0 if the literal is omitted.

Query using OFFSET and omitting its clause:

select *
from   hr.employees
offset;

Query using OFFSET omitting the first 5 lines:

select *
from   hr.employees
offset 5 rows;

Fetch

Syntax: fetch { first | next } [integer-literal | percet ] { row | rows } only | with ties

The FETCH clause is used to return the N first/next rows of a dataset. The literal value must be greater than or equal to one (>= 1). If the literal is omitted, the default value is 1. We can also retrieve a percentage of rows using the percent clause - in this case, the literal can not be omitted. If the FETCH clause is completely omitted, all rows in the table are returned or in case of a combination with the offset clause, all rows that are not ignored.

If we replace the ONLY clause with WITH TIES, instead we return exactly the requested number of rows, the query can bring additional lines after the last case the value of the

ORDER BY is the same. For this to occur, it must be used in conjunction with the ORDER BY clause, otherwise, no additional rows will be returned.

Query using FETCH and omitting its clause:

select *
from   hr.employees
fetch;

Query using FETCH returning the first 5 rows:

select *
from   hr.employees
fetch next 5 rows only;

Query using the WITH TIES clause:

select   *
from     hr.employees
order by department_id desc
fetch next 3 rows with ties;

In the example above, we have defined that the number of rows to be returned should be 3, but we use the ORDER BY + WITH TIES clause, ordering the column DEPARTMENT_ID (desc). In this case, in addition to the number of rows that we defined, the query returned the remaining records that have DEPARTMENT_ID value equal to 90 (= 90), returning a total of 5 rows.

Offset + Fetch

Understanding the individual functioning of the OFFSET and FETCH clauses, we can now combine them. The use of the two clauses is very useful for systems that use pagination of results, such as web systems.

Let's look at a practical example of its use:

select *
from   hr.employees
offset 2 rows fetch next 3 rows only;

As we can see in the example above, we omitted the first two lines and requested the return of the next three only.

Note:

ROW is synonymous with ROWS and FIRST is synonymous with NEXT.

Conclusion

The OFFSET and FETCH clauses are a great alternative for tasks where we need to limit the number of rows in a result. Its syntax is also very simple, making the query more readable.

#sql #oracle