EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » MySQL FETCH

MySQL FETCH

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL FETCH1

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:

MySQL FETCH2

Example #3

Now, if we want to skip 2 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:

OFFSET Clause

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;

MS SQL Training (16 Courses, 11+ Projects)

4.9

Oracle Training (14 Courses, 8+ Projects)

4.8

PL SQL Training (4 Courses, 2+ Projects)

4.7

Output:

ORDER BY clauses

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:

MySQL FETCH5

Again, with ASC:

Code:

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

Output:

with ASC

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 –

  1. MySQL Index
  2. MySQL DECODE()
  3. MySQL INSTR()
  4. MySQL BIN()
0 Shares
Share
Tweet
Share