values — Create Rows out of Nothing


The values keyword is probably as old as SQL itself and is pretty well-known for its use with the insert statement. This is, however, just the functionality required by entry-level SQL-92. With full SQL-92,0 values has a richer semantic: it becomes valid wherever select is valid and can produce multiple rows.

Usage with Full SQL-92

With full SQL-92, values is generally1 followed by a comma separated list of rows that are in turn column lists enclosed in parentheses. Each row must have the same number of columns2 and the corresponding columns must have the same data type in all rows3—very much like union.

VALUES [ROW]('row 1 column 1', 'row 1 column 2')
     , [ROW]('row 2 column 1', 'row 2 column 2')

The optional keyword row is not widely supported but required by some products (see Compatibility).

The result of this example is a 2×2 table holding the values as suggested by the data. The column names are implementation-defined but can be renamed in the from clause.

This code can be put everywhere where select is allowed.4 That is, to provide data to insert (multiple rows), in subqueries, and even as statements of its own.

Use Cases

Having explained how it works, you may wonder what it can be used for. There are several real-wold use-cases. Follow the links for more details:

Compatibility

BigQueryaDb2 (LUW)aaadaMariaDBaaaaeMySQLbbbbOracle DBPostgreSQLaaaaaSQL ServeracSQLiteaaaaaMulti-row insert … valuesStand-alone valuesFrom|Join (values …) tWith t as (values …)[not] in (values …)
  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. Column references not supported • Only without keyword row

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. Feature F641, “row and table constructors”, in later standards.

  2. Intentionally neglecting the <row value special case> here.

  3. SQL-92, §7.2, Syntax Rule 1; SQL:2016, Part 2, §7.3, Syntax Rule 1.

  4. SQL-92, §7.2, General Rule 1. Later standards allow conversion of data types (see SQL:2016, Part 2, §7.3, Syntax Rule 4 referring to §9.5) .

  5. Both, values and select are referenced from <simple table>.

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