How to generate a multirow resultest without selecting from a table in MYSQL-CodePudding

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?

CodePudding user response：

You can use a CTE where you name the columns that you want and generate the column values with the VALUES statement and the ROW() row constructor clause:

WITH cte(col_name_1, col_name_2, col_name_3) AS (VALUES
  ROW('row1-value1', 'row1-value2', 'row1-value3'),
  ROW('row2-value1', 'row2-value2', 'row2-value3')
)
SELECT * FROM cte;

Result:

col_name_1	col_name_2	col_name_3
row1-value1	row1-value2	row1-value3
row2-value1	row2-value2	row2-value3

See the demo.

CodePudding user response：

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 |
 ------------- ------------- -------------