Is the FROM Clause Really Optional?

Did you know that starting with Oracle 23c, the FROM clause is optional? Not totally optional, so let me explain.

In a SELECT statement, that retrieves data from an actual table or tables, the FROM clause is still mandatory. This makes sense, since Oracle database cannot read your mind to figure out which table you want to select from.
However, if you want to select a character string or today’s date, or the database environment you are connected to, you no longer need to specify the FROM clause, it is optional. Oracle will fill in the “blanks” with “FROM dual”, behind the scene.

Anything where you previously wanted to select something “FROM DUAL” can be left out now in your select statement. This is very cool, as it makes the code easier to read and shorter.

Here is a basic example on how to use this:

select sysdate;

SYSDATE
---------
14-DEC-23

In pre-23c versions you would have written this as:

select sysdate from dual;

SYSDATE
---------
14-DEC-23

And the original syntax with the FROM clause, still works in 23c.

Other uses of this new feature would be in scripts. Usually at the begining of my scripts I like to read into parameters the environment I am connected to. I would use the sys_context function to return various things: ie. db name, or host, with the purpose of populating some parameters. Here are some examples how you could use this feature now:

select sys_context('USERENV','DB_NAME');

select sys_context('USERENV','HOST');

select 'List of users as of: '  || sysdate;

Remember if you want to select from a specific table, such as hr.employees, then the FROM clause in mandatory, required. If you want to select something from DUAL, then the FROM clause is optional.

Did you know about this feature? What do you think of it? Leave a comment, I read every feedback!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !