SQL 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 should 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:


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

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 »


  1. Feature F641, “row and table constructors”, in later standards.

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

  3. ISO/IEC 9075-2:2023 §7.3 SR 1.

  4. Since 1999 the SQL standard allows implict type conversions (ISO/IEC 9075-2:2023 §7.3 SR 3).

  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