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   |

Answer 1

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)