Using SELECT result in subselect IN()

Question

The following query works (example query below)

SELECT cat, dog, mouse, ids,
    (SELECT cheese
    FROM my_second_table AS a
    WHERE a.ids IN (1,2,3,4,5,6,7,8,9,10)
    LIMIT 1) AS cheese
FROM my_first_table

It gives me back the 5 columns with the correct data as I'd expect, including the cheese column.

The following query does not work, cheese column is always empty

SELECT cat, dog, mouse, ids,
    (SELECT cheese
    FROM my_second_table AS a
    WHERE a.ids IN (ids)
    LIMIT 1) AS cheese
FROM my_first_table

ids contains the same comma separated numbers as in the first query above, retrieved from each row in the my_first_table.

I feel this should work and that I am doing something wrong. Is it OK to use the SELECT results in a Sub-Select IN() clause?

I guess `ids` is a single string value. It won't work like that. — Jakub Matczak, Jul 14 '14 at 11:43
@dragoste Correct, why not? It's just `1,2,3,4,5,6,7,8,9,10` — superphonic, Jul 14 '14 at 11:44
Because it's not PHP or smth like that. ;-) MySQL puts it into the query like `\`1,2,3,4,5,6,7,8,9,10\`` - escaped, as single value. — Jakub Matczak, Jul 14 '14 at 11:46

Sadikhasan · Accepted Answer · 2014-07-14T11:56:52.893

2

Try with find_in_set function like

SELECT cat, dog, mouse, ids,
    (SELECT cheese
    FROM my_second_table AS a
    WHERE find_in_set(a.ids, ids)
    LIMIT 1) AS cheese
FROM my_first_table

IN accepts a list or parameters to search, FIND_IN_SET accepts a string parameter containing a comma-separated list.

In your case ids are comma separated string so find_in_set will true and IN clause return false

edited Jul 14 '14 at 11:56

answered Jul 14 '14 at 11:46

Sadikhasan

18,365
21
80
122

@superphonic have tried this answer? – Sadikhasan Jul 14 '14 at 11:51
Yep just have, this works! Are there any drawbacks? What's the performance difference if any between this and `IN()` – superphonic Jul 14 '14 at 11:53
There is no any perfomance difference and check http://stackoverflow.com/questions/15656740/why-find-in-set-works-but-in-clause for reference – Sadikhasan Jul 14 '14 at 11:55

Using SELECT result in subselect IN()

1 Answers1