11

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?

Improve this question
7
  • would you please elaborate it more ? what is " (values(1),(2)) as a" ? Commented Mar 25, 2012 at 13:03
  • 1
    Your question isn't very clear. What does values(1),(2) represent? Commented Mar 25, 2012 at 13:04
  • If you mean select specific attributes, then of course you can. If you mean that one attribute value IS an array, then I don't think so, but I'm not sure. Commented Mar 25, 2012 at 13:04
  • In MSSQL this syntax is possible. 1 and 2 are example numbers of my array. Commented Mar 25, 2012 at 13:06
  • No, these are constant values. Suppose, that I want to select from integers. Commented Mar 25, 2012 at 13:09

3 Answers 3

Reset to default
14

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Very helpful, got to give the correct answer to michael, he has helped me to put the right question.
Guess it's the way to select from a subquery: select id from db1 where url in (select url from db2)
13

I think you mean something like this?

SELECT 1 UNION SELECT 2 UNION SELECT 3
Improve this answer

2 Comments

No worries. Sorry for being initially so dumb to your question.
I always forget to add the select. pretty simply but so annoying. thanks for reminding me
0

sorry for my english

you can use (IN) like this 

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

1 Comment

this answer is wrong, this is not what they are asking about

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.