sqloracle-database

Select without a FROM clause in Oracle

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?

Solution

  • No, in Oracle there is no SELECT without FROM.

    Using the dual table is a good practice.

    dual is an in-memory table. If you don't select DUMMY from it, it uses a special access path (FAST DUAL) which requires no I/O.

    Once upon a time, dual had two records (hence the name) and was intended to serve as a dummy recordset to duplicate records being joined with.

    Now it has but one record, but you can still generate an arbitrary number of rows with it:

    SELECT  level
FROM    dual
CONNECT BY
        level <= 100

    MySQL also supports dual (as well as the fromless syntax).