Using the SQL Row Limiting Clause: FETCH

Using the SQL Row Limiting Clause: FETCH

Overview of FETCH

The FETCH clause allows you to specify the number of rows to retrieve after applying an OFFSET. It provides a way to retrieve a subset of rows from a result set, which is useful for paginating through results or limiting the output to a specific number of rows.

Syntax: 

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET m ROWS FETCH NEXT n ROWS ONLY;

• OFFSET m ROWS: Skips the first m rows of the result set.
• FETCH NEXT n ROWS ONLY: Retrieves the next n rows after the OFFSET.

Basic Usage of FETCH

The FETCH clause is typically used in conjunction with the ORDER BY clause to ensure the rows are returned in a specific order.

Example 1: Limiting Rows

To retrieve the top 5 rows from a query: 

SELECT first_name, last_name
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

In this example:

• The query selects the first_name and last_name columns from the employees table.
• The ORDER BY salary DESC ensures that the rows are sorted by salary in descending order.
• FETCH FIRST 5 ROWS ONLY limits the result to the top 5 rows based on the specified ordering.

Pagination with FETCH and OFFSET

Pagination is a common use case for FETCH. It allows you to retrieve a specific page of results by skipping a set number of rows and then fetching the desired number of rows.

Example 2: Paginating Results

To get rows 11 to 20 from a query: 

SELECT first_name, last_name
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

In this example:

• OFFSET 10 ROWS skips the first 10 rows.
• FETCH NEXT 10 ROWS ONLY retrieves the next 10 rows after the skipped rows.
• This query effectively retrieves rows 11 through 20 from the ordered result set.

Combining FETCH with Other Clauses

You can use FETCH in combination with other SQL clauses to refine the results further.

Example 3: Filtering and Fetching Rows

To retrieve rows that meet a specific condition and then limit the results: 

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC
FETCH FIRST 5 ROWS ONLY;

In this example:

• The WHERE clause filters rows to include only those where the department is ‘Sales’.
• The ORDER BY hire_date DESC sorts the results by hire_date in descending order.
• FETCH FIRST 5 ROWS ONLY limits the result to the top 5 rows from this filtered and ordered result set.

Considerations and Best Practices

• Performance: Ensure that the ORDER BY clause is used with indexed columns when using FETCH to improve query performance.
• Pagination Queries: Use OFFSET and FETCH together for pagination to avoid issues like missing or duplicate rows when data changes between page requests.
• Consistency: The results can vary if the underlying data changes between requests. For consistent pagination, ensure the dataset is stable or use additional filtering criteria to maintain consistency.

Compatibility and SQL Standards

• The FETCH clause is part of the SQL:2008 standard and is supported by Oracle 12c and later.
• For earlier versions of Oracle, or other SQL databases that do not support the FETCH clause, you may need to use alternatives like ROWNUM or LIMIT (in MySQL).

Conclusion

The FETCH clause provides a powerful and flexible way to limit the number of rows returned by a query and to paginate through results. By combining FETCH with OFFSET, you can efficiently handle large datasets and provide a better user experience in applications that display paginated results.