Is it possible to have a statement like
SELECT "Hello world"
WHERE 1 = 1
in SQL?
The main thing I want to know, is can I SELECT from nothing, ie not have a FROM clause.
sql – SELECT from nothing? – Stack Overflow
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Q
A
It’s not consistent across vendors – Oracle, MySQL, and DB2 support dual:
SELECT 'Hello world' FROM DUAL…while SQL Server, PostgreSQL, and SQLite don’t require the
FROM DUAL:SELECT 'Hello world'MySQL does support both ways.
C
I’ve always wondered. Why the choice of the term dual for the phantom table?
C
“The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.”
C
On DB2 dual is called ‘sysibm.sysdummy1’
C
On Postgresql, it’s possible to create a dummy table called
DUALand perform queries from a phantom-like table.A
Try this.
Single:
SELECT * FROM (VALUES ('Hello world')) t1 (col1)Multi:
SELECT * FROM (VALUES ('Hello world'),('Hello world'),('Hello world')) t1 (col1)more detail here: http://modern-sql.com/use-case/select-without-from *
A
Here is the most complete list of database support of dual from https://blog.jooq.org/tag/dual-table/ **
A
In Firebird, you can do this:
select "Hello world" from RDB$DATABASE;RDB$DATABASE is a special table that always has one row.
A
For ClickHouse, the nothing is
system.oneSELECT 1 FROM system.one
Thanks [Wayback/Archive] Ritwik Bose, [Wayback/Archive] OMG Ponies, [Wayback/Archive] Alex Blakemore, [Wayback/Archive] rebelliard, [Wayback/Archive] Danubian Sailor, [Wayback/Archive] Stephan, [Wayback/Archive] chuongtv, [Wayback/Archive] Vadzim, [Wayback/Archive] Robyn, and [Wayback/Archive] simPod!
Let’s get that straight from the beginning: select without from is not standard conforming SQL. Full stop.Nevertheless it works in many databases—also in standard conforming ones.
That’s no contradiction: the standard explicitly allows conforming databases to “provide user options to process non-conforming SQL statements”. The behavior of such statements is completely up to the vendor, of course.
So what alternative does the standard offer to select without from? A surprisingly simple and yet powerful one: values without insert.
** [Wayback/Archive] SQL Trouble with dummy tables – Java, SQL and jOOQ.
…
In many other RDBMS, there is no need for dummy tables, as you can issue statements like these:
SELECT1;SELECT1 + 1;SELECTSQRT(2);These are the RDBMS, where the above is generally possible:
- H2
- MySQL
- Ingres
- Postgres
- SQLite
- SQL Server
- Sybase ASE
In other RDBMS, dummy tables are required, like in Oracle. Hence, you’ll need to write things like these:
SELECT1FROMDUAL;SELECT1 + 1FROMDUAL;SELECTSQRT(2)FROMDUAL;These are the RDBMS and their respective dummy tables:
- DB2: SYSIBM.DUAL
- Derby: SYSIBM.SYSDUMMY1
- H2: Optionally supports DUAL
- HSQLDB: INFORMATION_SCHEMA.SYSTEM_USERS
- MySQL: Optionally supports DUAL
- Oracle: DUAL
- Sybase SQL Anywhere: SYS.DUMMY
Trouble when avoiding dummy tables
While some might find avoiding dummy tables in H2 or MySQL better as SQL becomes more readable, it is worth mentioning that you can run into trouble when doing so: MySQL’s avoiding DUAL Clauses such as the following one seem to cause trouble in MySQL in some contexts:
-- this can cause troubleexists (select1where1 = 0)-- this will workexists (select1fromdualwhere1 = 0)Other similar clauses exist Ingres has no DUAL, but would actually need it In Ingres, you cannot have a WHERE, GROUP BY or HAVING clause without a FROM clause. Without dummy tables, you will have to create your own dummy subquery:
SELECT1WHERE1 = 1-- this is a workaround with a nested selectSELECT1FROM(SELECT1)ASDUALWHERE1 = 1
DUAL
This is the table I always remember it by, was introduced by Oracle and originally held two rows (not one!) as you can see in the DUAL table – Wikipedia entry:
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 calledDUMMYthat has a value of'X'. It is suitable for use in selecting a pseudo column such asSYSDATEorUSER.…
Charles Weiss explains why he created
DUAL:I created the
DUALtable 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 aJOINto theDUALtable and create two rows in the result for every one row in your table. Then, by usingGROUP BY, the resulting join could be summarized to show the amount of storage for theDATAextent and for theINDEXextent(s). The name,DUAL, seemed apt for the process of creating a pair of rows from just one.…
Beginning with 10g Release 1, Oracle no longer performs physical or logical I/O on the
DUALtable, though the table still exists.
Many other databases either do not require such a table (including Microsoft SQL Server, MySQL, PostgreSQL, SQLite, and Teradata), or provide an alternative view or table.
From the above Stack Overflow question, blog post and Wikipedia article, I created this overview
DBMS Table/View column name column type value remarks ClickHouse system.onedummyUInt80implicitly used when no table is specified in the SELECT query Firebird/InterBase RDB$DATABASE6 columns various types various values the table has a meaning of its own, see [Wayback/Archive] Firebird 4.0 Language Reference: RDB$DATABASE HSQLDB INFORMATION_SCHEMA.SYSTEM_USERS??? ??? ??? H2 DUAL??? ??? ??? Optionally supports DUAL.IBM Db2 sysibm.sysdummy1IBMREQD??? YHas a view that SYSTEM.DUALwhen using Oracle Compatibility.Informix sysmaster:"informix".sysdual??? ??? ??? Supported in Informix version 11.50 and later; You can use CREATE PUBLIC SYNONYM dual FOR sysmaster:"informix".sysdualto create a namedualin the current database with the same functionality.Microsoft Access none none none none 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 none none none none Not required, but you can create a view CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;
MariaDB/MySQL DUALnone none none Usage of DUALis optional and there is noTABLEorVIEWdefinition for it.Oracle DUALDUMMYVARCHAR2(1)'X'PostgreSQL none none none none Not required, but you can create a view CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;Snowflake DUAL??? ??? ??? : DUAL is supported, but not explicitly documented. SAP HANA DUMMY??? ??? ??? SQL Anywhere SYS.DUMMY??? ??? ??? SQLite none none none none Not required, but you can create a view CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;Teradata Not required. Vertica DUAL??? ??? ??? has support for a DUAL table in their official documentation.
Oracle Magazine
From [Wayback/Archive] Oracle Mag. Send Mail: More About Oracle’s History
The History of Dual
In the [Wayback/Archive] November/December issue of Oracle Magazine you recount the history of the company, so I’m wondering if you can tell me how the DUAL table got its name. People I’ve asked, including seasoned Oracle gurus, have not been able to answer this.
Sean O’Neill
(sean.oneill@organon.ie)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.
Chuck Weiss, Oracle
Queries
- [Wayback/Archive] SELECT from DUAL – Google Search
- [Wayback/Archive] sqlite select from dual – Google Search (as SQLite was where I needed it for)
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment