Introduction to rowid and rownum in oracle

[Overview]

Those who have just been familiar with oracle may often be confused by the word rowid and rownum. Figuring out these two guys will be of great help for us to write SQL statements. I will introduce them as an example below, let's briefly talk about the differences between them.

 [Comparison]

Rowid and rownum are both pseudo columns in oracle, but they still have essential differences:

Rowid: a physical address used to locate the data location in a data table. It is unique and will not change.

Rownum: it is a logical number assigned to each row based on the query result set. The query result is different, and rownum is naturally different.

For the same record, the query conditions are different, and rownum will be different, but rowid will remain unchanged.

Example: Query all employees of a company

Select rowid, rownum, empno, ename from emp;

Example: query the name of a company employee that contains 'S'

Select rowid, rownum, empno, ename from emp where ename like '% S % ';

Careful students can easily find that the rowid and rownum of a row of records have not changed. For example, an employee named 'Smith.

[Trap]

1. rownum only supports symbols <, <=, and ,! =, Not supported>,> =, =, between... and

Select * from emp where rownum> 10 -- no result

Select * from (select rownum row_num, e. * from emp e) emp_temp where emp_temp.row_num> 10; -- Obtain ten records later

It is explained that rownum is a pseudo column added to the result set, that is, a column added after the result set is checked (emphasis: a result set is required ). to put it simply, rownum is the serial number that matches the condition result. It is always ranked from 1, so the result you select cannot be 1, but there is a value greater than 1.

With the above knowledge, it is not difficult to explain the following phenomena:

① Why select * from emp where rownum! = 10 always return the first nine records?

Because after the result set is queried, 9th records are displayed, and the subsequent records are all! = 10.

② Why can't rownum> 1 find a record while rownum> 0 or rownum> = 1 always show all records?

Because rownum is added after the result set is queried, it always starts from 1.

③ Why between 1 and 10 or between 0 and 10 can be found, but between 2 and 10 cannot be found?

The reason is the same as above. If rownum = 1 is missing, it cannot exist like a castle in the air.

[Application]

What's the use of these two buddies? Hey, don't worry. Let's get the main dish below...

Problem: get the top three employees

Analysis: If it is SQL server, we can use the top keyword. rownum can also be used in oracle.

 select *   from (select * from emp order by sal desc)  where rownum <= 3  order by rownum asc