SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> select 4*5 from dual;
4*5
----------
20
SQL>
I find it really strange. If there is no column named 4*5 in dual, how does the select statement work?
Also, why don't I see the same behaviour when I create my own dual table?
SQL> create table dual2(dummy varchar2(1));
Table created.
SQL> desc dual2
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> select 4*5 from dual2;
no rows selected
SQL>
Best Answer
From Wikipedia:
Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)
It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.
4*5
is a mathematical operation, just as'Foo'
is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.From the documentation (CONCEPTS):
And the SQL Reference: