Let’s get that straight from the beginning: select
without from
is not standard conforming SQL. Full stop.
Nevertheless it works in many databases—also in standard conforming ones. That’s no contradiction: the standard explicitly allows conforming databases to “provide user options to process non-conforming SQL statements”.0 The behavior of such statements is completely up to the vendor, of course.
So what alternative does the standard offer to select
without from
? A surprisingly simple and yet powerful one: values
without insert
.
The following select
statement can thus be implemented as a standard-conforming values without insert:
Instead of a non-conforming select
without from
:
SELECT CURRENT_DATE
the standard allows the use of values
without insert
:
VALUES (CURRENT_DATE)
Too bad the stand-alone use of values
is still not part of Core SQL. Consequently, only three out of the six tested databases support it. select
without from
, on the other hand, works on four of them.
By now you might wonder why stand-alone values
might be useful at all. As I implied above, it is more powerful than select
without from
because it is not limited to produce a single row.
The following values
statement returns today’s and yesterday’s dates (use-case) in two rows—not two columns:
VALUES (CURRENT_DATE)
, (CURRENT_DATE - INTERVAL '1' DAY)
With select
without from
, you’d need to use union
. That can quickly become bulky.
Conforming Alternatives
SQL Server offers a confirming variant: values
is allowed in the from
clause, if the from
clause assigns column names:
SELECT *
FROM (VALUES (1,2)
, (3,4)
) t1 (c1, c2)
The only other standard-conforming alternative is to use a dummy table in the from
clause. Databases that do not allow select
without from
usually ship with tables for this purpose (e.g., DUAL
in the Oracle database or SYSIBM.DUMMY1
in DB2). Besides portability there is nothing against using them.
The easiest way to build a standard-conforming and portable solution it is to ship your own dummy1 table with your software.
If you don’t mind maintaining different create
statements for each target database, you can also use a view2 based on the vendors proprietary dummy table. That may or may not save you from an argument about performance if the vendors dummy table is super-performance-optimized.
Compatibility
On the bottom line, this topic is an embarrassing demonstration how poorly the standard is adopted. However, it is upon us to demand this SQL-92 feature from the database vendors.
- Proprietary extension (non-standard!)
- Only without keyword
row
- Requires keyword
row
:values row('r1c1','r1c2'), row('r2c1', 'r2c2')
- Needs
from
clause column renaming • Only without keywordrow
- Erfordert Spaltennamen in der
With
-Klausel:with x (c1,c2) as (values …)
• Nur ohne Schlüsselwortrow
- Nur ohne Schlüsselwort
row
- Nur mit Schlüsselwort
row
:values row('r1c1','r1c2'), row('r2c1', 'r2c2')