Selecting From … Nothing

Every database platform has a way to select from nothing. Sometimes we use it to generate data manually, sometimes we use it to get values of various system functions, but it is important to be able to do.

Dummy Table

Generally a dummy table is a dummy table that has exactly one row, but no data this makes it so we can select static values or understand the values of settings or functions, or even understand how a particular function works when we feed it just static data. Probably the most common use I’ve seen of this over the years is finding out what the current time is on the database server, which we do in Db2 using:

select current timestamp from sysibm.sysdummy1;

The result looks something like this:

Quite some time ago, Db2 added support for Oracle’s version of this, called DUAL, but only with the right Oracle compatibility feature enabled.

SELECT Without FROM

When I spent time working with MySQL recently, I learned that MySQL’s version to this is to simply use a SELECT statement without a FROM clause. This also works in Snowflake and most RDBMS platforms. On Snowflake, this looks like:

select current_timestamp();

And the output, predictably, looks something like this:

As expected, this fails in Db2:

select current timestamp;

Values Statement

The values statement is another way to get this kind of information for many platforms. For db2, it can be issued alone with no SELECT or FROM:

values(current timestamp);


Interestingly, the stand-alone values statement does not work in Snowflake:

values(current_timestamp());

The values statement is more powerful than just this, and can be used to essentially build a static temporary table as a part of a query. It is commonly used in insert statements to define the tuples to insert.

Who Cares?

I’m a new convert to SELECT without FROM. In my days working exclusively with Db2, I would have said this was blasphemy, and that valid SQL requires a minimum of SELECT and FROM. Even when working with MySQL I was still getting used to it. Now I’m a convert. Just using SELECT is easy, and not specifying ‘FROM SYSIBM.SYSDUMMY1’ is easy and intuitive. If I want to select something from nothing, specifying nothing is fairly logical.

This is also one the ‘devil is in the details’ edge cases in SQL. Yes, a huge part of SQL is standard across platforms, but dates/times are different, casting to different data types is different, and selecting something from nothing is also different. Along with all the dialect-specific things like MERGE/UPSERT and so on. SQL is an old language, a very heavily used language and an evolving language so the solution that one platform chooses may not be the ultimate standard selected.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 557

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.