DB는 postgreSQL에서 실행했습니다.
좋은방법이라기보다는 좀 다른방법으로 작성해봣어요
WITH TEST1 (NAME,CLASS) AS ( SELECT '안정환', '1' UNION ALL SELECT '이천수', '1' UNION ALL SELECT '최용수', '2' UNION ALL SELECT '박지성', '2' ) ,TEST2 (NAME) AS ( SELECT '안정환' ) ,TEST3 (CLASS) AS ( SELECT CLASS FROM TEST1 A ,TEST2 B WHERE A.NAME = B.NAME GROUP BY CLASS ) SELECT A.NAME,A.CLASS FROM TEST1 A ,TEST3 B WHERE A.CLASS = B.CLASS