group_concat 으로 저장된 값을 이용한 where in 조회 방법

예상 시나리오

1.

DB : A / TABLE : test1

no	type	value
1	a	Apple
2	b	Banana
3	c	Car

DB : B / TABLE : test2

no	member	option
1	Jeffrey	1,2
2	Worker	1
3	Joy	2,3
4	Brian	3

SELECT BB.member, BB.option, (SELECT CONCAT('[', GROUP_CONCAT(AA.`value` ORDER BY AA.`no` SEPARATOR ']['), ']') str
FROM A.test1 AA WHERE `no` IN (option)) str
FROM B.test2 BB

하지만 예상했던 결과는 NULL로 가득찼고 option이 1개 인 경우만 출력이 되었다.

Result

member	option	str
Jeffrey	1,2	(NULL)
Worker	1	[Apple]
Joy	2,3	(NULL)
Brian	3	[Car]

그래서 FIND_IN_SET 내장함수를 이용했다

---

2.

SELECT BB.member, BB.option, (SELECT CONCAT('[', GROUP_CONCAT(AA.`value` ORDER BY AA.`no` SEPARATOR ']['), ']') str
FROM A.test1 AA WHERE FIND_IN_SET(`no`, option)) str
FROM B.test2 BB

Result

member	option	str
Jeffrey	1,2	[Apple][Banana]
Worker	1	[Apple]
Joy	2,3	[Banana][Car]
Brian	3	[Car]

출처 : [https://stackoverflow.com/questions/10909623/is-it-possible-in-mysql-to-use-in-statement-with-varchar-column]