how to select multiple static rows with one SQL query in mysql

Question

We all know that:

select 1,2,3

will give us:

|1|2|3|

However, what I want to get is:

|1|
|2|
|3|

I am aware that it can be achieved by "union"s like:

select 1 union select 2 union select 3

And WITHOUT the temporary table or predefined table.

Is there any better solution? Thanks.

Answer 1

Your solution is basically fine, but you should use union all:

select 1 union all select 2 union all select 3

union incurs overhead for removing duplicates, and you don't want to do that.

An alternative solution would be to use a numbers table, if you have one handy.

Another solution would be to take any sufficiently large table and use:

select (@rn := @rn + 1) as n
from t cross join
     (select @rn := 0) params
limit 3;

This is handy if you want to expand to larger numbers.

Answer 2

Your UNION version is perfectly fine. As alternative with MariaDB 10.3.3 or later you can use VALUES:

VALUES(1),(2),(3);

db<>fiddle