The basics of "ROWNUM"

ROWNUM is a function in Oracle, that returns a number for each row returned by a query. It returns an increasing value starting from 1.

For example, if we write :

select * from emp where rownum<=3. The oracle will do the following things:

1. Oracle executes the query and fetches the first row. It assigns it number 1.
2. Oracle checks whether it has reached to number 3. If no, it returns that row(since the rownum is less than 3) and if yes, it does not return the row.
3. Oracle then, fetches the next row and advances the rownum.
4. Oracle goes to step 2.

It is to be noted that Oracle's ROWNUM are assigned after each row is fetched.

If a user writes :

select * from emp where rownum=3.
No rows will be returned. The reason is that after fetching the first row, oracle will assign it number 1. It now checks whether the number is 3. Since its not, it will discard the row. It will then fetch next row. It will again assign it number 1 but this time also it will not return any row.
It will , hence, not return any row since the number will never be 3 in this case.

However, the query 'select * from emp where rownum=1', will return the first row since after fetching the first row, oracle assigns number 1 to that row which matches the condition.

This clarifies that the equality operator is not generally used with rownum.