What is dual in oracle?

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 .

Now let’s dig into more on the DUAL table.

You can watch below video on DUAL table which is showing code snippets by executing them in SQL Developer:

In Oracle, the SELECT statement must have a FROM clause unlike other databases such as SQL Server, My SQL, Sybase, and PostgreSQL..etc.

In those databases (not Oracle), in order to get the date, you can simply write :

If you observe, there is no table reference (FROM clause) in the above SELECT queries.

You might be confused about why there is a need to give FROM clause if you are new to Oracle and had experience with the above-mentioned databases.

But one thing is clear, FROM clause is mandatory in all the queries in Oracle SQL. So we have to re-write the above query like below in Oracle:

Of course, you can keep any physical table in the place of ‘my_table’ and get the result of the current date.

But the problem here is, you ended up getting the same number of rows currently you have in your physical table even for the current date. Obviously we don’t want this.

Fortunately, Oracle provides you with the DUAL table which is a special table that belongs to the schema of the user SYS and it is accessible to all users.

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.

This is shown below:

or you can use

By using the DUAL table, you can execute queries that contain functions that do not involve any table like the LOWER() function as follows:

Besides calling built-in functions, you can use expressions in the SELECT clause of a query that accesses the DUAL table:

The DUAL table is the most simple one because it was designed for fast access.

This optimization provides even better performance than directly accessing the physical DUAL table.

Following code display the numbers 1..10 from DUAL :

Read also:

comments

Example-1: Oracle Query

Output –

Example-2: Oracle Query

Output –

DUAL : It is a table that is 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.

Output –

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. Oracle Query :

Output –

Oracle Query :

Output :

Several other databases, including MS SQL Server, MySQL, PostgreSQL and SQLite, allows the omitting FROM clause. This exception is the reason there is no dummy table like DUAL in other databases.

【dual in MySQL】

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses.

MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced. Meanwhile, you are permitted to specify DUAL as a dummy table name in situations where no tables are referenced.