The DUAL table is a special one-row, one-column
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
present by default in
Oracle
An oracle is a person or agency considered to provide wise and insightful counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. As such, it is a form of divination.
Description
The word '' ...
and other
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
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.
Example use
Oracle's
SQL syntax requires the
FROM
From may refer to:
* From, a preposition
* From (SQL), computing language keyword
* From: (email message header), field showing the sender of an email
* FromSoftware, a Japanese video game company
* Full range of motion, the travel in a range of ...
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;
History
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.
Optimization
Beginning with 10g Release 1, Oracle no longer performs physical or logical I/O on the DUAL table, though the table still exists.
DUAL is readily available for all authorized users in a SQL database.
In other database systems
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.
*
ClickHouse
ClickHouse is an open-source column-oriented DBMS (columnar database management system) for online analytical processing (OLAP) that allows users to generate analytical reports using SQL queries in real-time. ClickHouse Inc. is headquartered in ...
has a one-row system table system.one with a single column named "dummy" of type UInt8 and value 0. This table is implicitly used when no table is specified in the SELECT query.
*
Firebird
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
* Bennu, Egyptian firebird
* Huma bird, Persian firebird
* Firebird (Slavic folklore)
Bird species
''Various sp ...
has a one-row system table RDB$DATABASE that is used in the same way as Oracle's DUAL, although it also has a meaning of its own.
*
IBM Db2
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...
has a view that resolves DUAL when using Oracle Compatibility. It also has a table called sysibm.sysdummy1 that has similar properties to the Oracle DUAL one.
*
Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose I ...
: Informix version 11.50 and later has a table named with the same functionality but a more verbose name. You can use to create a name in the current database with the same functionality.
*
Microsoft Access
Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Access Database Engine (ACE) with a graphical user interface and software-development tools (not to be confused with the old Microsoft Access w ...
: A table named DUAL may be created and the single-row constraint enforced via ADO
Table-less UNION query in MS Access
*
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ma ...
: SQL Server does not require a dummy table. Queries like 'select 1 + 1' can be run without a "from" clause/table name.
*
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database o ...
allows DUAL to be specified as a table in queries that do not need data from any tables. It is suitable for use in selecting a result function such a
SYSDATE()o
although it is not essential.
*
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
: A DUAL-view can be added to ease porting from Oracle.
*
Snowflake
A snowflake is a single ice crystal that has achieved a sufficient size, and may have amalgamated with others, which falls through the Earth's atmosphere as snow.Knight, C.; Knight, N. (1973). Snow crystals. Scientific American, vol. 228, no. ...
: DUAL is supported, but not explicitly documented. I
appears in sample SQL for other operationsin the documentation.
*
SQLite
SQLite (, ) is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the most ...
: A VIEW named "dual" that works the same as the Oracle "dual" table can be created as follows:
*
SAP HANA
SAP HANA (HochleistungsANalyseAnwendung or High-performance ANalytic Application) is an in-memory, column-oriented, relational database management system developed and marketed by SAP SE. Its primary function as the software running a databas ...
has a table called DUMMY that works the same as the Oracle "dual" table.
*
Teradata
Teradata Corporation is an American software company that provides cloud database and analytics-related software, products, and services. The company was formed in 1979 in Brentwood, California, as a collaboration between researchers at Caltech a ...
database does not require a dummy table. Queries like 'select 1 + 1' can be run without a "from" clause/table name.
*
Vertica
Vertica Systems is an analytic database management software company. Vertica was founded in 2005 by the database researcher Michael Stonebraker, with Andrew Palmer as the founding CEO. Ralph Breslauer and Christopher P. Lynch served as later ...
has support for a DUAL table in their official documentation.
Notes
{{reflist
Databases
Oracle software