What is the DUAL Table in Oracle?

Have you seen SQL statements with the word DUAL in them? Wondering what it is? Find out what the dual table in Oracle is in this article.

 

The DUAL Table in Oracle

You might have seen some SQL statements with the keyword DUAL in them.

These would have been SELECT statements, and the DUAL keyword would be used in place of a table.

What is it?

The DUAL table is a dummy table in Oracle databases. It’s used for selecting data from system functions and calculations when you don’t need any data from the database.

An example of using the DUAL table would be:

SELECT SYSDATE FROM DUAL;

This would return the system’s current date to your screen.

SYSDATE

03/JUL/22

 

Why Use the DUAL Table?

You might be wondering, why would we even use this DUAL table in the first place? We’re not selecting any data from the database, so shouldn’t we be able to say SELECT SYSDATE without the FROM DUAL part?

Well, this is because Oracle needs a table name with all SELECT statements. Even if you don’t actually need one.

So, if you try to run SELECT SYSDATE by itself, without the FROM DUAL part, you’ll get an error.

This is because the FROM <table> is required.

Now, this is only Oracle-specific. If you’ve used SQL Server or MySQL, you don’t need to specify a DUAL table.

 

What Columns Can I Select?

I mentioned earlier that the DUAL table is a dummy table used for functions and calculations in your queries.

Normally, when you run a SELECT statement, you need to specify the columns that exist in your table.

For example:

SELECT first_name, last_name
FROM student;

In this case, the first_name and last_name columns need to exist in the student table.

But, if the DUAL table is being used for all kinds of reasons, how does it know what columns you need?

The answer is: it doesn’t matter.

The DUAL table is a “dummy” table. It doesn’t matter what columns you specify in your SELECT statement, you don’t need to have them in the DUAL table.

What Is The Structure Of The DUAL Table?

The DUAL table has:

• One column called DUMMY, which is of type VARCHAR2(1)
• One row with a value of ‘X’
• Owned by the schema SYS

So, it looks like this:

DUMMY

‘X’

 

What’s The History of the DUAL Table and Why Is It Called DUAL?

According to Wikipedia, the DUAL table was created by Oracle staff member Chuck Weiss. It was originally used to join internal views:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, apt for the process of creating a pair of rows from just one.

So, the reason it’s called DUAL is because it originally had two rows and was used to create a pair of rows from just one.

 

Can We Drop The DUAL Table in Oracle?

Technically you can, but you shouldn’t. Messing with the DUAL table or any other table in the SYS schema can break your database.

There is no need to drop this table.

Tom Kyte from AskTom has posted in a long thread about some internal usage of the DUAL table. It’s a long thread, and it’s quite old, but you can read it if you’re interested.

 

Conclusion

So, in summary:

• The DUAL table is a one row, one column, dummy table used by Oracle.
• SELECT statements need a table, and if you don’t need one for your query, you can use the DUAL table
• Don’t modify or delete the DUAL table.

Next time someone asks you about the DUAL table, you’ll be able to answer their questions and explain what it’s used for!