MySQL VALUES Statement: Create a Table of Rows

MySQL 8.0.19 and later supports a standalone VALUES statement that returns one or more rows as a table. Write each row as ROW(...). This is different from the VALUES list in an INSERT statement. See the MySQL VALUES statement reference and the MySQL 8.0.19 release notes.

MySQL VALUES syntax

VALUES
  ROW(1, 'Laptop'),
  ROW(2, 'Mouse');

Each row constructor must contain at least one scalar value, and every ROW(...) in the same statement must contain the same number of values. A value can be a literal or an expression that returns one value. NULL is allowed; an empty ROW() is not and causes MySQL Error 3942. You can optionally append ORDER BY column_N and LIMIT row_count; the examples below show both clauses.

The standalone statement does not accept the DEFAULT keyword. DEFAULT can be used in an INSERT ... VALUES list instead; see the MySQL INSERT guide.

Return rows as a standalone result

Use VALUES ROW(...) to return rows without creating or reading a persistent table:

VALUES
  ROW(1, 'Laptop'),
  ROW(2, 'Mouse'),
  ROW(3, 'Keyboard');

MySQL names the result columns column_0, column_1, and so on, starting at zero:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Laptop   |
|        2 | Mouse    |
|        3 | Keyboard |
+----------+----------+

Sort or limit the result

Use the generated column names in ORDER BY. LIMIT restricts how many rows the statement returns:

VALUES
  ROW(3, 'Keyboard'),
  ROW(1, 'Laptop'),
  ROW(2, 'Mouse')
ORDER BY column_0
LIMIT 2;

This returns the rows with column_0 values 1 and 2. For a table expression with more descriptive names, supply a table alias and column aliases:

SELECT products.product_id, products.product_name
FROM (
  VALUES ROW(1, 'Laptop'), ROW(2, 'Mouse')
) AS products(product_id, product_name)
ORDER BY products.product_id;

Use VALUES with INSERT

MySQL also accepts ROW(...) constructors as the source of an INSERT or REPLACE statement:

INSERT INTO inventory (product_id, product_name)
VALUES ROW(1, 'Laptop'), ROW(2, 'Mouse');

This is an INSERT: it writes rows to inventory. A standalone statement such as VALUES ROW(1, 'Laptop'); returns a result set instead. The traditional INSERT ... VALUES (1, 'Laptop') syntax remains available; see the MySQL INSERT guide.

Do not confuse the VALUES statement with VALUES()

The VALUES statement is not the VALUES(column_name) function used in some INSERT ... ON DUPLICATE KEY UPDATE examples. The statement returns rows as a table; the function refers to an inserted column value. See the MySQL VALUES() function guide or MySQL’s ON DUPLICATE KEY UPDATE documentation for that separate syntax.

For more MySQL SQL errors, see MySQL Error Troubleshooting.