SELECT on an array of constants

Question

In MySQL, how is it possible to do a SELECT on an array of constant values? Something similar to this:

SELECT ['1', '2', '3'] AS ID;

Where the desired output is:

+-----+
| ID  |
+-----+
| 1   |
| 2   |
| 3   |

I would like to do this with ~20 values, so if there is a solution that is simpler than a tower of `UNION`s, would use that :) — Daniel Szalay, Sep 12 '14 at 09:33
Have you searched through SO? [Look here](http://stackoverflow.com/questions/2201170/how-to-select-multiple-rows-filled-with-constants) — Kleskowy, Sep 12 '14 at 09:50
@GeorgeGarchagudashvili I want to use those values later for `JOIN`s. I know I can just use a temporary table, but I am curious if such a syntax exists for MySQL. — Daniel Szalay, Sep 12 '14 at 10:01
No you can't do it with easy line of codes, but can be done with procedures. don't you use any other programming lang? for generating JSON? or directly from mysql? — George G, Sep 12 '14 at 10:35

Kickstart · Accepted Answer · 2014-09-12T11:17:18.683

You can use joins to generate a series of rows.

SELECT 1 AS ID UNION SELECT 2 UNION SELECT 3

For small amounts of data this works well. Down side is that there is no index to check these on.

A temp table is likely far better for this as at least any SQL that joins against these values can then use indexes.

Another possibility if the values are from a limited pool is to just have a table which contains all possible values and select from it:-

SELECT ID 
FROM all_poss_values
WHERE ID IN (1,2,3)

or using a generated range of values (which again loses the availability of indexes):-

SELECT 1 + units.i + tens.i * 10 AS ID
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units,
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
WHERE 1 + units.i + tens.i * 10 IN (1,2,3)

SELECT on an array of constants

1 Answers1