Let's get that straight from the beginning:
from is not standard conforming SQL. Full
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
from? A surprisingly
simple and yet powerful one:
select statement can thus be implemented as
a standard-conforming values without insert:
Instead of a non-conforming
the standard allows the use of
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.
from, on the other hand, works on four of
By now you might wonder why stand-alone
might be useful at all. As I implied above, it is more powerful than
where because it is not
limited to produce a single row.
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)
need to use
union. That can quickly become bulky.
The only standard-conforming alternative is to use a dummy table in
from clause. Databases that do not allow
from usually ship
with tables for this purpose (e.g.,
DUAL in the Oracle
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
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.
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.