[SOLVED] How to generate a multirow resultest without selecting from a table in MYSQL

Issue

You can easily generate a one row result-set with a MariaDB/MySQL query like so:

MariaDB [(none)]> SELECT 'Some value' AS some_col_name;
+---------------+
| some_col_name |
+---------------+
| Some value    |
+---------------+
1 row in set (0.000 sec)

However, I would like to do a similar thing, but generate multiple rows of data.

I came up with this:

SELECT 'row1-value1' AS col_name_1 , 'row1-value2' AS col_name_2, 'row1-value3' AS col_name_3 UNION ALL SELECT 'row2-value1' AS col_name_1, 'row2-value2' AS col_name_2, 'row2-value3' AS col_name_3;
+-------------+-------------+-------------+
| col_name_1  | col_name_2  | col_name_3  |
+-------------+-------------+-------------+
| row1-value1 | row1-value2 | row1-value3 |
| row2-value1 | row2-value2 | row2-value3 |
+-------------+-------------+-------------+
2 rows in set (0.000 sec)

Which works, but the query isn’t very nice. Is there a better way to do it either in MySQL or MariaDB?

Solution

The VALUES statement is new in MySQL 8.0:

VALUES ROW('row1-value1', 'row1-value2', 'row1-value3'),
       ROW('row2-value1', 'row2-value2', 'row2-value3');

Output:

+-------------+-------------+-------------+
| column_0    | column_1    | column_2    |
+-------------+-------------+-------------+
| row1-value1 | row1-value2 | row1-value3 |
| row2-value1 | row2-value2 | row2-value3 |
+-------------+-------------+-------------+

See https://dev.mysql.com/doc/refman/8.0/en/values.html

There doesn’t seem to be any syntax in the VALUES statement to set the column names. The columns are named implicitly column_N starting with 0.

You can name the columns with a workaround: use VALUES after UNION with another query, because the first query in a UNION determines the column names. Below is an example that uses another new feature of MySQL 8.0, applying a WHERE condition to a SELECT statement without any table reference. The SELECT therefore returns zero rows, and its only purpose is to name the columns.

mysql> SELECT null as a, null as b, null as c WHERE FALSE 
 UNION VALUES ROW('row1-value1', 'row1-value2', 'row1-value3'),
              ROW('row2-value1', 'row2-value2', 'row2-value3');
+-------------+-------------+-------------+
| a           | b           | c           |
+-------------+-------------+-------------+
| row1-value1 | row1-value2 | row1-value3 |
| row2-value1 | row2-value2 | row2-value3 |
+-------------+-------------+-------------+

Answered By – Bill Karwin

Answer Checked By – David Marino (BugsFixing Volunteer)