select without from


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.

BigQueryabDb2 (LUW)bbbeMariaDBabbbfMySQLaccgOracle DBPostgreSQLabbbfSQL ServerabdSQLiteabbbfselect without fromMulti-row insert … valuesStand-alone valuesFrom|Join (values …) tWith t as (values …)
  1. Proprietary extension (non-standard!)
  2. Only without keyword row
  3. Requires keyword row: values row('r1c1','r1c2'), row('r2c1', 'r2c2')
  4. Needs from clause column renaming • Only without keyword row
  5. Erfordert Spaltennamen in der With-Klausel: with x (c1,c2) as (values …) • Nur ohne Schlüsselwort row
  6. Nur ohne Schlüsselwort row
  7. Nur mit Schlüsselwort row: values row('r1c1','r1c2'), row('r2c1', 'r2c2')

About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy his Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Footnotes

  1. SQL:2011, Part 1, §8.4 (freely available from ISO). Also SQL-92, §23.3.

  2. Or “Auxiliary Table” as Joe Celko would say.

  3. …or proprietary features such as synonyms.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license