Oracle FETCH Clause

Summary: in this tutorial, you will learn how to use the Oracle FETCH clause to limit the rows returned by a query.

Introduction to Oracle FETCH clause #

Some RDBMS, such as MySQL and PostgreSQL have the LIMIT clause that allows you to retrieve a subset of rows generated by a query.

See the following products and inventories tables in the sample database.

The following query uses the LIMIT clause to get the top 5 products with the highest inventory level:

SELECT
  product_name,
  quantity
FROM
  inventories
  INNER JOIN products USING (product_id)
ORDER BY
  quantity DESC
LIMIT
  5;Code language: SQL (Structured Query Language) (sql)

In this example:

• First the ORDER BY clause sorts the products by stock quantity in descending order.
• Then, the LIMIT clause returns the first 5 products with the highest stock quantity.

Oracle Database does not have the LIMIT clause. However, since the 12c release, it provided a similar but more flexible clause called the row limiting clause.

By using the row-limiting clause, you can rewrite the query that uses the LIMIT clause above as follows:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

In this statement, the row limiting clause is:

FETCH NEXT 5 ROWS ONLYCode language: SQL (Structured Query Language) (sql)

Similar to the statement that uses LIMIT clause above, the row limiting clause returns the top 5 products with the highest inventory level.

Oracle FETCH clause syntax #

The following illustrates the syntax of the row limiting clause:

[ OFFSET offset ROWS]
FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]Code language: SQL (Structured Query Language) (sql)

 OFFSET clause #

The OFFSET clause specifies the number of rows to skip before fetching rows. The OFFSET clause is optional. If you skip it, then the offset is 0, and FETCH clause will start fetching from the first row.

The offset must be a number or an expression that evaluates to a number. It follows these rules:

• If the offset is negative, Oracle treats it as 0.
• If the offset is NULL or greater than the number of rows returned by the query, then the statement returns no rows.
• If the offset includes a fraction like 5.5, then the Oracle truncates the fractional portion.

 FETCH clause #

The FETCH clause specifies the number of rows or percentage of rows to return.

For semantic clarity purposes, you can use the keyword ROW instead of ROWS, FIRST instead of  NEXT. For example, the following clauses behave the same:

FETCH NEXT 1 ROWS
FETCH FIRST 1 ROWCode language: SQL (Structured Query Language) (sql)

 ONLY | WITH TIES #

The ONLY returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST).

The WITH TIES returns additional rows with the same sort key as the last row fetched.

Note that if you use WITH TIES, you must specify an ORDER BY clause in the query. If you don’t, the query will not return the additional rows.

Since Oracle stores rows in a table in an unspecified order, you should always use the FETCH clause with the ORDER BY clause to get a predictable result.

Oracle FETCH clause examples #

Let’s take an example of using the FETCH clause.

Selecting the top N rows example #

The following statement uses the FETCH clause to retrieve the top 10 products with the highest quantity:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 10 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

How the query works:

• First, the ORDER BY clause sorts rows by quantity from high to low.
• Second, the FECTCH clause retrieves the top 10 rows.

The following statement uses the FETCH clause to retrieve the top 3 products with the highest standard cost in category 1:

SELECT
  product_name,
  quantity
FROM
  inventories
  INNER JOIN products USING (product_id)
WHERE
  category_id = 1
ORDER BY
  quantity DESC
FETCH NEXT 3 ROWS ONLY;

Try it

Output:

Selecting WITH TIES example #

The following query uses the FETCH clause to with the WITH TIES option to retrieve the top 10 rows with ties:

SELECT
  product_name,
  quantity
FROM
  inventories
  INNER JOIN products USING (product_id)
ORDER BY
  quantity DESC
FETCH NEXT
  10 ROWS
WITH
  TIES;Code language: SQL (Structured Query Language) (sql)

Try it

Although the statement tells Oracle to return 10 rows, it returns 12 rows.

The reason is the WITH TIES option, and there are two additional rows with the same quantity (273).

Limit by percentage of rows #

The following query uses the FETCH clause to retrieve the top 5% products with the highest quantity:

SELECT
  product_name,
  quantity
FROM
  inventories
  INNER JOIN products USING (product_id)
ORDER BY
  quantity DESC
FETCH FIRST
  5 PERCENT ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

The inventories table has 1,112 rows. Therefore, 5% of 1,112is 55.6 which is rounded up to 56 rows.

Using the OFFSET example #

The following query uses the OFFSET clause to skip the first 10 rows with the highest quantity and return the next 10 ones:

SELECT
  product_name,
  quantity
FROM
  inventories
  INNER JOIN products USING (product_id)
ORDER BY
  quantity DESC
OFFSET
  10 ROWS
FETCH NEXT
  10 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

How the query works:

• First, the ORDER BY clause sorts rows by quantity from high to low.
• Second, the OFFSET clause skips the first 10 rows.
• Third, the FECTCH clause retrieves the next 10 rows.

Summary #

• Use the Oracle FETCH clause to limit the number of rows returned by a query.

Quiz #