Which are Pseudo Columns? | Complete Guide Updated 2020

In this article, we will discuss which are Pseudo Columns and its use. Pseudo columns are commonly asked SQL queries in interviews

One must answer when any questions on this area because these are very basic and Pseudo columns are commonly asked SQL queries as well.

Here I have displayed the column in the department table. We can see three columns are available in the below table.

Pseudo Columns

Apart from these two columns in the department table, there are some other columns exist which are called Pseudo Columns or Invisible Columns.

These columns appear to do not exist in any of the tables but actually they exist in all the tables.

Row ID and Row Number are Pseudo Columns in the table. We can write a sample query to see these two columns in the department table.

Pseudo Columns

Pseudo Column Row ID

  • Row ID: is a physical location where the records are stored in the database hard disk. The row ID is generated by the Oracle once when each record is inserted into a table.
  • Whenever we insert a new record into a table that time Row ID is automatically inserted by Oracle.
  • Row ID doesn’t get changed when we update a record in a table.
  • The Row ID is removed when the record gets deleted from the table.
  • Row ID’s are arranged in alphabetical order
  • Row ID can be used to identify a record uniquely. If you don’t have a primary key in your table.
  • Using this Row ID you can display the records in the right way.

For example, you can take a rented house address. The people inside the house keep change whereas the address of that particular house doesn’t get changed.

For example, I want to display the first record in a table for that I can write a query as below.

Pseudo Columns

The diagram shows the same Row Number to two different departments. So using Row ID we can display records how we want.

I hope you are clear up to here.

Pseudo Column Row Number

A Row Number is a serial number that is generated as when the record is retrieved. It is assigned to a record in run time. To know more about Row Number you can read this article pseudo columns & DUAL table

Row Number keeps changing and it is not stored in the Database and It is a dynamic integer value.

To understand this we will retrieve the records from the department table where the department number is equal to 10.

Row Number Dynamic view

If you can compare the below table Row Number with the second diagram row number it is different.

So this is all about Pseudo Columns (Row Number/ Row ID). I hope you enjoyed this article.

Please leave your comments in the comments section.

To understand more about Row Number and Row ID you can Read our other important article What is Row Number in SQL?

Would you like to share something about the pseudo column? You can comment in the comment section.

Thank you. Have a great day.

Leave a comment