MySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.

This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.

Syntax

The syntax for the VALUES DML statement 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_index

Each ROW(value_list) represents a row in the resulting table, and the value_list represents the columns in the row.

Example

Here’s an example of how we can use VALUES as a stand alone statement:

VALUES 
  ROW(1, 'Cat'), 
  ROW(2, 'Dog'), 
  ROW(3, 'Bird');

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Cat      |
|        2 | Dog      |
|        3 | Bird     |
+----------+----------+

So it basically provides us with a quick and simple way to create tabular data.

Each column is automatically assigned a name using the convention column_n, such as column_0, column_1, column_2, etc.

The above example is the equivalent of the following:

SELECT 1 AS column_0, 'Cat' AS column_1
UNION
SELECT 2, 'Dog' 
UNION
SELECT 3, 'Bird';

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Cat      |
|        2 | Dog      |
|        3 | Bird     |
+----------+----------+

In this case I had to use multiple UNION clauses to join the results of multiple SELECT statements. The VALUES DML statement provides a more concise method of achieving the same outcome.

The ORDER BY Clause

The syntax allows for an ORDER BY clause to sort the results of the VALUES statement. However, at the time of writing, MySQL has a bug that prevents this from working correctly.

Here’s how we can incorporate the ORDER BY clause into our VALUES statement:

VALUES 
  ROW(1, 'Cat'), 
  ROW(2, 'Dog'), 
  ROW(3, 'Bird')
ORDER BY column_1;

This should produce the following results:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 | Bird     |
|        1 | Cat      |
|        2 | Dog      |
+----------+----------+

However as mentioned, as I write this there’s a bug that prevents this from working correctly, and when I run it the results are not sorted. Hopefully this bug is fixed soon.

In the meantime, if you need to sort the results, you can place the VALUES statement into a subquery and sort the results of the outer query (example of this later).

The LIMIT Clause

We can use the LIMIT clause to limit the number of rows returned to the specified number:

VALUES 
  ROW(1, 'Cat'), 
  ROW(2, 'Dog'), 
  ROW(3, 'Bird')
LIMIT 2;

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Cat      |
|        2 | Dog      |
+----------+----------+

In this case I specified that only two rows should be returned.

Combining Multiple VALUES Statements

We can use the UNION clause to combine multiple VALUES statements, so that the results are combined into a single result set:

VALUES 
  ROW(1, 'Cat'), 
  ROW(2, 'Dog'), 
  ROW(3, 'Bird')
UNION
VALUES 
  ROW(4, 'Buffalo'), 
  ROW(5, 'Squirrel');

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Cat      |
|        2 | Dog      |
|        3 | Bird     |
|        4 | Buffalo  |
|        5 | Squirrel |
+----------+----------+

However, the above example could easily be made more concise simply by omitting the UNION and second VALUES statement, so that it’s just one big VALUES statement. In any case, it’s an option that’s available if needed.

Combining with a SELECT Statement

We can also use the UNION clause to combine the VALUES statement with a SELECT statement.

Here’s an example that produces the same output as the previous example:

VALUES 
  ROW(1, 'Cat'), 
  ROW(2, 'Dog'), 
  ROW(3, 'Bird')
UNION
SELECT 4, 'Buffalo'
UNION 
SELECT 5, 'Squirrel';

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 | Cat      |
|        2 | Dog      |
|        3 | Bird     |
|        4 | Buffalo  |
|        5 | Squirrel |
+----------+----------+

This also works with the TABLE statement.

We can use clauses such as INTERSECT and EXCEPT instead of UNION to produce different results as required.

Using VALUES in a Subquery

Even though we can use it without the need for a SELECT statement, we still have the option of including the VALUES statement in a subquery, like this:

SELECT column_1 
FROM (
    VALUES 
        ROW(1, 'Cat'), 
        ROW(2, 'Dog'), 
        ROW(3, 'Bird')
) AS t1;

Result:

+----------+
| column_1 |
+----------+
| Cat      |
| Dog      |
| Bird     |
+----------+

This means we can use WHERE and other clauses to extract data from the VALUES statement:

SELECT column_1 
FROM (
    VALUES 
        ROW(1, 'Cat'), 
        ROW(2, 'Dog'), 
        ROW(3, 'Bird')
) AS t1
WHERE column_0 = 2;

Result:

+----------+
| column_1 |
+----------+
| Dog      |
+----------+

It also means that we can use an ORDER BY clause to sort the results:

SELECT * 
FROM (
    VALUES 
        ROW(1, 'Cat'), 
        ROW(2, 'Dog'), 
        ROW(3, 'Bird')
) AS t1
ORDER BY column_1;

Result:

+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 | Bird     |
|        1 | Cat      |
|        2 | Dog      |
+----------+----------+