SQL | OFFSET-FETCH Clause

The OFFSET-FETCH clause in SQL is a powerful tool used for pagination, allowing users to retrieve a subset of rows from a result set. It is especially useful when dealing with large datasets, enabling smooth navigation through data by skipping a certain number of rows and fetching only the required ones.

TOP cannot be used alongside OFFSET-FETCH. In this article, we will cover its syntax, usage, and practical examples to help us understand and implement OFFSET-FETCH effectively.

What is the OFFSET Clause?

The OFFSET clause identifies the starting point from which rows are returned in a result set. It essentially skips the specified number of rows before returning the remaining rows. OFFSET can only be used with ORDER BY clause. It cannot be used on its own. OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET rows_to_skip ROWS;

Consider the following Employee table which contains details of employees, including their ID, first name, last name, and salary. It will be used for demonstrating the OFFSET and FETCH examples.

Example 1: Skip the Employee with the Lowest Salary

Print the first name and last name of all employees except the one with the lowest salary.

Query:

SELECT Fname, Lname
FROM Employee
ORDER BY Salary
OFFSET 1 ROWS ONLY;

Output

Explanation:

• The ORDER BY Salary sorts employees by their salary.
  
• OFFSET 1 ROWS skips the first record, which corresponds to the employee with the lowest salary.

What is the FETCH Clause?

The FETCH clause retrieves a specified number of rows from a result set. It cannot be used independently and must be paired with the OFFSET clause.

Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name
OFFSET rows_to_skip
FETCH NEXT number_of_rows ROWS ONLY;

Example 2: Fetch Rows from 3rd to 6th Position

Print the first name and last name of employees from the 3rd to the 6th position when sorted by salary. The OFFSET/FETCH row count expression can be only be any arithmetic, constant, or parameter expression which will return an integer value

Query:

SELECT Fname, Lname
FROM Employee
ORDER BY Salary
OFFSET 2 ROWS
FETCH NEXT 4 ROWS ONLY;

Output

Explanation:

• OFFSET 2 ROWS skips the first two rows.
  
• FETCH NEXT 4 ROWS ONLY retrieves the next four rows, corresponding to positions 3 through 6.

Example 3: Retrieve the Bottom 2 Rows

Print the details of last 2 tuples of Employee table when sorted by Salary.

Query:

SELECT Fname, Lname
FROM Employee
ORDER BY Salary
OFFSET (SELECT COUNT(*) FROM EMPLOYEE) - 2 ROWS
FETCH NEXT 2 ROWS;

Output

Explanation:

• (SELECT COUNT(*) FROM Employee) - 2 calculates the position to start fetching the last two rows.
  
• FETCH NEXT 2 ROWS ONLY retrieves the bottom two rows.

Conclusion

The OFFSET-FETCH clause is a important tool for managing large datasets by enabling precise control over the range of rows returned. Its combination with the ORDER BY clause ensures accurate pagination and allows users to implement efficient data retrieval strategies. By mastering OFFSET-FETCH, we can optimise our SQL queries for better performance and usability in applications requiring pagination, such as web-based data tables or reports.