Pseudo Columns in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Pseudo Columns in Oracle with Examples

In this article, I am going to discuss Pseudo Columns in Oracle with Examples. Please read our previous article where we discussed Multiple Column Subquery in Oracle with Examples.

What are Pseudo Columns in Oracle?

The pseudo columns are just like table columns. There are two types of Pseudo columns available in Oracle. They are as follows.

1. ROWID
2. ROWNUM

Example to understand Pseudo Columns in Oracle

We are going to use the following Employee table to understand Pseudo Columns in Oracle with Examples.

Please use the below SQL Script to create and populate the Employee table with the required sample data.

CREATE TABLE EMPLOYEE
(	
        EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO EMPLOYEE VALUES (7369, 'SMITH', 'CLERK',      7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES (7521, 'WARD', 'SALESMAN',    7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMPLOYEE VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

ROWID Pseudo Column in Oracle:

1. A new row inserts into a table
2. Unique id address/number for each row-wise.
3. Store in database

Example:

SELECT ROWID, ENAME FROM EMPLOYEE;
When you execute the above query, you will get the following output.

Example:

SELECT ROWID, ENAME, DEPTNO FROM EMPLOYEE WHERE DEPTNO=10;
When you execute the above query, you will get the following output.

Example:

SELECT MIN(ROWID) FROM EMPLOYEE;
When you execute the above query, you will get the following output.

Example:

SELECT MAX(ROWID) FROM EMPLOYEE;
When you execute the above query, you will get the following output.

How to delete multiple duplicate rows except for one duplicate row from a table?

Whenever we want to delete multiple duplicate rows except for one duplicate row from a table then we use the “ROWID” pseudo column. Let us understand this with an example. We are going to use the following Test table.

Please use the below SQL Script to create and populate the Test table with the required sample data.

CREATE TABLE TEST
(
    SNO INT,
    NAME VARCHAR2(10)
);

INSERT INTO TEST VALUES (10, 'A');
INSERT INTO TEST VALUES (10, 'A');
INSERT INTO TEST VALUES (10, 'A');
INSERT INTO TEST VALUES (20, 'B');
INSERT INTO TEST VALUES (20, 'B');
INSERT INTO TEST VALUES (30, 'C');
INSERT INTO TEST VALUES (30, 'C');
INSERT INTO TEST VALUES (30, 'C');
INSERT INTO TEST VALUES (40, 'D');
INSERT INTO TEST VALUES (40, 'D');
INSERT INTO TEST VALUES (50, 'E');
INSERT INTO TEST VALUES (50, 'E');
INSERT INTO TEST VALUES (50, 'E');

Example:

Delete multiple duplicate rows except for one duplicate row from the Test table
Solution:
DELETE FROM TEST WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM TEST GROUP BY SNO);
Output:

Now, verify the Test table and you will see that all the duplicate rows are deleted except one row as shown in the below image.

ROWNUM Pseudo Column in Oracle:

1. Generate numbers to each row wise / group of rows wise
2. Not saved in database (temporary)

Example:

SELECT ROWNUM, ENAME FROM EMPLOYEE;
When you execute the above query, you will get the following output.

Example:

SELECT ROWNUM, ENAME, DEPTNO FROM EMPLOYEE WHERE DEPTNO=10;
When you execute the above query, you will get the following output.

Example:

Write a query to fetch the first-row employee details from Employee Table by using rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM=1;
Output:

Example:

Write a query to fetch the second-row employee details from the Employee table by using rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM=2;
Output:

Note: Generally, rownum is always starting with 1 from every selected row in a table. So to avoid this problem we can use <,<= operators.

Example:

SELECT * FROM EMPLOYEE WHERE ROWNUM<=2
MINUS
SELECT * FROM EMPLOYEE WHERE ROWNUM=1;
Output:

Example:

Write a query to fetch the first five rows from the Employee table by using rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM<=5;
Output:

Example:

Write a query to fetch the fifth-row employee details from the Employee table by using rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM<=5
MINUS
SELECT * FROM EMPLOYEE WHERE ROWNUM<=4;
Output:

Example:

Write a query to fetch from 3rd to 9th row from Employee table by using rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM<=9
MINUS
SELECT * FROM EMPLOYEE WHERE ROWNUM<3;
Output:

Example:

Write a query to fetch the last two rows from the Employee table by rownum?
Solution:
SELECT * FROM EMPLOYEE WHERE ROWNUM<=14
MINUS
SELECT * FROM EMPLOYEE WHERE ROWNUM<=12;

(or)

SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ROWNUM<=(SELECT COUNT(*)-2 FROM EMPLOYEE);

Output:

In the next article, I am going to discuss the Inline View Subquery in Oracle with examples. Here, in this article, I try to explain Pseudo Columns in Oracle with Examples. I hope you enjoy this Oracle Pseudo Columns article.