VALUES ROW()

VALUES ROW()

Table Value Constructors with VALUES ROW()

Since MySQL 8.0.19 524 , VALUES is a statement of its own, a table value constructor whose rows are written ROW(...). Alone, its columns are named column_0, column_1 and so on; as a derived table, you name them in a column list, giving you a lookup table without creating one:

VALUES as a labeled lookup table driving a LEFT JOIN, and ragged rowsSQL

SELECT s.status, s.label, COUNT(o.id) AS orders
FROM (VALUES ROW('paid', 'Paid'), ROW('shipped', 'On its way'),
             ROW('refunded', 'Refunded')) AS s (status, label)
LEFT JOIN orders AS o ON o.status = s.status
GROUP BY s.status, s.label;
VALUES ROW(1, 2), ROW(3);

Output

+----------+------------+--------+
| status   | label      | orders |
+----------+------------+--------+
| paid     | Paid       |      3 |
| shipped  | On its way |      4 |
| refunded | Refunded   |      0 |
+----------+------------+--------+
3 rows in set (0.001 sec)
ERROR 1136 (21S01): Column count doesn't match value count at row 2

refunded shows a zero because the constructed table drives the join. Likewise, (VALUES ROW(1), ROW(5), ROW(42)) AS v (id) LEFT JOIN products with WHERE p.id IS NULL finds the pasted id 42 that has no product. Rows need equal lengths, and ROW is compulsory: bare VALUES (1, 2) is a syntax error. The sibling TABLE categories abbreviates SELECT * FROM categories and accepts ORDER BY and LIMIT.

MENU