SQL | OFFSET-FETCH Clause

The OFFSET-FETCH clause in SQL is used in conjunction with the ORDER BY clause to provide a mechanism for retrieving a subset of rows, which can be helpful for pagination scenarios. This clause is especially relevant for databases that do not have a built-in LIMIT keyword (like MySQL), with SQL Server being a notable user of the OFFSET-FETCH construct.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET n ROWS
FETCH NEXT m ROWS ONLY;

n: The number of rows to skip.
m: The number of rows to fetch after the offset.

Example:

Imagine you want to retrieve the next 10 products after skipping the first 50 (which could represent, for instance, the 6th page of a paginated list if each page shows 10 items):

SELECT product_id, product_name, price
FROM products
ORDER BY product_id
OFFSET 50 ROWS
FETCH NEXT 10 ROWS ONLY;

Notes:

Using ORDER BY: For the OFFSET-FETCH clause to work, you must use an ORDER BY clause. This is because the ordering of rows in a relational database is inherently not guaranteed without an explicit order being specified.
Performance: When implementing pagination, especially in large datasets, be mindful of the performance implications. Fetching rows using a large offset can be slow in some databases. Always ensure you have proper indexing in place and consider other optimizations, if necessary.
Alternative to LIMIT: In databases like MySQL and PostgreSQL, you'd typically use the LIMIT and OFFSET clauses to achieve similar functionality. The OFFSET-FETCH is more standard with SQL Server.
FETCH without OFFSET: If you want to use FETCH without OFFSET, you still need to specify OFFSET 0 ROWS.
Compatibility: While the OFFSET-FETCH clause is part of the SQL:2011 standard, not all databases support it. Ensure it's compatible with your RDBMS before using it. If not, there are often other mechanisms for pagination specific to that RDBMS.

How to use OFFSET and FETCH in SQL:
- OFFSET skips a specified number of rows from the beginning.
- FETCH retrieves a specified number of rows after the OFFSET.
```
SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;
```
Pagination with OFFSET-FETCH in SQL:
- Achieve result set pagination by specifying the number of rows to skip (OFFSET) and the number of rows to retrieve (FETCH).
```
SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
```
SQL OFFSET-FETCH vs. LIMIT and OFFSET:
- Different databases may use different syntax. LIMIT and OFFSET are commonly used in some database systems like PostgreSQL and MySQL.
```
-- PostgreSQL or MySQL
SELECT column1, column2
FROM example_table
ORDER BY column1
LIMIT 10 OFFSET 20;
```

Paging through SQL query results using OFFSET-FETCH:

Use OFFSET and FETCH for paging through large result sets.

DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 3;

SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Using OFFSET-FETCH for result set pagination:

Implement pagination for a user interface where only a subset of data is displayed at a time.

DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 1;

SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

SQL OFFSET-FETCH in SELECT statement:
- Use OFFSET and FETCH within a SELECT statement to control result set size.
```
SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;
```

Dynamic pagination with OFFSET-FETCH in SQL:

Adjust OFFSET and FETCH values dynamically based on user input or application logic.

DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 1;

SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

OFFSET-FETCH for efficient data retrieval in SQL:
- Use OFFSET and FETCH for efficient data retrieval, especially when dealing with large datasets.
```
SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET 100000 ROWS
FETCH NEXT 1000 ROWS ONLY;
```

Limiting rows with OFFSET-FETCH in SQL Server:

Limit the number of rows returned from a query.

SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

OFFSET-FETCH in combination with ORDER BY in SQL:
- Use ORDER BY in conjunction with OFFSET and FETCH for consistent result ordering.
```
SELECT column1, column2
FROM example_table
ORDER BY column1 DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;
```

Handling large datasets with OFFSET-FETCH in SQL:

Efficiently retrieve and display a subset of data from large tables.

DECLARE @PageSize INT = 100;
DECLARE @PageNumber INT = 1;

SELECT column1, column2
FROM large_table
ORDER BY column1
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;