Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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.

like image 686
zhangv Avatar asked Sep 03 '25 16:09

zhangv


2 Answers

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.

like image 119
Gordon Linoff Avatar answered Sep 05 '25 05:09

Gordon Linoff


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

like image 39
Lukasz Szozda Avatar answered Sep 05 '25 07:09

Lukasz Szozda