ROWNUM vs ROW_NUMBER() in Oracle SQL: Key Differences and Use Cases (Basic 1 - 22)

 

ROWNUM and ROW_NUMBER() are two pseudocolumns in Oracle SQL that can be used to generate sequential numbers for rows in a result set. However, there are some key differences between the two, and they have different use cases.

ROWNUM

ROWNUM is a pseudocolumn that returns the sequential number of a row in a result set, regardless of the order of the rows. ROWNUM is calculated before the ORDER BY clause is applied, so the values in the ROWNUM column may not be in sequential order if the result set is ordered.

ROWNUM is useful for limiting the number of rows returned by a query, or for referencing rows within a fetched result set. For example, the following query will return the first 10 rows from the EMP_TEST table:

SQL

SELECT * FROM EMP_TEST WHERE ROWNUM <= 10;

ROW_NUMBER()

ROW_NUMBER() is an analytic function that returns the sequential number of a row in a result set according to its ordering within a group of rows. ROW_NUMBER() is calculated after the ORDER BY clause is applied, so the values in the ROW_NUMBER() column will always be in sequential order.

ROW_NUMBER() is useful for generating a sequence of numbers for rows after they have been sorted, or for limiting the number of rows returned from a sorted result set. For example, the following query will return the top 10 employees by salary:

SQL

SELECT * FROM EMP_TEST ORDER BY SAL DESC ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) <= 10;

Key differences between ROWNUM and ROW_NUMBER()

The following table summarizes the key differences between ROWNUM and ROW_NUMBER():

Feature	ROWNUM	ROW_NUMBER()
Type	Pseudocolumn	Analytic function
Permanence	Temporary	Permanent
Purpose	To limit the number of rows returned by a query or to reference rows within a fetched result set	To generate a sequence of numbers for rows after they have been sorted, or to limit the number of rows returned from a sorted result set
Ordering	Calculated before the ORDER BY clause is applied	Calculated after the ORDER BY clause is applied
WHERE clause	Can be used in the WHERE clause	Cannot be used in the WHERE clause

Use cases

ROWNUM and ROW_NUMBER() are both useful for generating sequential numbers for rows in a result set, but they have different use cases. ROWNUM is useful for limiting the number of rows returned by a query, or for referencing rows within a fetched result set. ROW_NUMBER() is useful for generating a sequence of numbers for rows after they have been sorted, or for limiting the number of rows returned from a sorted result set.

Here are some specific examples of when to use each one:

ROWNUM

• To limit the number of rows returned by a query, such as when fetching the first 10 rows or the last 10 rows.
• To reference rows within a fetched result set, such as when using the FIRST_VALUE() or LAST_VALUE() analytic functions.

ROW_NUMBER()

• To generate a sequence of numbers for rows after they have been sorted, such as when ranking employees by salary or customers by order value.
• To limit the number of rows returned from a sorted result set, such as when fetching the top 10 employees by salary or the bottom 10 customers by order value.

Conclusion

ROWNUM and ROW_NUMBER() are both powerful tools that can be used to manipulate and access data in Oracle SQL. By understanding the differences between the two, you can choose the right tool for your needs.