SQL OFFSET Clause for Pagination

The OFFSET clause in SQL is commonly used to skip a specified number of rows in a query result, often in conjunction with the LIMIT clause to retrieve a specific subset of rows. This is particularly useful for implementing pagination, allowing you to load data in manageable chunks instead of all at once.

Basic Syntax

Here’s the general syntax of how to use OFFSET with LIMIT in SQL:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;

• LIMIT specifies the maximum number of rows to retrieve.
• OFFSET specifies the number of rows to skip before beginning to return rows in the result.

Example: Fetching Rows with OFFSET

Assume you have an employees table and want to skip the first 10 rows, then retrieve the next 5 rows:

SELECT * 
FROM employees 
ORDER BY employee_id 
LIMIT 5 OFFSET 10;

In this example:

• The query will start fetching rows from the 11th row based on employee_id ordering.
• Only 5 rows will be returned.

Pagination with OFFSET and LIMIT

Pagination can be achieved by specifying different values for OFFSET and LIMIT across multiple pages:

1. Page 1 (First 10 rows):

   SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 0;

2. Page 2 (Next 10 rows):

   SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 10;

3. Page 3 (Another 10 rows):

   SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 20;

This approach enables efficient data retrieval for applications requiring sequential data loading, such as lists or tables that support pagination.

Key Takeaways

• OFFSET helps skip over a specified number of rows in the result.
• Often used with LIMIT for pagination, allowing data to be loaded in manageable portions.
• Essential for database-driven applications needing paginated displays.

---

This setup of OFFSET and LIMIT is helpful for web applications where data is displayed in parts, improving both performance and user experience.