Menu
I executed two sample queries on AdventureWorks2016 and gave me the same result. When should I use NEXT or FIRST keyword then?
select LastName + ' ' + FirstName
from person.person
order by LastName asc OFFSET 10 rows **Fetch next** 10 rows only
select LastName + ' ' + FirstName
from person.person
order by LastName asc OFFSET 10 rows **Fetch first** 10 rows only
656
The fetch-next statement fetches sequentially forward beginning at the current position (cursor position) in the result set and sets the cursor position to the last row fetched. After the execution of a query (SQL SELECT command), the cursor position is set to the first row of the result set.
FETCH: It is an optional clause that provides the number of rows we want to return after the OFFSET in a query. We cannot use it without OFFSET. Its value cannot be negative similar to OFFSET. Therefore, it should always be greater than or equal to zero; otherwise, it will throw an error.
OFFSET and FETCH Clause are used in conjunction with SELECT and ORDER BY clause to provide a means to retrieve a range of records. OFFSET. The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records.
The fetch first clause, which can be combined with the result offset clause if desired, limits the number of rows returned in the result set. The fetch first clause can sometimes be useful for retrieving only a few rows from an otherwise large result set, usually in combination with an ORDER BY clause.
FETCH FIRST and FETCH NEXT do exactly the same thing. The reason both exist because of the preceding OFFSET clause. Using the word FIRST combined with OFFSET can be confusing to a human reader:
SELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY; -- Does this mean rows 6 to 10, or 1 to 5?
Whereas:
SELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY; -- Now it's clear!
SqlFiddle example here.
115
There is no difference. The SQL standard simply allows both, maybe to bridge differences between syntaxes, or to allow you the choice to write 'fluent English' queries.
This similar as to why the standard allows you to write:
fetch first row
fetch first rows
fetch first 1 row
fetch first 1 rows
(and variants with next) which will all fetch a single row. That this also allows you to write grammatically incorrect sentences like fetch first 2 row is taken for granted.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
