CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 8109

Select from array in MySQL

Is it possible in MySQL to select from a static set of integers? Given an array of integers like 1 and 2, what is the correct syntax in MySQL to do something like:

select 
    * 
from 
    (values(1),(2))

Which should return one row containing the number 1 and one row containing the number 2.

In other SQL than MySQL (e.g. MSSQL) this is possible. Is there a way to do this in MySQL?

Upvotes: 11

Views: 35367

Answers (3)

Kaii
Kaii

Reputation: 20540

The only way to create a virtual set in MySQL is using a subquery with UNION. The subquery in the FROM clause creates 3 rows which can be joined with another table:

SELECT foo.*
FROM (
    SELECT 1 AS num UNION
    SELECT 2 AS num UNION
    SELECT 3 AS num
) virtual
LEFT JOIN foo ON foo.num = virtual.num

When you want to use your list of values as a condition for WHERE or ON clause, then the IN() construct may be the right way to go:

SELECT foo.* FROM foo WHERE foo.num IN (1,2,3)

Upvotes: 14

ALI ATTALAOUI
ALI ATTALAOUI

Reputation: 21

sorry for my english

you can use (IN) like this 

SELECT * FROM Table WHERE id IN (1,2,3....)

Upvotes: 1

Michael
Michael

Reputation: 12806

I think you mean something like this?

SELECT 1 UNION SELECT 2 UNION SELECT 3

Upvotes: 13

Related Questions