Select without a FROM clause in Oracle

Viewed 65702

in SQL Server is possible to execute a SELECT, without reference to a table; something like:

Select 1.2 +3, 'my dummy string'

As Oracle does not allow a SELECT without a FROM, I use the dual table for this type of operation; something like:

Select 1,2+3, 'my dummy string' FROM DUAL

There is a better way of doing this type of query? it is good practice to use the dual table?

6 Answers

Yes, the dual table is the usual way to do this in Oracle. As a matter of fact, it was introduced just for this.

The main advantage of DUAL is that the optimizer in Oracle knows it is special, so queries using it can be faster than if you used a single-row table you made yourself. Other than that, there's nothing special about it.

Actually, SQL Server's implementation is non-standard. The SQL-92 Standard (Section 7.9) requires a FROM clause in a SELECT statement. DUAL is Oracle's way of providing a table to select from to get a scalar row.

Related