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.
VALUES (1, 'one'), (2, 'two'), (3, 'three');
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 |
-- 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:
VALUESexists only inside its query. To keep rows, useINSERTorCREATE TABLE AS. - Expressions allowed. Items can be functions or arithmetic (
VALUES (1 + 1, upper('a'))). - Sorting and limits apply to the whole
VALUESresult:VALUES (3), (1), (2) ORDER BY 1is accepted in SQLite and PostgreSQL.
Related
INSERT INTO,WITH, derived tables,UNION- Lessons: INSERT, CTEs