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-920, 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 1 column 1', 'row 1 column 2')
     , ('row 2 column 1', 'row 2 column 2')

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 allowed4. 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

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Markus’ Book

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

The essence of SQL tuning in 200 pages.

Buy from Markus
(paperback and/or PDF)

Buy from Amazon
(paperback only)

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience. 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>.

“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 | CC-BY-NC-ND 3.0 license