Select without a FROM clause in Oracle
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).
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.
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;
...