Introduction to MySQL FETCH
MySQL Fetch Clause statement is used to fetch a set of rows to retrieve some kind of operational data in MySQL. We also need to know how we can use the SQL OFFSET FETCH clauses to allow limits while fetching the number of rows from the table through a SQL query. FETCH cannot be operated by itself; it is used in aggregation with OFFSET. Generally, these two clauses OFFSET and FETCH are possibilities of the ORDER clause which permits to add 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 OFFSET and FETCH clauses together are not used with the ORDER BY clause then, it will produce an error. So, it is preferred to use them for implementing a query.
Syntax
The FETCH and OFFSET clauses are combined together to be used with SELECT and ORDER BY clause 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 to skip the number of rows before starting to fetch the rows from the SQL query. Offset_rows_count can be specified by a constant, any scalar, variable, any parameter greater than or equal to zero. The FETCH clause is used to return the number of records after the execution of the OFFSET clause.
Fetch_rows_count can be specified by a constant, any scalar, variable, any 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 respectively to add them interchangeably and the same thing for 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 the MySQL FETCH with OFFSET to return the number of rows excluding 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;
Now, again you can use the following code to exclude m rows and fetch 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
Below are examples mentioned :
Example #1
Let us consider to create 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 2 rows of records and return the remaining then, use the OFFSET Clause as follows:
4.5 (2,670 ratings)
View Course
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 2 rows and returning other rest of the rows from the table Employees. Again, to exclude the first 2 and get the result set with the next two rows, we will have to 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 3rd to 5th fields of table Employeeswhich 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 Employees table after sorting the rows list by Salary and 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 2 rows when ordered by Salary. Suppose, we won’t get the least or top salary paid to the employees from the column names and address as result set from the query list when sorted by salary, then we can use ASC or DESC operators in MySQL.
The following query deals with the result set containing the most top paid and least paid salary to an employee where OFFSET and FETCH works 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 form 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.
- FETCH NEXT together with OFFSET returns a specified window of rows forms a table.
- FETCH NEXT with OFFSET supports effectively for constructing pagination provisions.
- It gives support to interpret an SQL query and maintain database integrity.
Conclusion
The FETCH is used in combination to OFFSET to provide a limit of rows from the table. MySQL FETCH with OFFSET helps MySQL to manage a large number of databases and their records to quickly fetch the rows to a limit range. FETCH returns the result set after the SQL execution to get the rows along with skipping OFFSET rows count.
Recommended Articles
This is a guide to MySQL FETCH. Here we discuss an introduction to MySQL FETCH, syntax, how does it work, examples, and advantages. You can also go through our other related articles to learn more –
