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:

• Select without from

• Query prototyping without table

• Reduce Network Latency For insert

• Emulating is [not] distinct from comparisons

Compatibility

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