DUAL table

<h2 id="example-use">Example use</h2>
<p>Oracle's <a href="/facts/SQL/UjQLJcYm">SQL</a> syntax requires the <a href="/facts/From_(SQL)/xLyuQAo8">FROM</a> clause but some queries don't require any tables - DUAL can be used in these cases.
</p>
SELECT 1+1
FROM dual;

SELECT 1 
FROM dual;

SELECT USER 
FROM dual;

SELECT SYSDATE 
FROM dual;

SELECT * 
FROM dual;

<h2 id="history">History</h2>
<p><a href="/facts/Charles_Weiss/NopTzkVW">Charles Weiss</a> explains why he created DUAL:
</p>
<blockquote><p>
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.<a class="footnote-ref" id="fnref:1" href="#fn:1"><sup>1</sup></a> </p></blockquote>
<h2 id="optimization">Optimization</h2>
<p>Beginning with 10g Release 1, Oracle no longer performs physical or logical I/O on the DUAL table, though the table still exists.<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a>
</p><p>DUAL is readily available for all authorized users in a SQL database.
</p>
<h2 id="in-other-database-systems">In other database systems</h2>
<p>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.
</p>
<ul><li><a href="/facts/ClickHouse/gorE2TXd">ClickHouse</a> 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.</li>
<li><a href="/facts/Firebird_(database_server)/hKVdDnBR">Firebird</a> 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.</li>
<li><a href="/facts/IBM_Db2/2cYITuuY">IBM Db2</a> has a view that resolves DUAL when using Oracle Compatibility.<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a> It also has a table called sysibm.sysdummy1 that has similar properties to the Oracle DUAL one.</li>
<li><a href="/facts/Informix/jDdeW93c">Informix</a>: Informix version 11.50 and later has a table named sysmaster:"informix".sysdual with the same functionality but a more verbose name.<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a>  You can use CREATE PUBLIC SYNONYM dual FOR sysmaster:"informix".sysdual to create a name dual in the current database with the same functionality.</li>
<li><a href="/facts/Microsoft_Access/1tID4rKM">Microsoft Access</a>: A table named DUAL may be created and the single-row constraint enforced via ADO (<a href="https://stackoverflow.com/a/7933927/154439">Table-less UNION query in MS Access</a>)</li>
<li><a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a>: SQL Server does not require a dummy table. Queries like 'select 1 + 1' can be run without a "from" clause/table name.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a></li>
<li><a href="/facts/MySQL/QEEDNBDP">MySQL</a> allows DUAL to be specified as a table in queries that do not need data from any tables.<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a> It is suitable for use in selecting a result function such as <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sysdate">SYSDATE()</a> or <a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user">USER()</a>, although it is not essential.</li>
<li><a href="/facts/PostgreSQL/LHdXuHfl">PostgreSQL</a>: A DUAL-view can be added to ease porting from Oracle.<a class="footnote-ref" id="fnref:7" href="#fn:7"><sup>7</sup></a></li>
<li><a href="/facts/Snowflake_Inc./t8zG3Xld">Snowflake</a>: DUAL is supported, but not explicitly documented. It <a href="https://docs.snowflake.net/manuals/user-guide/querying-sequences.html#sequences-as-expressions">appears in sample SQL for other operations</a> in the documentation.</li>
<li><a href="/facts/SQLite/1PwYzIft">SQLite</a>: A VIEW named "dual" that works the same as the Oracle "dual" table can be created as follows: CREATE VIEW dual AS SELECT 'x' AS dummy;</li>
<li><a href="/facts/SAP_HANA/QUmJmqtj">SAP HANA</a> has a table called DUMMY that works the same as the Oracle "dual" table.</li>
<li><a href="/facts/Teradata/sarnALYT">Teradata</a> database does not require a dummy table. Queries like 'select 1 + 1' can be run without a "from" clause/table name.</li>
<li><a href="/facts/Vertica/SGJBWtrr">Vertica</a> has support for a DUAL table in their official documentation.<a class="footnote-ref" id="fnref:8" href="#fn:8"><sup>8</sup></a></li></ul>
<h2 id="notes">Notes</h2>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"More About Oracle's History". Oracle Magazine. January–February 2002. Archived from the original on December 5, 2004. Retrieved 4 September 2013. <a href="https://web.archive.org/web/20041205061859/http://www.oracle.com/technology/oramag/oracle/02-jan/o12sendmail.html" target="_blank">https://web.archive.org/web/20041205061859/http://www.oracle.com/technology/oramag/oracle/02-jan/o12sendmail.html</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>Oracle Database SQL Language Reference 11g Release 2 (11.2), Selecting from the DUAL Table <a href="https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries009.htm#SQLRF20036" target="_blank">https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries009.htm#SQLRF20036</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>DB2 10.1 InfoCenter :: DB2 Version 10.1 for Linux, UNIX, and Windows - DUAL table <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052874.html" target="_blank">http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052874.html</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
<li id="fn:4"><p>Informix 12.10 InfoCenter :: Administrator's Reference :: The sysmaster database :: The System-Monitoring Interface Tables :: sysdual <a href="https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0225.htm" target="_blank">https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0225.htm</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li>
<li id="fn:5"><p>What is the Oracle equivalent of “Dual” table in SqlServer? <a href="https://stackoverflow.com/questions/28371342/what-is-the-oracle-equivalent-of-dual-table-in-sqlserver" target="_blank">https://stackoverflow.com/questions/28371342/what-is-the-oracle-equivalent-of-dual-table-in-sqlserver</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li>
<li id="fn:6"><p>"MySQL :: MySQL 5.0 Reference Manual :: 13.2.8 SELECT Syntax". Archived from the original on 2012-01-03. Retrieved 2009-03-14. <a href="https://web.archive.org/web/20120103180754/http://dev.mysql.com/doc/refman/5.0/en/select.html" target="_blank">https://web.archive.org/web/20120103180754/http://dev.mysql.com/doc/refman/5.0/en/select.html</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li>
<li id="fn:7"><p>PostgreSQL :: PostgreSQL Wiki :: Oracle to Postgres Conversion <a href="http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#The_Dual_Table" target="_blank">http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#The_Dual_Table</a> <a href="#fnref:7" class="footnote-back-ref">↩</a></p></li>
<li id="fn:8"><p>"DUAL". www.vertica.com. Retrieved 2022-03-10. <a href="https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/DUAL.htm" target="_blank">https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/DUAL.htm</a> <a href="#fnref:8" class="footnote-back-ref">↩</a></p></li>
</ol>

DUAL table open-in-new

DUAL table