This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL).

select 'abc' abc, 1 def;

It does not work on Oracle. Why do we need to select from DUAL in Oracle? Does the ISO/ANSI standard for SQL require a FROM clause for SELECT statements?


Per Bacon Bit's answer, it does seem required by the SQL standard.

So in reality, because the name DUAL is such a misnomer, if I were to create a table and name it ATOM or ONE, e.g. create table one (atom int); .. select 'abc' abc, 1 def FROM one; - Is there a performance penalty compared to SELECT .. FROM DUAL?

  • I think DB2 also cannot do a select without a from. DB2 has a similar dummy table called SYSIBM.SYSDUMMY1. Also you probably already know this, but when you select 'A' from dual, the dual table is not really accessed, which answers the question in your edit (which merited a new question btw). Nov 12, 2012 at 5:58
  • 1
    It does not work in "all" DBMS. There are several DBMS that do not allow a SELECT without a FROM. The manual answers your question about performance: docs.oracle.com/cd/E11882_01/server.112/e26088/… Nov 12, 2012 at 7:03
  • 3
    @JackDouglas: You are correct. DB2 does require a FROM clause. Off the top of my head: Informix, Firebird and Apache Derby also require it. Nov 12, 2012 at 7:32
  • 1
    DB2 does require a FROM clause, but an alternative is to use a statement like values ('abc', 1). You can of course also select from such statement: select abc from ( values ('abc',1) ) as t(abc,def)
    – Lennart
    Jan 11, 2017 at 20:08

3 Answers 3


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:

{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]]

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:



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.

  • 3
    +1 and welcome to dba.se. This is an excellent answer with a fascinating bit of history—I hope you can be encouraged to stick around and contribute more :) Nov 12, 2012 at 5:54
  • 4
    Hours of endless fun were had by me once when a developer inserted a few more rows into dual. Broke plenty of things :) Took a while to track down the culprit!
    – Philᵀᴹ
    Nov 12, 2012 at 14:28

The advantage to dual is the optimizer understands dual is a special one row, one column table (with varchar2 data type) -- when you use it in queries, it uses this knowledge when developing the plan.

Why do we need to select from dual in Oracle?

You can select from dual or from your own tables too, you can if you want.

For me, I'll stick with dual because I know dual exists. I know it has at least 1 and at most 1 row. I know the optimizer knows all about dual and does the most efficient thing for me. The optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. So it's just the way it works.

  • "I know it has at least 1 and at most 1 row." Well, a jerk (or an idiot) with DBA privileges can modify DUAL. Be careful! Apr 26, 2013 at 6:00
  • well if thats happen to you. revoke all privileges except for CREATE SESSION from this Someone person. and what if dual was dropped accidentally by someone?
    – DevYudh
    Apr 26, 2013 at 6:29
  • you can create it using create table dual (dummy varchar2(1)) storage (initial 1) or flashback table dual to before drop
    – DevYudh
    Apr 26, 2013 at 6:30
  • Well, It's an advantage in using DUAL as compared to selecting the expression from any other existing table, but it doesn't explain what's the advantage of having DUAL as alternative for not having to have FROM, like in PostgreSQL or SQLServer Sep 12, 2013 at 9:16
  • @Lukasz Lech in Oracle there is no SELECT without FROM for #MSSQL Serv: There is no need of Dual table in SQL Server at all. but if you transferred your code from oracle to SQL Serv you can create dual using this script CREATE TABLE DUAL ( DUMMY VARCHAR(1) ) GO INSERT INTO DUAL (DUMMY) VALUES ('X') GO but, I don’t see any reason to use/create Dual table in sql server. and also MSSQL Serv and PostGRE SQL dont require Dummy table
    – DevYudh
    Sep 19, 2013 at 8:00

The other two answers provide good background for my answer.

On Oracle databases, it is traditional and reliable. It will fail on other databases which don't have a DUAL table. It is not necessary that you use DUAL, but I would recommend you do.

Standards compliant databases will require a FROM clause specifying at least Table reference. If you have an ORDERS table the following replacement for the FROM DUAL clause would work:

FROM   orders
WHERE  rownum =1

Substitute any table or view you can select from and it will work. Substitute a table or view you can't select from and it will fail. DUAL is more reliable as barring a DBA breaking it, all users can select from it, and will get only one row in the result set. (It does get broken occasionally.)

I am unaware of a standards compliant verb for accessing data which is not in a table without including a Table reference. Given how little I access such data, I don't see such a need. Many of the cases I run into, can be better handled in different ways.

  • 2
    SELECT CURRENT_TIMESTAMP FROM (VALUES(1)) V(C) is standard compliant I believe and doesn't rely on any particular table. Nov 12, 2012 at 14:32
  • @MartinSmith I have updated my response to use Table reference. This is what I meant and should have specified.
    – BillThor
    Nov 12, 2012 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.