DUAL table in SQL

There may be a situation where we want to query something that is not from a table. For example, getting the current date or querying a simple arithmetic expression like 2+2.

In Oracle, clause FROM is not exceptional. If we don’t write the FROM clause in Oracle, we’ll get an error.

Example-1: Oracle Query

SELECT SYSDATE;

Output –

ORA-00923: FROM keyword not found where expected

Example-2: Oracle Query

SELECT 'GeeksforGeeks';

Output –

ORA-00923: FROM keyword not found where expected

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.

Example: Oracle Query

SELECT * 
FROM DUAL ;

Output –

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.

Oracle Query :

SELECT 'GeeksforGeeks' 
AS NAME FROM DUAL;

Output –

GeeksforGeeks 

Oracle Query :

SELECT 2+2 
FROM DUAL;

Output :

2+2 = 4 

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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

pranavjandu

Check out this Author's contributed articles.