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