Oracle

In Oracle, the FETCH and OFFSET clauses are used to control the number of rows returned by a query, making it easier to implement pagination or to retrieve a subset of rows. The OFFSET clause skips a specified number of rows, while the FETCH clause limits the number of rows returned. These are especially useful in applications that display large datasets over multiple pages, such as in a library system where you might want to paginate through the books available.

How FETCH and OFFSET Work in Oracle

1. Basic Syntax
   The general syntax for FETCH and OFFSET is:

   SELECT column_list
   FROM table_name
   ORDER BY column_name
   OFFSET offset_value ROWS
   FETCH NEXT fetch_value ROWS ONLY;
   

   • OFFSET skips a number of rows.
   • FETCH NEXT limits the number of rows returned.

2. Example Using a Books Table
   Here’s an example where you are fetching a subset of rows from the books table, skipping the first 10 books and fetching the next 5:

   SELECT title, author_id
   FROM books
   ORDER BY title
   OFFSET 10 ROWS
   FETCH NEXT 5 ROWS ONLY;
   

   • This query skips the first 10 books (based on the alphabetical order of titles) and retrieves the next 5.

3. Using FETCH and OFFSET for Pagination
   Pagination allows users to view data in chunks, like displaying 10 books per page.

   SELECT title, author_id
   FROM books
   ORDER BY title
   OFFSET (10 * (:page_number - 1)) ROWS
   FETCH NEXT 10 ROWS ONLY;
   

   • :page_number is a variable indicating the current page. If it is set to 1, it fetches the first 10 rows. If it is 2, it skips the first 10 rows and fetches the next 10.

4. When to Use ORDER BY

   • Always use ORDER BY with OFFSET and FETCH. Without it, the result set may return unpredictable rows, since the rows skipped and fetched are not in any specific order.

5. Best Practices

   • Ensure Predictability: Always include an ORDER BY clause to ensure consistent results when using FETCH and OFFSET.
   • Limit Offset: Try to minimize the number of rows skipped using OFFSET, especially for large datasets, as skipping many rows can affect performance.
   • Test Pagination Queries: Always test pagination queries to verify they return the expected results.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT *
FROM act_order
FETCH FIRST 5 ROWS ONLY;

SELECT *
FROM act_order
ORDER BY order_number
OFFSET 10 ROWS
FETCH FIRST 5 ROWS ONLY;

Example 1:

Let's understand the process of extracting data from a specified table using FETCH clause. In this case, we aim to retrieve top 5 rows from the employee table.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT *
FROM org_employee
FETCH FIRST 5 ROWS ONLY;

Example 1 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 1 - Query Output

Example 2:

Let's understand the process of extracting data from a specified table using FETCH clause in combination with offset. Here, offset is the number of rows to skip. In this exmaple we are trying to skip 3 rows and retreive next 6 rows

Example 2 - Raw data from employee table

Example 2 - Query

SELECT *
FROM org_employee
OFFSET 3 ROWS
FETCH FIRST 6 ROWS ONLY;

Example 2 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 2 - Query Output

Example 3:

Let's understand the process of extracting data using FETCH in combination with ORDER BY clause. Here we are trying to order the data by last name and then select top 4 rows.

Example 3 - Raw data from employee table

Example 3 - Query

SELECT *
FROM org_employee
ORDER BY last_name
FETCH FIRST 4 ROWS ONLY;

Example 3 - Step 1 Order the raw data by last name

As step 1 first we have to order our raw data by last name as shown in above image.

Example 3 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 3 - Query Output