The MySQL BETWEEN OperatorThe BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
If you want to select only specific columns, replace the * with the names of the columns, separated by commas. The following statement selects just the name_id, firstname and lastname fields from the master_name table.
VALUES is a DML statement introduced in MySQL 8.0. 19 which returns a set of one or more rows as a table. In other words, it is a table value constructor which also functions as a standalone SQL statement.
The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.
From the link you provided :
VALUES (1, 'one'), (2, 'two'), (3, 'three');
This will return a table of two columns and three rows. It's effectively equivalent to:
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
So you need
select * from
table1,
(
SELECT 1 AS val
UNION ALL
SELECT 2
UNION ALL
SELECT 3
)b
In MySQL 8.0.19, this syntax is supported. please refer to this official link
mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 1 | -2 | 3 |
| 5 | 7 | 9 |
| 4 | 6 | 8 |
+----------+----------+----------+
This is another way to get around the lack of WITH
support in MySQL
:
create temporary table tmp (c int);
insert into tmp (c)
values (1), (2), (3);
select * from tmp;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With