Select without a FROM clause in Oracle

The ABC and XYZ of DUAL

DUAL is a single-row, single-column table created during Oracle installation. All Oracle users have access to DUAL, a table that comes in handy when you need to fetch data without accessing a physical table.

Applications of DUAL

Generating dates:

When you lost your calendar and need to figure out the dates for the next 10 days:

SELECT TRUNC(SYSDATE) + level - 1 AS date_generated
FROM dual
CONNECT BY level <= 10;


This generates a series of dates starting from right now.

Variable assignment:

For when you want to make sure variables get their values without disturbing DUAL:

DECLARE 
    my_var VARCHAR2(100);
BEGIN
    -- Hey DUAL, mind if we just directly assign some values here?
    SELECT 'Direct Assignment' INTO my_var FROM dual;
    -- Okay, thanks bud!
END;


Quick calculations:

When you're tired of your calculator and just want to flex some SQL muscles:

-- Did you know SQL could do your math homework too?
SELECT SYSDATE, USER, 3.14159 * (2*2) AS area_of_circle
FROM dual;


Know when to avoid DUAL

Even DUAL needs peace sometimes. In certain scenarios, we can choose not to use it.

Simple PL/SQL operations:

For instance, assigning direct values like this doesn't trouble DUAL:

DECLARE
    today_s_date DATE := SYSDATE; -- Directly assigning SYSDATE to a variable
BEGIN
    -- Proceed with PL/SQL block without querying DUAL
END;


Handling user-defined types:

When working with collections or user-defined objects, you can manage them directly without involving DUAL.

SPEED UP access with FAST DUAL

Just as the Flash outpaces ordinary mortals, Oracle's DUAL blazes past regular table access. Stored in memory (not on disk), and optimized via the FAST DUAL access path, DUAL returns date and user data faster than you can say "select star"!

Unleashing performance

When crafting Oracle SELECT statements that don't query real tables, the optimizer deploys its Flash-like FAST DUAL ability, turbocharging data retrieval.

MySQL in comparison

In contrast, MySQL offers fromless syntax, granting you the ability to execute a SELECT without a table:

SELECT 3.14159, 'Hello, StackOverflow!';


However, Oracle-based folks would need to get accustomed to DUAL for similar operations when transitioning from MySQL.