Drafting Queries Without a Table


Sometimes, we just want to try or demo a specific way to use SQL. Even if the query is simple, it can become a tedious task if it requires test data that is not yet available in any table. In such cases, the values statement can be handy.

In its full power, values can not only provide data to the insert statement, but be used as stand-alone statement very much like select. Unlike select, values doesn’t access any tables but only the data that is explicitly listed in the SQL statement.

The following example demonstrates that COUNT(*) and COUNT(expression) mean something different:0

SELECT COUNT(c1)
     , COUNT(*)
  FROM (VALUES (1)
             , (NULL)
       ) t1(c1)

The example uses the values clause to produce a table with two rows and one column (of unknown name). One of the values is null. To be able to refer to this column, and thus be able to apply an aggregate function upon it, the example aliases this derived table as t1 and its column as c1 using the alias columns in from syntax. Alternatively, you can use a with clause to name the columns. t1 and its columns can now be used like a regular table of view.

Compatibility

The example conforms to full SQL-92 and later standards with the optional features F641, F591 and F661.1

BigQueryDb2 (LUW)aadMariaDBaaaMySQLbbbOracle DBPostgreSQLaaaeSQL ServercfSQLiteaaaStand-alone valuesFrom|Join (values …) tWith t as (values …)rename columns in from clause
  1. Only without keyword row
  2. Requires keyword row: values row('r1c1','r1c2'), row('r2c1', 'r2c2')
  3. Needs from clause column renaming • Only without keyword row
  4. Requires column names in with clause • Only without keyword row
  5. Accepts missing column aliases
  6. Only for derived tables (not regular tables or views)

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the 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 all sizes.
Learn more »

Footnotes

  1. COUNT(*) counts rows (SQL-92, §6.5, General Rule 1a; SQL:2016, Part 2, §10.9 General Rule 5).

    COUNT(expression) counts not null values (SQL-92, §6.5, General Rule 1b; SQL:2016, Part 2, §10.9 General Rule 7a).

  2. Validated at http://developer.mimer.se/validator/index.htm.

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