FETCH in SQL

The FETCH statement in SQL is commonly used to retrieve a subset of records from a result set, especially when working with large datasets, cursors, or rows that need to be retrieved incrementally. It is typically used in conjunction with SQL cursors to retrieve rows from a result set in a controlled, step-by-step manner.

In this article, we will discuss the FETCH command in SQL, including its usage, syntax, and practical examples, starting from basic concepts and moving to more advanced use cases.

FETCH in SQL

The SQL FETCH command is used to retrieve a subset of rows from a result set, typically after applying an ORDER BY clause and specifying an OFFSET to define the starting point. It is commonly used in pagination scenarios where you want to retrieve a limited number of rows at a time, such as top records, specific ranges, or sequential data fetching.

• With FETCH the OFFSET clause is mandatory. You are not allowed to use, ORDER BY … FETCH.
• You are not allowed to combine TOP with OFFSET and FETCH.
• The OFFSET/FETCH row count expression can only be any arithmetic, constant, or parameter expression that will return an integer value.
• With the OFFSET and FETCH clause, the ORDER BY is mandatory to be used. 

Syntax:

SELECT column_name(s)

FROM table_name

ORDER BY column_name

OFFSET start_row_number ROWS

FETCH NEXT number_of_rows ROWS ONLY;

Example of FETCH in SQL

Here, we will discuss the steps to implement the FETCH command in SQL. 

Let us consider a table is created based on marks of students in the class that contains data displayed below.

CREATE TABLE MarkList
(
    id int,
    name varchar(20),
    mathematics int, 
    physics int,
    chemistry int
);
INSERT INTO MarkList VALUES(501,'Surya',99,97,85);
INSERT INTO MarkList VALUES(502,'Charan',99,93,88);
INSERT INTO MarkList VALUES(503,'Sravan',91,98,94);
INSERT INTO MarkList VALUES(504,'Ram',92,99,82);
INSERT INTO MarkList VALUES(505,'Aryan',94,99,88);
INSERT INTO MarkList VALUES(506,'Sathwik',91,88,91);
INSERT INTO MarkList VALUES(507,'Madhav',90,97,89);

After inserting, the table will look like this.

Output:

To retrieve the names, IDs, and total marks of the top 3 students (sorted by the total marks in descending order), we can use the FETCH command with OFFSET and ORDER BY.

Query:

SELECT id, name, (mathematics + physics + chemistry) AS total
FROM MarkList
ORDER BY (mathematics + physics + chemistry) DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;

Output:

So, the SQL FETCH command is used to retrieve selected rows from a table sequentially. It's handy to use when you want to select a limited number of rows from an ordered set, like top 3, top 10, or bottom 3, etc.

Performance Considerations

While using FETCH can be very useful for limiting result sets, consider the following best practices:

• Ensure proper indexing: When using ORDER BY, make sure the columns involved in sorting are indexed for faster query execution.
  
• Use in pagination: It’s common to use FETCH in paginated APIs or web applications to display data in chunks (e.g., 10 rows per page).

Restrictions with SQL FETCH

• Mandatory ORDER BY: When using OFFSET and FETCH, the ORDER BY clause is required to ensure a consistent, predictable order of rows.
  
• Cannot be used with TOP: You cannot combine TOP with OFFSET and FETCH. Both mechanisms serve the same purpose of limiting rows, but they should not be mixed in the same query.
  
• Arithmetical Expressions: The number of rows specified in FETCH NEXT must be an integer expression (constant, arithmetic, or parameterized).

Conclusion

The SQL FETCH command is a powerful tool for retrieving a specific range of rows from a result set, often used in pagination and when dealing with large datasets. By using OFFSET and FETCH together with ORDER BY, you can efficiently retrieve ordered subsets of data, making it easier to implement features like "Top N records" or "Paginated results" in your SQL applications.