FETCH ONLY Clause

The FETCH ONLY clause is part of Oracle SQL's pagination and row-limiting syntax, introduced in Oracle 12c (12.1). It is used to specify the exact number of rows you want to return from a query, effectively limiting the number of rows that the query returns. While it's primarily used in conjunction with the ORDER BY and OFFSET clauses, FETCH ONLY itself is a key component when working with limited results.

1. Basic Syntax of FETCH ONLY

The syntax for using the FETCH ONLY clause is:

SELECT column1, column2, ...

FROM table_name

ORDER BY column_name

FETCH FIRST n ROWS ONLY;

Where:

·        FETCH FIRST n ROWS ONLY limits the result set to the first n rows, where n is the number of rows you want to fetch.

·        ORDER BY is required when using the FETCH ONLY clause to guarantee a consistent order of rows being fetched.

Example: Fetching the First 10 Rows

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH FIRST 10 ROWS ONLY;

In this example, the query retrieves the first 10 rows from the employees table, ordered by the hire_date.

2. Differences Between FETCH FIRST and FETCH NEXT

·        FETCH FIRST is used to retrieve the first n rows from the result set. It can be used to get a fixed number of rows from the top of the query result.

·        FETCH NEXT is typically used in conjunction with OFFSET to fetch a specific range of rows, often for pagination.

Example of FETCH FIRST:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH FIRST 10 ROWS ONLY;

This will return the first 10 rows of employees ordered by hire_date.

Example of FETCH NEXT with OFFSET for Pagination:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

This will skip the first 10 rows and return the next 10 rows in the result set.

3. FETCH FIRST vs LIMIT in MySQL

In other databases like MySQL, the LIMIT clause is used to limit the number of rows returned. Oracle uses FETCH FIRST (and FETCH NEXT), which serves a similar purpose but is more standardized as part of the SQL:2008 standard.

MySQL Example:

SELECT * FROM employees

ORDER BY hire_date

LIMIT 10;

Oracle Example:

SELECT * FROM employees

ORDER BY hire_date

FETCH FIRST 10 ROWS ONLY;

Both of these queries will return the first 10 rows ordered by hire_date.

4. FETCH FIRST with ALL ROWS ONLY

You can also use FETCH ALL ROWS ONLY to return all rows in the result set, similar to how a query would behave without the FETCH clause, but it allows you to explicitly write it.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH ALL ROWS ONLY;

This query will return all the rows from the employees table, ordered by hire_date, without any row limit.

5. Pagination with FETCH FIRST

While FETCH FIRST alone will give you a fixed number of rows from the top of the result set, it is often used with OFFSET to implement pagination. For example, if you are building a web application or report where you need to show results across multiple pages (e.g., 10 rows per page), the combination of OFFSET and FETCH NEXT is used.

Example of Pagination:

Page 1 (rows 1–10):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH FIRST 10 ROWS ONLY;

Page 2 (rows 11–20):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Page 3 (rows 21–30):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Here, the OFFSET clause is used to skip the number of rows corresponding to the previous page, and FETCH NEXT is used to get the next set of rows.

6. Handling Different Row Limitations with FETCH ONLY

6.1. Fetching a Single Row

If you want to fetch only the first row of a result set, you can use the FETCH FIRST 1 ROWS ONLY clause.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH FIRST 1 ROWS ONLY;

This query will return only the first row based on the hire_date column.

6.2. Fetching Top N Rows Based on a Condition

You can also use FETCH FIRST in combination with a WHERE clause to filter the data and then return the top n rows.

Example:

SELECT employee_id, first_name, last_name

FROM employees

WHERE department_id = 10

ORDER BY hire_date

FETCH FIRST 5 ROWS ONLY;

This query returns the first 5 employees who belong to department 10, ordered by their hire date.

7. FETCH FIRST with ROWNUM or ROW_NUMBER()

You can achieve similar results as FETCH FIRST using ROWNUM (for Oracle versions before 12c) or ROW_NUMBER() in combination with a subquery. However, FETCH FIRST is more concise and easier to understand.

Example Using ROWNUM (Pre-12c):

SELECT *

FROM (SELECT employee_id, first_name, last_name

      FROM employees

      ORDER BY hire_date)

WHERE ROWNUM <= 10;

Example Using ROW_NUMBER() (Pre-12c):

SELECT employee_id, first_name, last_name

FROM (

  SELECT employee_id, first_name, last_name,

         ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num

  FROM employees

)

WHERE row_num <= 10;

Both methods are valid but less efficient and readable than FETCH FIRST, which is simpler and directly part of the SQL standard.

8. Limitations and Considerations

·        ORDER BY Is Required: Whenever you use FETCH FIRST, you must include an ORDER BY clause to ensure that the result set is ordered predictably. Without it, the rows returned may not follow any specific order, making the results unreliable.

·        Performance Considerations: The FETCH FIRST clause does not affect the query execution plan drastically but can still introduce inefficiencies if the dataset is large. This is because the database must still scan the entire dataset before returning the limited number of rows, especially if no indexes are available for the sorting column.

·        Pagination Efficiency: Using FETCH FIRST is an efficient way to limit the rows returned, but for large datasets with deep pagination, it may become less efficient. If you need to paginate large datasets (e.g., retrieving page 100 of results), consider using keyset pagination or seek pagination techniques.

9. Conclusion

The FETCH FIRST clause is a powerful tool for limiting the number of rows returned by a query. It is most often used in conjunction with the ORDER BY clause to ensure that the query returns a predictable subset of rows. By providing a clear and standardized way to limit rows, it simplifies pagination, makes the queries more readable, and ensures efficient data retrieval in Oracle SQL 12c and later.