SQL FETCH

The FETCH clause in SQL is used to retrieve a specific number of rows from a result set, typically for pagination. It is often used with the OFFSET clause and works alongside ORDER BY to control which rows are returned.

 

Basic Syntax of FETCH

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

• OFFSET: Skips a specific number of rows.
• FETCH NEXT: Specifies how many rows to return after skipping rows.
• ORDER BY: Ensures the rows are sorted before applying OFFSET and FETCH.

 

Key Features of FETCH Clause

1. Retrieving a Limited Number of Rows

The FETCH clause allows you to specify how many rows to return after sorting.

SELECT name, city
FROM customers
ORDER BY name
FETCH NEXT 5 ROWS ONLY;

This query returns the first 5 rows sorted by the name column.

2. Combining FETCH with OFFSET

You can combine FETCH with OFFSET to skip rows and retrieve specific rows for pagination.

SELECT name, city
FROM customers
ORDER BY name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

• This skips the first 10 rows and fetches the next 5 rows.

3. Pagination Example

Using OFFSET and FETCH, you can implement pagination to retrieve different pages of results.
Page 1:

SELECT name, city
FROM customers
ORDER BY name
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Page 2:

SELECT name, city
FROM customers
ORDER BY name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

• Page 1 starts from row 0 and fetches 10 rows.
• Page 2 skips the first 10 rows and fetches the next 10 rows.

 

Example Queries

1. Fetch Top 5 Most Expensive Products

SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH NEXT 5 ROWS ONLY;

This retrieves the top 5 products sorted by price in descending order.

2. Skip First 5 Rows and Fetch the Next 5

SELECT product_name, price
FROM products
ORDER BY price
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

This skips the first 5 rows and returns the next 5 rows sorted by price.

3. Paginating Customers

First Page:

SELECT name, city
FROM customers
ORDER BY city
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Second Page:

SELECT name, city
FROM customers
ORDER BY city
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Differences Between FETCH and LIMIT

 

Summary

The FETCH clause in SQL, when used with OFFSET and ORDER BY, provides a powerful way to retrieve a specific number of rows for tasks like pagination. It is part of the SQL standard and works well in SQL Server and PostgreSQL. It allows for better control of row retrieval compared to LIMIT.