Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

why does "select 1 from dual" work - what is the "1"?

SOLVED
Go to solution
abc_18
Regular Advisor

why does "select 1 from dual" work - what is the "1"?

Okay, I've searched TFM, but can't find the SELECT explanation of *why* the syntax:
"select 1 from dual;" actually works.

Any ideas?

Thanks in advance.
11 REPLIES
Brian Crabtree
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

"dual" is a null object (think using /dev/null).

You generally use it to verify that you are connected to the database in scripts, and look for the error code. Also, you can test to_char and to_date functions using the 'dual' object.

Not sure if that explains what you are looking for.

Brian
abc_18
Regular Advisor

Re: why does "select 1 from dual" work - what is the "1"?

Not quite what I was looking for.

I'm trying to find what part of the "SELECT" syntax tree says using "1" is valid syntax...

Also, I'm not sure how "dual" is supposed
to be /dev/null. It looks just like an
ordinary table to me - are you saying
it's not?

Leif Halvarsson_2
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

Hi,
As I remember it.

Yes, it is an ordinary table but, containing only one "post", containing the value "1".

It is sometimes used like an "always true" statement in other languages.
Julio Yamawaki
Esteemed Contributor

Re: why does "select 1 from dual" work - what is the "1"?

Hi,

I think your are talking about select from dual in Oracle, correct?
In Oracle dual is a table made to help programmer to show an expression result instead of table contents.
For example, if you want to show time stamp, you can execute 'select sysdate from dual' and it will show you 'Mar-29-05'.
This table dual exists on sys objects and its contents is a column with 'x' value.
Don´t change this, if you drop this contents, this 'select xxx from dual' will stop working.

Regards,

Re: why does "select 1 from dual" work - what is the "1"?

Dual is table that has 1 column and 1 row.

1 is a 'literal' that will be returned by Oracle as it is from the Database.

Basically, we use the Dual table to get something/anything from Oracle database once.
for example to get any expression evaluated or to have any calculation done by Oracle.

You can similarly do a "select 1 from dept/any_other_table/view;"

watch the result and you will get the idea.
Indira Aramandla
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

Hi abc,

select 1 from any table will work as the syntax is correct. It comes from the expr option of the SELECT commnad.

SELECT command selects column values from the tables/ views/ snapshots.

SELECT also selects an expression (expr), usually based on column values, from tables/views/snapshotsâ ¦

Where an expression (expr)is a combination of one or more values, operators or a column name.

DUAL is a table ownerd by SYS that has 1 column called dummy of variable character length 1

When you select an expression say 1 from a table (can be dual or any other table), it will return the expression depending on the number of rows in the table.

Eg:- Select 1 from dual; returns 1 as there is only one record in dual.
Select 1 from v$session; returns 7 1â s if there are 7 records on v$session

Here is a table called TAB1 that has 3 rows.

SQL> select * from tab1;

ENO
----------
101
102
103
Now if I select an expression say 1 from tab1

SQL> select 1 from tab1;

1
----------
1
1
1

If I select an expression â a+bâ from tab1
SQL> select 'a+b' from tab1;

'A+
---
a+b
a+b
a+b

I hope this helps.

Indi
Never give up, Keep Trying
Muthukumar_5
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

select 1 from dual statement will work as,

[1]
1 .. will be printed for number of rows in that table dual.

select will process(general) as,

select
Easy to suggest when don't know about the problem!
Jean-Luc Oudart
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

Hi,

this is all explained in the documentation :

"DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Please refer to "SQL Functions " for many examples of selecting a constant value from DUAL."

(SQL reference guide)

Regards
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor

Re: why does "select 1 from dual" work - what is the "1"?

hi,

about the dual.

Dual is just a "convienence" table. You do not need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.

DUAL exists solely as a means to have a 1 row table we can reliably select from.

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
R. Allan Hicks
Trusted Contributor
Solution

Re: why does "select 1 from dual" work - what is the "1"?

The syntax may not make sense on first look, but if you think about it... .

1 is a literal. If you select 1 from emp; as scott/tiger, you'll see a bunch of 1's. It's similar to saying

select 'Employee Name : '||ename from emp; The string is a literal and is echoed for each row. In this case, it allows you to pretty up a report. You need the single quotes so as not to imply that you mean a column name. Since a column name of 1 is not legal, sqlplus gives you a break and assumes that it is a literal.

Hope that this helped

Dual is, as many have pointed out, a convince table, It is handy for things like select sysdate from dual;
"Only he who attempts the absurd is capable of achieving the impossible
abc_18
Regular Advisor

Re: why does "select 1 from dual" work - what is the "1"?

.