SQL FETCH

In modern application development, you rarely want to dump thousands of database records onto a user's screen all at once. The FETCH clause is a powerful tool used to retrieve a specific slice of a result set. While it is most famous for powering "Pagination" (like clicking through Page 1, 2, or 3 on a search engine), it is also essential for performance optimization and report generation.

The FETCH clause works as a filter that limits the number of rows returned by a query. To ensure you get the exact data you expect, it is almost always used in conjunction with ORDER BY and OFFSET.

Basic Syntax of FETCH

The standard syntax for implementing row limiting involves three main parts: sorting, skipping, and fetching.

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

• ORDER BY: This is critical. Without it, SQL doesn't guarantee the order of rows, which means your "first 10" rows could change every time you run the query.
• OFFSET: This tells the database to skip a specific number of rows before it starts returning data. If you want to start from the very beginning, you use OFFSET 0 ROWS.
• FETCH NEXT: This defines the maximum number of rows to return in the result set.

Key Features of FETCH Clause

1. Retrieving a Limited Number of Rows

If you only need a specific number of records for example, the "Top 5" most recent orders you use FETCH NEXT after sorting the data by date.

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

This query sorts all customers alphabetically and hands back only the first 5 names on the list.

2. Combining FETCH with OFFSET

The real power of FETCH is unlocked when combined with OFFSET. This allows you to jump deep into a dataset without loading everything that came before it.

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

• The database sorts the list.
• It skips the first 10 customers.
• It returns only customers 11 through 15.

3. Pagination Example

In a real-world web application, you usually calculate the OFFSET based on the current page number and the "Page Size" (how many items appear per page).
Page 1 (Items 1-10):

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

Page 2 (Items 11-20):

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

Example Queries

1. Fetch Top 5 Most Expensive Products

Imagine you are building a "Featured Products" section for an e-commerce store. You want the highest prices shown first.

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

By using DESC (descending order), we ensure the most expensive items are at the top before we FETCH the first 5.

2. Skip First 5 Rows and Fetch the Next 5

This is useful if you have a "Headline" area showing the first 5 items and a "More Stories" list that needs to show everything after those first 5.

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

3. Paginating Customers by Geography

Often, you need to group data logically. Here we fetch customers page-by-page, sorted by their city location.

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 is the industry-standard way to handle row limiting and pagination. By combining it with OFFSET and ORDER BY, you can create efficient, user-friendly interfaces that load data in manageable chunks. While many developers are used to the LIMIT keyword from MySQL, learning FETCH is essential for writing "future-proof" SQL that works across professional database systems like SQL Server and Oracle.