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.
select current timestamp from sysibm.dual is shorter than select current timestamp from sysibm.sysdummy1 🙂