SQL DUAL Table

SQL DUAL Table – Learn when to use dual table in SQL with example queries.

DUAL

• DUAL is a dummy table that is automatically created by Oracle Database.
• DUAL table is present under SYS user but accessible to all the users.
• DUAL table has one DUMMY column defined with varchar2(1) and contains one row with a value x.
• Selecting from dual table is useful to calculate constant expressions with select statement.
• you can select a constant, pseudo column, or expression from any table, but the value will be returned as many times as there are rows in the table.

desc dual;

DESC describes the columns and their data types in the table

Name Null Type 
----- ---- ----------- 
DUMMY VARCHAR2(1)

Example queries using DUAL table

select * from dual;

Output

DUMMY
X

select count(*) from dual;

Output

count(*)
1

SELECT 'VISWANATH' FROM DUAL;

Output

VISWANATH
VISWANATH

select 1+2 from dual;

Output

1+2
3

select sqrt(100) from dual;

Output

sqrt(100)
10

select user from dual;

Output

USER
TRAINING593

select sysdate from dual;

Output

SYSDATE
05-MAY-19 10.07.19 PM

select substr('viswanath',1,4) from dual;

substr(‘viswanath’,1,4)
visw

• If we observe all the queries we are not selecting any data from dual table. We are using it just like to complete the syntax of select query with from table.

• we can also create a dummy table with one column and one row similar to dual table.
• dual table is optimized for performance.

Decode cannot be called directly in the plsql block. to call the decode statement we need select statement.

SELECT decode(null,null,1,0) 
FROM DUAL;

decode(null,null,1,0)
1

DUAL table also use to fetch sequence value like seq.currval and nextval.

some other examples using DUAL function

select rownum ||'* 5 = '||rownum*5 from dual connect by level <=10;

The above query prints 5 multiplication table.

select ROWNUM, SUBSTR('VISWANATH',1,ROWNUM),
SUBSTR('VISWANATH',ROWNUM)FROM DUAL connect by level <= length('VISWANATH');

The above query prints the pyramid structure

DELETE FROM DUAL;

Output

SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"

TRUNCATE FROM DUAL;

Output

SQL Error: ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword
03290. 00000 - "Invalid truncate command - missing CLUSTER or TABLE keyword"

SELECT * FROM DUAL
UNION ALL
SELECT * FROM DUAL;

Output

DUMMY
x
x

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10;

The above query prints from 1 to 10 as output