In MySQL, the VALUES statement returns a set of one or more rows as a table. Basically, it’s a table value constructor in accordance with the SQL standard, which also functions as a standalone SQL statement.
The VALUES statement was introduced in MySQL 8.0.19.
Syntax
The official syntax goes like this:
VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
value_list:
value[, value][, ...]
column_designator:
column_indexExample
Here’s a simple example to demonstrate how it works:
VALUES ROW(1, 2, 3), ROW(4, 5, 6);Result:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 4 | 5 | 6 | +----------+----------+----------+
The resulting columns are implicitly named column_0, column_1, column_2, and so on, always beginning with 0.
We can see that each ROW() row constructor clause results in a new row in the resulting table.
Each ROW() contains a value list of one or more scalar values enclosed in the parentheses. A value can be a literal of any MySQL data type or an expression that resolves to a scalar value.
Therefore we can also do the following:
VALUES ROW("Black", "Cat"), ROW("Yellow", "Dog");Result:
+----------+----------+ | column_0 | column_1 | +----------+----------+ | Black | Cat | | Yellow | Dog | +----------+----------+
Or stuff like this:
VALUES
ROW(CURDATE(), DATE_ADD(CURDATE(), INTERVAL 10 YEAR)),
ROW(CURTIME(), DATE_ADD(CURTIME(), INTERVAL 2 HOUR));Result:
+---------------------+---------------------+ | column_0 | column_1 | +---------------------+---------------------+ | 2022-02-17 00:00:00 | 2032-02-17 00:00:00 | | 2022-02-17 09:30:46 | 2022-02-17 11:30:46 | +---------------------+---------------------+
The ORDER BY Clause
The syntax allows for the use of the ORDER BY clause in order to order the results. However, I’ve found that the ORDER BY clause doesn’t work as expected on the systems I’ve tried running it against.
Here’s how it should work (according to the MySQL documentation):
VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;Result:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | -2 | 3 | | 4 | 6 | 8 | | 5 | 7 | 9 | +----------+----------+----------+
But on the two systems that I ran that statement against (MySQL 8.0.26 on Ubuntu 20.04.3 and MySQL 8.0.27 Homebrew on MacOS Monterery), the ORDER BY clause doesn’t work at all. Perhaps this is a bug.
The LIMIT Clause
We can use the LIMIT clause to limit the number of rows that are output:
VALUES
ROW('Black', 'Cat'),
ROW('Yellow', 'Dog'),
ROW('Aqua', 'Fish')
LIMIT 2;Result:
+----------+----------+ | column_0 | column_1 | +----------+----------+ | Black | Cat | | Yellow | Dog | +----------+----------+
With a SELECT Statement
We can also use the VALUES statement within a SELECT statement, as if the VALUES table constructor were an actual table:
SELECT
PetName,
PetType
FROM
(VALUES
ROW(1, "Fluffy", "Cat"),
ROW(2, "Bark", "Dog"),
ROW(3, "Gallop", "Horse")
) AS Pets(PetId, PetName, PetType)
WHERE PetId = 2;Result:
+---------+---------+ | PetName | PetType | +---------+---------+ | Bark | Dog | +---------+---------+
ROW() Cannot be Empty
A row constructor cannot be empty, unless it’s being used as source in an INSERT statement.
Here’s what happens if we try to use an empty row constructor:
VALUES ROW();Result:
ERROR 3942 (HY000): Each row of a VALUES clause must have at least one column, unless when used as source in an INSERT statement.
ROW() Can Contain Null Values
Although row constructors cannot be empty, they can contain Null values:
VALUES ROW(null, null);Result:
+----------+----------+ | column_0 | column_1 | +----------+----------+ | NULL | NULL | +----------+----------+
Each ROW() Must Contain the Same Number of Values
Each ROW() in the same VALUES statement must have the same number of values in its value list.
Therefore, we can’t do this:
VALUES ROW(1, 2), ROW(3);Result:
ERROR 1136 (21S01): Column count doesn't match value count at row 2
Using VALUES to Insert Data
We can use the VALUES statement in conjunction with the INSERT and REPLACE statements to insert data into a table.
Example:
INSERT INTO Pets VALUES
ROW(9, 3, 1, 'Woof', '2020-10-03'),
ROW(10, 4, 5, 'Ears', '2022-01-11');That inserted two rows into a table called Pets. This assumes that the table already exists.
We can now use a SELECT statement to see the new values in the table:
SELECT * FROM Pets
WHERE PetId IN (9, 10);Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 9 | 3 | 1 | Woof | 2020-10-03 | | 10 | 4 | 5 | Ears | 2022-01-11 | +-------+-----------+---------+---------+------------+
The above INSERT statement is the equivalent of doing the following:
INSERT INTO Pets VALUES
(9, 3, 1, 'Woof', '2020-10-03'),
(10, 4, 5, 'Ears', '2022-01-11');When Creating Tables
The VALUES statement can also be used in place of the source table in CREATE TABLE … SELECT and CREATE VIEW … SELECT statements.
Here’s an example:
CREATE TABLE t1 VALUES ROW(1,2,3), ROW(4,5,6);
SELECT * FROM t1;Result:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 4 | 5 | 6 | +----------+----------+----------+
We can also do this:
CREATE TABLE t2 SELECT * FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS v;
SELECT * FROM t2;Result:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 4 | 5 | 6 | +----------+----------+----------+
Those two CREATE TABLE statements are like doing this:
CREATE TABLE t3 SELECT * FROM t2;
SELECT * FROM t3;Result:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 4 | 5 | 6 | +----------+----------+----------+
In this case, I used the t2 table as the source table, instead of providing the values in a VALUES statement.