UNION ALL w/ MINUS

From Wikipedia:

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)

It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.

4*5 is a mathematical operation, just as 'Foo' is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.

From the documentation (CONCEPTS):

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.

The DUAL table has one column called DUMMY and one row containing the value X.

And the SQL Reference:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

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.