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.
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.
Your UNION version is perfectly fine. As alternative with MariaDB you could use VALUES:
VALUES(1),(2),(3);