SQL Pesudo Columns | Rowid |Rownum | Oracle SQL | SQL

 

Pseudo Columns

1. ROWID:

•       ROWID is nothing but the physical memory location on which that data/row is stored. ROWID basically returns address of row.

•       ROWID uniquely identifies row in database.

•       ROWID is combination of data object number, data block in data file, position of row and datafile in which row resides.

•       ROWID is 16 digit hexadecimal number whose datatype is also ROWID.

Syntax:         SELECT ROWID, column1, column2, ...

FROM table_name

WHERE condition:

      Example:    SELECT ROWID, employee_id, first_name, last_name

                         FROM employee 

                         WHERE department_id = 30;

2.ROWNUM:

•       ROWNUM is magical column in Oracle which assigns the sequence number to the rows retrieves in the table.

•       To limit the values in the table you can use rownum pseudo column.

•       ROWNUM is nothing but logical sequence number given to the rows fetched from the table.

•       ROWNUM is logical number assigned temporarily to the physical location of the row.

•       You can limit the values in the table using rownum.

Syntax:  SELECT ROWNUM, column1, column2, ...

              FROM (

              SELECT column1, column2, ...

               FROM table_name

               WHERE condition

               ORDER BY some_column

                )

               WHERE ROWNUM <= n;