The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.
Oracle's SQL syntax requires the FROM clause but some queries don't require any tables - DUAL can be used in these cases.
SELECT 1+1
FROM dual;
SELECT 1
FROM dual;
SELECT USER
FROM dual;
SELECT SYSDATE
FROM dual;
SELECT *
FROM dual;
Charles Weiss explains why he created DUAL:
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.[1]
Beginning with 10g Release 1, Oracle no longer performs physical or logical I/O on the DUAL table, though the table still exists.[2]
DUAL is readily available for all authorized users in a SQL database.
Several other databases (including Microsoft SQL Server, MySQL, PostgreSQL, SQLite, and Teradata) enable one to omit the FROM clause entirely if no table is needed. This avoids the need for any dummy table.
sysmaster:"informix".sysdual
with the same functionality but a more verbose name.[4] You can use CREATE PUBLIC SYNONYM dual FOR sysmaster:"informix".sysdual
to create a name dual
in the current database with the same functionality.CREATE VIEW dual AS SELECT 'x' AS dummy;