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

[abc_18]

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.

[Brian Crabtree]

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

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]

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]

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,

[Dilip Kumar_4]

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]

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

[Muthukumar_5]

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 from table;

select expression may be table field name or arithmatic expression(s).

Try as,

select 1 + 1 from dual;
it will show as,

1+1
2 .. for all rows.

I hope you got the point now.

[Jean-Luc Oudart]

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

[Yogeeraj_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

[R. Allan Hicks · Accepted solution]

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;

[abc_18]

.