What is and what is the utility of the DUAL table for Oracle?

Question

I came across this scenario during the modification of a report in the system:

One of querys is returning to this such table DUAL:

select * from dual

But in the system there is no DUAL table, and the answer they gave me was that it was a native table of Oracle.

From that arose the doubts:

• What is and what is the usefulness of the DUAL table?

• If the goal is to return an impossible value, why not return a value that will return null of an existing table?

Answer 1

It is a kind of false table with a single record, used to select when you are not really interested in the data, but want to receive the result of some function or variable.

In simplest words it is a table "imaginary" for you to use functions, for example, you want to use a sysdate, you can’t just run it, you should call in the scope of a select.

Example:

select sysdate from dual;

Source

EDIT

Oracle differs from SQL Server in that you can simply select without having a clause FROM. This is mainly because the dual table allows you to create a JOIN, unlike the way SQL Server works. In Oracle it is possible to duplicate the lines if necessary. In addition, conceptually every selection must have an origin. The dual table is of generic origin of a single record, with the intention of creating this necessary interaction between the elements.

Source

Answer 2

Just complementing the response of Luiz Santos, giving comparative examples with other Dbms:

In SQL Server or Mysql, when you want to select a value that is not contained in a specific table (such as a variable declared in a scope of query, or native function), we can simply use the command SELECT, without specifying where we select these values from:

SELECT 'EXEMPLO'

On the other hand, in the Oracle databases, we cannot carry out the above command, considering the way Oracle works its SELECT, obliging us to clarify where we seek this data, through the clause FROM. For the same example given above in an Oracle database, the action would be represented by the following clause:

SELECT 'EXEMPLO' FROM DUAL

In my opinion it is a concept somewhat equisito (with gambiarra face) this table "imaginary", used for relatively simple operations (a selection), which leaves the code even confusing for those reading it or coming from another context that applies SQL.

At this link there is a brief history of the table, told by its creator, Chuck Weiss, if you are interested, in English.