Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL VALUES clause

Reference for the SQL VALUES clause: row constructors, inline tables in FROM, multi-row INSERT, column naming and dialect differences.

VALUES builds a table out of literal rows. Each parenthesized list is one row, and all rows must have the same number of columns. Most often you see it in INSERT ... VALUES, but it is also a query on its own and can be used as an inline table in FROM, which is handy for lookup lists and test data.

SQL
VALUES (1, 'one'), (2, 'two'), (3, 'three');

Try it

SQL VALUES as a query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL VALUES as an inline lookup table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
VALUES (expr [, ...]) [, (expr [, ...]) ...]

INSERT INTO table (col [, ...])
VALUES (expr [, ...]) [, (expr [, ...]) ...];

FROM (VALUES (expr, ...), ...) AS alias (col [, ...])
Part Description
Row A parenthesized list of expressions. Every row needs the same number of items.
Multiple rows Separate rows with commas.
Alias with column list Names the columns of an inline table. Without it, names are dialect-specific.

Returns: a table with one row per parenthesized list.

Dialect differences

Behavior SQLite PostgreSQL MySQL SQL Server Oracle
Stand-alone VALUES (...) Yes Yes VALUES ROW(...) No (use in FROM) No
Multi-row INSERT ... VALUES Yes Yes Yes Yes (up to 1000 rows) 23ai; INSERT ALL before
Inline table in FROM Yes (VALUES ...) AS t(a, b) 8.0.19+: VALUES ROW(...) (VALUES ...) AS t(a, b) 23ai
Default column names column1, column2 column1, column2 column_0, column_1 none; alias required n/a
SQL
-- MySQL 8.0.19+
SELECT * FROM (VALUES ROW(1, 'one'), ROW(2, 'two')) AS t(n, word);

-- Oracle, before 23ai
SELECT 1 AS n, 'one' AS word FROM dual
UNION ALL
SELECT 2, 'two' FROM dual;

Usage notes

  • Same width everywhere. A row with the wrong number of items is an error.
  • Types are inferred from the values. In PostgreSQL, a column of mixed integers and decimals becomes numeric; in SQLite, each value keeps its own type.
  • Name the columns by giving the CTE or derived table a column list, as in the second example, so later references stay readable.
  • Not a table: VALUES exists only inside its query. To keep rows, use INSERT or CREATE TABLE AS.
  • Expressions allowed. Items can be functions or arithmetic (VALUES (1 + 1, upper('a'))).
  • Sorting and limits apply to the whole VALUES result: VALUES (3), (1), (2) ORDER BY 1 is accepted in SQLite and PostgreSQL.