Fetch clause in SQL

Today, we are going to discuss about the Fetch clause and some of the unknow keywords used along with it. Fetch clause limits the number of rows returned from the result set.

Trimbakeshwar Temple, Nasik

Fetch clause is used in the situations where you want to load only some specific number of records. So the benefit of using Fetch clause is the speed in which the records will be retrieved is improved, as you are not loading all the records from the table. Let’s first undertstand the basic syntax.

Syntax:-

<Select Statement> FETCH first/next "number of row(s)" only;

Here number of row(s) can be any integer number. You can have First or Next in your query, both will give you the same result. Below is the sample data on which all the examples will be shown in this article.

Example:-

SELECT * FROM public."Employee" 
ORDER BY "Name" FETCH FIRST 1  ROW ONLY;

Output:-

Output with first record

Here in the above example we are fetching the first employee whose name comes first in ascending order. We can can change the integer number 1 with any other integer number.

Fetch with Offset:-

We can also skip some number of records and then fetch the records from the table. We can use OFFSET keyword before Fetch clause to skip the records.

Syntax :-

<SELECT STATEMENT> OFFSET <Number of Records> <FETCH CLAUSE>

In the above syntax you can see that before fetch clause we can use OFFSET keyword with number of records that we want to skip. Let’s check the example below.

select * from public."Employee"  ORDER BY "Name"  
offset 2 FETCH first 1 row only;

Output:-

Output

Here in the above example we are skipping two rows from the top of the record and showing third record. This can be used in the scenario where you want to implement pagination in the application.

Fetch With Ties:-

We can use WITH TIES keyword after FETCH clause to get the duplicate records which are matching the fetched records. Let’s check the example below.

SELECT * FROM public."Employee" ORDER BY "Name" 
FETCH FIRST 1 ROW with ties;

Output:-

Output

Here in the above example you can see that we got two records instead of 1 record. If you see the output the name of the employee is same as the first record so it retrieved all the duplicate names. As we are doing order by on name column so that’s why it has checked the duplication on the basis on name column only. Order by clause is mandatory incase if you are planning to use With ties keyword in your query.

It is to be noted that we can use limit keyword as well which will work as Fetch clause but Fetch clause is SQL standard clause which works in all the SQL based Database language.

Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.

Bhagavad Gita: Chapter 17, Verse 28

अश्रद्धया हुतं दत्तं तपस्तप्तं कृतं च यत् |
असदित्युच्यते पार्थ न च तत्प्रेत्य नो इह || 28||

Translation

O son of Pritha, whatever acts of sacrifice, charity, or penance are done without faith, are termed as “Asat.” They are useless both in this world and the next.