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.