MySQL FETCH

Introduction to MySQL FETCH

MySQL Fetch Clause statement is used to fetch a set of rows to retrieve some operational data in MySQL. We also need to know how to use the SQL OFFSET FETCH clauses to allow limits while fetching the number of rows from the table through a SQL query. You cannot use FETCH alone; it is used in conjunction with OFFSET for aggregation. Generally, these two clauses, OFFSET and FETCH, are possibilities of the ORDER clause, which permits the addition of a limit to the query for returning the number of records from a table in MySQL. We need to use these simultaneously while fetching rows from the table to allow proper query execution in MySQL. If you do not use the OFFSET and FETCH clauses with the ORDER BY clause, it will result in an error. Therefore, it is preferable to use them when implementing a query.

Syntax

The FETCH and OFFSET clauses are combined with SELECT and ORDER BY clauses to get a range of records from the result set. The syntax below defines the query code for OFFSET and FETCH clauses:

SELECT ColumnNames FROM TableName ORDER BY ColumnName OFFSET rows_to_be_skipped FETCH NEXT n ROWS ONLY; // Where n is a number of rows

Some points to know in a query:

ORDER BY ColumnNames[DESC/ASC]
OFFSET offset_rows_count
FETCH {either First or Next} fetch_rows_count ONLY

Explanation: The OFFSET argument in MySQL identifies the starting point for the rows to return from the query. The OFFSET query is responsible for skipping the number of rows before fetching the rows from the SQL query. Offset_rows_count can be specified by a constant, scalar, variable, or parameter greater than or equal to zero. The FETCH clause returns the number of records after the execution of the OFFSET clause.

Fetch_rows_count can be specified by a constant, scalar, variable, or parameter greater than or equal to zero. In an SQL query, it is necessary to use the OFFSET clause, but the FETCH clause can be an optional term. The First and Next terms are synonyms to add interchangeably, and the same applies to ASC and DESC keywords to sort the rows when fetched.

How does the FETCH clause work in MySQL?

We can look at the complete syntax for using MySQL FETCH with OFFSET to return the number of rows excluding the first rows and fetch the next rows from the table. This is basic query syntax to exclude first m rows.

SELECT ColumnNames FROM TableName ORDER BY ColumnNames OFFSET m ROWS FETCH NEXT p ROWS ONLY;

Again, you can use the following code to exclude m rows and fetch the next p rows from the table. This will fetch rows from (m+1) to (m+1+p) only.

SELECT ColumnNames FROM TableName ORDER BY ColumnNames OFFSET m ROWS FETCH NEXT p ROWS ONLY;

Examples to Implement MySQL FETCH

Here are the examples:

Example #1

Consider creating a table named Employees for a sample to explain examples with EmpID, Name, Phone, Address, and Salary with the below query.

Code:

CREATE TABLE Employees (EmpID int NOT NULL PRIMARY KEY, Name varchar(255) NOT NULL, Address varchar(255), Phone varchar(255), Salary varchar(255));

Also, entering some values like this:

Code:

INSERT INTO Employees (EmpID, Name, Address, Phone, Salary)VALUES
('1', 'Nikhil', 'Delhi', '9878906543', '8000'),
('2', 'Divya', 'Ranchi', '8990076543', '5000'),
('3', 'Ravi', 'Bareilly', '7789945765', '7000'),
('4', 'Anna', 'Noida', '9789945760', '4000'),
('5', 'Surbhi', 'Jaipur', '7800541123', '5500');

Output:

Example #2

For example, the query below will return all the employees from the table with Name and Address columns and sorted by Salary.

Code:

SELECT Name, Address FROM Employees ORDER BY Salary;

Output:

Example #3

Now, if we want to skip two rows of records and return the remaining, then use the OFFSET Clause as follows:

Code:

SELECT Name, Address FROM Employees ORDER BY Salary OFFSET 2 ROWS;

Output:

Example #4

This will provide the result set by skipping the first two rows and returning other rest of the rows from the table Employees. Again, to exclude the first two and get the result set with the next two rows, we must use FETCH and OFFSET with SELECT and ORDER BY clauses in MYSQL. This query limits the range of rows to be fetched from the table:

Code:

SELECT Name, Address FROM Employees ORDER BY Salary OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;

Output:

Explanation: This will print Name and Address columns from the 3^rd to 5^th fields of the table Employees, which is sorted according to Salary. Here the result table rows are sorted after the rows are fetched.

Example #5

Now, if we want to fetch the rows from the Employees table after sorting the rows list by Salary, then excluding the rows, and finally providing the fetched rows, we need to use the syntax in the query.

OFFSET (SELECT COUNT (*) FROM Employees)

Below is the query from which we will fetch the bottom two rows when ordered by Salary. In the result set obtained from the query list, we won’t retrieve the least or top salary paid to the employees along with their column names and addresses when sorted by salary. Then we can use ASC or DESC operators in MySQL.

The following query deals with the result set containing the top paid and least paid a salary to an employee where OFFSET and FETCH work together to fetch the required rows from the table Employees:

Code:

SELECT Name, Address FROM Employees ORDER BY Salary DESC OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;

Output:

Again, with ASC:

Code:

SELECT Name, Address FROM Employees ORDER BY Salary ASC OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;

Output:

Advantages of using FETCH in MySQL

Below are the advantages :

• It helps to fetch the desired number of records from the table.
• OFFSET with FETCH query in MySQL performs to skip the first set of rows.
• FETCH with ORDER BY let us order the number of rows either by ascending or descending sorting.
• The FETCH NEXT and OFFSET clauses return a specified window of rows from a table.
• FETCH NEXT with OFFSET supports effectively constructing pagination provisions.
• It gives support to interpret an SQL query and maintain database integrity.

Conclusion

We combine FETCH with OFFSET to limit rows from the table. MySQL FETCH with OFFSET helps MySQL manage many databases and their records to quickly fetch the rows to a limited range. FETCH returns the result set after the SQL execution to get the rows and skip the OFFSET rows count.

Recommended Articles

We hope that this EDUCBA information on “MySQL FETCH” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL Index
2. Working of MySQL DECODE()
3. MySQL INSTR()
4. MySQL BIN()