17

While inserting multiple rows into a table using the following style :

insert all

into ghazal_current (GhazalName,Rating) values('Ajab Apna Haal Hota Jo Visaal-e-Yaar Hota',5)
into ghazal_current (GhazalName,Rating) values('Apne Hothon Par Sajana Chahta Hun',4)
into ghazal_current (GhazalName,Rating) values('Shaam Se Aankh Mein Nami Si Hai',4)
into ghazal_current (GhazalName,Rating) values('Tumhe Yaad Ho Ke Na Yaad Ho',3)

select 1 from dual;

What does the statement select 1 from dual mean ? What is it here for ?

Y.E.P
  • 1,187
  • 6
  • 20
  • 40

3 Answers3

14

DUAL is a built-in table, useful because it is guaranteed to return only one row. This means DUAL may be used to get pseudo-columns such as user or sysdate, the results of calculations and the like. The owner of DUAL is SYS but it can be accessed by every user. DUAL is well-covered in the documentation. Find out more.

In your case, SELECT 1 FROM DUAL; will simply returns 1. You need it because the INSERT ALL syntax demands a SELECT clause but you are not querying the input values from a table.

Tereza Tomcova
  • 4,928
  • 4
  • 30
  • 29
Roney Michael
  • 3,964
  • 5
  • 30
  • 45
5

Brief re-introduction to one-row tables

Some SQL databases require all values to come FROM a table or table-like object, whereas others permit queries to construct values ex nihilo:

-- MySQL, sqlite, PostgreSQL, HSQLdb, and many others permit
-- a "naked" select:
SELECT 1;

-- Others *require* a FROM target, like Oracle.
SELECT 1 FROM DUAL;

-- ...and Firebird/Interbase:
SELECT 1 FROM RDB$DATABASE;

-- ...and DB2:
SELECT 1 FROM SYSIBM.SYSDUMMY1;

Here the cardinality of DUAL is important. If it had more than one row, your result set would have more than one row. What happens, for example, when you SELECT 1 FROM A_Table_With_Ten_Rows?

Why DUAL is used here

The SQL construct VALUES (<row-value-expression>) is a row value constructor. VALUES (1, 2, 3) "creates" a row of values just as SELECT 1, 2, 3 does.

Oracle, of course, requires that these values come FROM somewhere.

As a demonstration, instead of SELECTing from DUAL at the end of the INSERT ALL, try a table with N rows, and you'll see that each VALUES() row is inserted N times.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
pilcrow
  • 56,591
  • 13
  • 94
  • 135
  • `SELECT 1 FROM dual;` can be used in MySQL, too. Postgres has also: `VALUES (1);` (yes, no `FROM`!) and this works in Postgres and SQL-Server: `SELECT y FROM (VALUES (1)) AS x (y);` Oh, `SELECT 1` works in SQL-Server as well. – ypercubeᵀᴹ Mar 13 '13 at 13:27
  • @ypercube, yes. The point is that ora _requires_ the FROM for SELECT and VALUES, whereas my/pg/h/lite/etc. do not require it, even though they may permit it. I'll clarify in answer. – pilcrow Mar 13 '13 at 13:55
  • My point (which I didn't make it clear) was that Postgres allows a syntax even without a `SELECT` ! – ypercubeᵀᴹ Mar 13 '13 at 14:08
  • @ypercube, yes, that (a naked VALUES) is a cool feature. (pg does so many things right!) – pilcrow Mar 13 '13 at 14:10
-1

There are some samples about using dual in Queries:

   select sysdate from dual  /--it returns date of system
    SELECT chr(223) FROM dual /--it returns character of Asciهi code
    select  my_sequence.nextval from dual; /-- It returns the next value of a sequence
    select to_char(sysdate,'yyyy/mm/dd','nls_calendar=persian')from dual 
    /--returns persian date of system
Fariba
  • 693
  • 1
  • 12
  • 27