* 'ALL'인 경우에는 모든 값을 값이 명시된 경우에는 해당값만 가져와라.
WITH T1 AS (
SELECT 1 C1,'1' C2 FROM DUAL UNION ALL
SELECT 1 ,'2' FROM DUAL UNION ALL
SELECT 1 ,'3' FROM DUAL UNION ALL
SELECT 1 ,'4' FROM DUAL UNION ALL
SELECT 1 ,'5' FROM DUAL UNION ALL
SELECT 2 ,'1' FROM DUAL UNION ALL
SELECT 2 ,'2' FROM DUAL UNION ALL
SELECT 2 ,'3' FROM DUAL UNION ALL
SELECT 2 ,'4' FROM DUAL UNION ALL
SELECT 2 ,'5' FROM DUAL UNION ALL
SELECT 3 ,'1' FROM DUAL UNION ALL
SELECT 3 ,'2' FROM DUAL UNION ALL
SELECT 3 ,'3' FROM DUAL
),
T2 AS (
SELECT 1 C1,'ALL' C2 FROM DUAL UNION ALL
SELECT 2 C1,'2' C2 FROM DUAL UNION ALL
SELECT 3 C1,'ALL' C2 FROM DUAL
)
SELECT
T1.C1,
T1.C2,
T2.C2
FROM T1, T2
WHERE 1=1
AND T1.C1 = T2.C1
AND T1.C2 = DECODE(T2.C2, 'ALL', T1.C2, T2.C2);