Thinking about using rownum in where clause: BEAWARE Series-I

First :

SELECT rnum, table_name FROM
(SELECT rownum rnum, table_name FROM user_tables)
WHERE rnum > 2;

Second:

SELECT table_name FROM user_tables
WHERE rownum > 2;

The first will return result whereas the second will zero rows.


The reason is described below:
However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behavior, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.
The bottom line is that conditions such as the following will work as expected.
WHERE rownum = 1;
WHERE rownum <= 10;
WHERE rownum <=3;
While queries with these conditions will always return zero rows.

WHERE rownum = 2;
WHERE rownum > 10;



Post Reference: Vikram Aristocratic Elfin Share