While inserting multiple rows what does the statement 'select 1 from dual' do?

Question

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 ?

Abidjan

Nothing else than select 1, as the statement states. – Rematch

Useful question, and brilliant choice of ghazals :) – Interfluent

Answer 1

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.

Albania

Dual is not a temporary table. – Swayder

That article describes it as special. That is not the same as temporary. It is a permanent table. – Swayder

Answer 2

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.

Vilhelmina

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. – Messner

@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. – Vilhelmina

My point (which I didn't make it clear) was that Postgres allows a syntax even without a SELECT ! – Messner

@ypercube, yes, that (a naked VALUES) is a cool feature. (pg does so many things right!) – Vilhelmina

Answer 3

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

Gasbag