This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL).
select 'abc' abc, 1 def;
It does not work on Oracle. Why do we need to select from DUAL in Oracle? Does the ISO/ANSI standard for SQL require a FROM clause for SELECT statements?
Edit:
Per Bacon Bit
's answer, it does seem required by the SQL standard.
So in reality, because the name DUAL is such a misnomer, if I were to create a table and name it ATOM or ONE, e.g. create table one (atom int);
.. select 'abc' abc, 1 def FROM one;
– Is there a performance penalty compared to SELECT .. FROM DUAL
?
Best Answer
Strictly, yes, the
FROM
clause of aSELECT
statement is not optional. The syntax for SQL-99 details the basicSELECT
statment, and theFROM
clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures. This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations. Whether we want to run
SELECT getdate()
orSELECT 1
orSELECT DB_NAME()
(or whatever your dialect prefers), we don't actually want data from a table.Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:
Other RDBMSes essentially assume that a dummy table is used if no
FROM
is specified.The history of the DUAL table is on Wikipedia: