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

For example,

SELECT 1+1
FROM dual;
SELECT 'GeeksforGeeks'
FROM DUAL;

SELECT NOW()
FROM dual;

SELECT CONCAT('fnama',' ','lname')
FROM dual;

is equivalent to

SELECT 1+1;

SELECT 'GeeksforGeeks';

SELECT NOW();

SELECT CONCAT('fnama',' ','lname');

And by the same logic,

SELECT * 
FROM DUAL;

will raise Error: No tables used

as same as

SELECT * ;