61

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?

Ben
  • 51,770
  • 36
  • 127
  • 149
RRUZ
  • 134,889
  • 20
  • 356
  • 483

6 Answers6

94

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).

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    Didn't know about the history of dual. Just make sure dual only contains one row. I have seen dual tables with all kind of stuff in it. A guarantee for a long and fun bug hunt. – Jens Schauder Dec 10 '09 at 15:57
  • Sort of like when /dev/null gets replaced by a normal file? – Paul Tomblin Dec 10 '09 at 16:07
  • `@Paul Tomblin`: worse than that. dual uses different access path depending on whether you select `dummy` from it of not. – Quassnoi Dec 10 '09 at 16:12
  • FAST DUAL is supported since Oracle 10g. In 9i DUAL is just an ordinary table. – Egor Rogov Dec 10 '09 at 16:29
  • 2
    And here is a good link from Tom Kyte that goes into some aspects of Dual: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388 – Doug Porter Dec 10 '09 at 17:21
  • "Once upon a time, dual had two records" .. dual is a good ol' man – Khaled.K Nov 11 '15 at 07:49
  • 1
    Do you have any idea if Oracle has ever considered dropping the FROM clause requirement? Selecting from dual seems so ridiculous and hacky to me I can't believe Oracle has never fixed this. – Andy Sep 06 '18 at 05:40
11

don't forget that most of the times you don't actually need to use SELECT.

Instead of:

SELECT sysdate INTO l_date FROM dual;
SELECT CASE WHEN i = j THEN 0 ELSE 1 END INTO l_foo FROM dual;
...

you can use 

l_date := sysdate;
l_foo  := CASE WHEN i = j THEN 0 ELSE 1 END;
...
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
4

it is good practice to use the dual table

Yes, the dual table is usually used for this exact purpose. It's pretty standard in Oracle when you have no table to select from.

dcp
  • 54,410
  • 22
  • 144
  • 164
4

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.

WhatsThePoint
  • 3,395
  • 8
  • 31
  • 53
sleske
  • 81,358
  • 34
  • 189
  • 227
2

I think you gotta use dual. it is used when you need to run SQL that does not have a table name. I can't say I use it much other than in SQL scripts to echo out the date something is ran or something stupid like that.

tmeisenh
  • 1,504
  • 1
  • 13
  • 11
2

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.

DCookie
  • 42,630
  • 11
  • 83
  • 92