Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:
SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
INTO DUMPFILE 'file_name' |
INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]
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() or SELECT 1 or SELECT 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:
CREATE TABLE DUAL (
DUMMY CHAR(1)
)
INSERT INTO DUAL (DUMMY) VALUES ('X')
Other RDBMSes essentially assume that a dummy table is used if no FROM is specified.
The history of the DUAL table is on Wikipedia:
The DUAL table was created by Charles Weiss of Oracle corporation to
provide a table for joining in internal views:
I created the DUAL table as an underlying object in the Oracle Data
Dictionary. It was never meant to be seen itself, but instead used
inside a view that was expected to be queried. The idea was that you
could do a JOIN to the DUAL table and create two rows in the result
for every one row in your table. Then, by using GROUP BY, the
resulting join could be summarized to show the amount of storage for
the DATA extent and for the INDEX extent(s). The name, DUAL, seemed
apt for the process of creating a pair of rows from just one.
The original DUAL table had two rows in it (hence its name), but
subsequently it only had one row.
The problem was histograms, I ran statistics and disabled histogram creation and the execution plan used nested loops:
BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'MIDAS', TABNAME => 'MINCISOC',
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
If I run it with FOR ALL COLUMNS SIZE AUTO again the same problem because it uses hash join. Thanks to Phil for the suggestion.
Best Answer
The number of columns in a table has nothing to do with the number of columns in the projection of a
SELECTstatement. You can always add additional computed columns to the projection (at least practically... theoretically, you might eventually hit some sort of logical limit).The fact that you are querying
dualhere is irrelevant. You can do exactly the same thing with any table. The projection in this queryreturns all the columns in the
emptable along with three additional computed columnscomputed_one,computed_two, andcomputed_three. The fact thatdualalways has exactly one row makes it useful when you want the result to have exactly one row but other than that, there is no logical difference whether you usedualor a single-row, single-column table that you create yourself.