Is There a Better Alternative to Using the DUAL Table in Oracle for SELECT Statements Without a FROM Clause?

Select Statements without a FROM Clause in Oracle

In SQL Server, a SELECT statement can be executed without referencing a table, using a syntax such as:

Select 1.2 +3, 'my dummy string'

However, Oracle does not allow a SELECT statement without a FROM clause. To execute such queries in Oracle, the dual table is often used:

Select 1,2+3, 'my dummy string' FROM DUAL

This begs the question: is there a better way to perform this type of query in Oracle? Let's explore the answer.

Dual Table Usage

No, Oracle does not allow SELECT statements without a FROM clause. Using the dual table is considered a good practice.

Dual is an in-memory table that provides a fast access path without I/O requirements. Originally, it contained two records and was used to duplicate records for JOIN operations. Today, it has only one record but can still generate any number of rows using the CONNECT BY clause:

SELECT  level
FROM    dual
CONNECT BY
        level <h3 id="Support-in-Other-Databases">Support in Other Databases</h3><p>Other databases like MySQL also support the dual table and the fromless syntax. MySQL's syntax is similar to SQL Server's:</p><pre class="brush:php;toolbar:false">Select 1.2 +3, 'my dummy string'

The above is the detailed content of Is There a Better Alternative to Using the DUAL Table in Oracle for SELECT Statements Without a FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!