In Oracle Database, we can use the OFFSET clause to make a SELECT statement skip a certain number of rows in its result set.

The OFFSET clause is defined as part of the FETCH row limiting clause, which allows us to limit the number of rows returned by a query.

We can therefore use both OFFSET and FETCH to limit the output to just the specified number or percentage of rows, at a specified offset.

Example

Suppose we have a table with the following data:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId;

Result:

PRODUCTID	PRODUCTNAME
1	Left handed screwdriver
2	Right handed screwdriver
3	Long Weight (blue)
4	Long Weight (green)
5	Sledge Hammer
6	Chainsaw
7	Straw Dog Box
8	Hammock
9	Bottomless Coffee Mugs (4 Pack)
10	Tea Pot

We can see that the table contains ten rows of data.

Here’s an example of adding the OFFSET clause to the above query in order to skip past the first few rows:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
OFFSET 3 ROWS;

Result:

PRODUCTID	PRODUCTNAME
4	Long Weight (green)
5	Sledge Hammer
6	Chainsaw
7	Straw Dog Box
8	Hammock
9	Bottomless Coffee Mugs (4 Pack)
10	Tea Pot

In this example I specified an offset of 3, and so the query output skipped the first three rows. This resulted in the output starting at the fourth row.

Adding the FETCH Clause

As mentioned, the OFFSET clause is defined as part of a larger row limiting clause that also includes the FETCH clause. We can therefore combine the OFFSET clause with the FETCH clause to limit the results to just a bunch of rows in the middle:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

Result:

PRODUCTID	PRODUCTNAME
4	Long Weight (green)
5	Sledge Hammer

Here, I used the same offset amount that I did in the previous example, but I also specified a FETCH value that limits the results to just the next two rows. Therefore, the query skipped the first three rows (because of the OFFSET clause), and then returned just the next two rows (because of the FETCH clause).

Negative Values

Passing a negative value to the OFFSET clause results in the offset being 0, which means no offset is applied:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
OFFSET -3 ROWS FETCH NEXT 2 ROWS ONLY;

Result:

PRODUCTID	PRODUCTNAME
1	Left handed screwdriver
2	Right handed screwdriver

Here, I specified an offset of -3, which is treated as 0. Therefore, no offset was applied.

In this case I also included the FETCH clause that limited the output to just the next two rows. We can see that this query returned a different two rows than the previous query.

Passing a Null Value

Passing NULL to the OFFSET clause results in no rows being returned:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET NULL ROWS;

Result:

no data found

Offsets that Exceed the Row Count

Passing an offset that exceeds the row count results in no rows being returned:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 12 ROWS;

Result:

no data found

In this case, I specified an offset of 12 but there are only 10 rows in the data set. As expected, this resulted in nothing being returned.