Pseudo Columns in Oracle

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.

Example to understand Pseudo Columns in Oracle

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.

Pseudo Columns in Oracle with Examples

Example:

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

Pseudo Columns in Oracle with Examples

Example:

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

ROWID Pseudo Column in Oracle

Example:

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

ROWID Pseudo Column in Oracle

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.

How to delete multiple duplicate rows except for one duplicate row from a 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:

ROWID Pseudo Column in Oracle with Examples

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.

ROWID Pseudo Column in Oracle with Examples

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.

ROWNUM Pseudo Column in Oracle

Example:

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

ROWNUM Pseudo Column in Oracle

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:

ROWNUM Pseudo Column in Oracle with Examples

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:

ROWNUM Pseudo Column in Oracle with Examples

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:

ROWNUM Pseudo Column

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:

ROWNUM Pseudo Column

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:

Pseudo Columns in Oracle with Examples

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:

Pseudo Columns in Oracle with Examples

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:

Oracle Pseudo Columns Examples

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.

Leave a Reply

Your email address will not be published.