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

16

I came across this scenario while modifying a report in the system:

One of querys is returning this table as 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 Oracle table.

From this emerged the doubts:

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

  • If the goal is to return an impossible value, why not return a value that will return null of an already existing table?
asked by anonymous 27.10.2017 / 19:08

2 answers

14

It's a fake table with a single record, used to select when you're not really interested in the data, but want to get the results of some function or variable.

In simpler words it is an "imaginary " table for you to use functions, eg you want to use a sysdate , you can not simply run it, you should call in the scope of a select .

Example:

select sysdate from dual;

Font

EDIT

Oracle differs from SQL Server in that you can simply select without having a FROM clause. 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 have it duplicate the rows, if necessary. Besides that 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.

Font

    
27.10.2017 / 19:12
7

Just complementing Luiz Santos's answer, giving comparative examples with other DBMSs:

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 query scope, or native function), we can simply use the command SELECT , without specifying where to select those values:

SELECT 'EXEMPLO'

In the Oracle databases, we can not perform the above command, considering how Oracle works its SELECT clauses, forcing us to explicitly where we are looking for this data, through 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, this "imaginary " table, used for relatively simple operations (a selection), leaves the code confusing for the reads or comes from another context that applies SQL.

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

    
27.10.2017 / 20:11