Use GROUP_CONCAT result in IN Clause Mysql

Question

I have a Query which returns comma separated integers like :

select GROUP_CONCAT(ids) from table2

now I want to use that result in another query like :

select * from table1 where column in (select GROUP_CONCAT(ids) from table2)

in this case it will consider only first value in IN clause.

Filipe Silva · Accepted Answer · 2013-11-15 12:02:15Z

10

I agree with @Alma that this can't be done with IN, you might be able to do it with FIND_IN_SET, but if you can do it with IN it's probably a better approach :

SELECT *
FROM table1
WHERE find_in_set(ids, (
      SELECT GROUP_CONCAT(ids)
      FROM table2
      )) != 0;

sqlfiddle demo

answered Nov 15, 2013 at 12:02

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M Khalid Junaid Over a year ago

its not a good solution when you can directly use IN() , GROUP_CONCAT is not advisable because it has a character limit you need to increase that one if you used in your answer you need to brief also,when it can be done in simple way then why make set first then apply find_in_set it makes sense ?

Filipe Silva Over a year ago

i'm not saying it is a good approach. i'm advising to use IN instead, but i think this is what he was asking for.

pitu Over a year ago

is there any other way, to use in in where clause for the group_concat() values other then find_in_set

M Khalid Junaid · Accepted Answer · 2013-11-15 11:53:31Z

2

Any special reason not using a join and using a sub query

select * from table1 t1
JOIN table2 t2 on (t2.column = t1.ids)

answered Nov 15, 2013 at 11:53

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

2 Comments

Mm-Art-In Over a year ago

why should this be done?

Aakash Sahai Over a year ago

These types of queries(group_concat+find_in_set) are useful or needed when you are storing the output of group_concat in a variable and re-using them multiple times on different places in programmatic approach, thereby skipping additional JOIN load on multiple query to improve performance, generally in STORED PROCEDURES and/or FUNCTIONS

Collectives™ on Stack Overflow

Use GROUP_CONCAT result in IN Clause Mysql

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related