WITH A AS ( SELECT 'A' CD FROM DUAL UNION ALL SELECT 'C' CD FROM DUAL ) ,B AS ( SELECT 'DEFAULT' CD ,'77777' CD_NO FROM DUAL UNION ALL SELECT 'A' CD ,'9999' CD_NO FROM DUAL ) SELECT A.CD, B.CD_NO FROM A A, B B WHERE A.CD=B.CD(+) ;
조인 조건에 없는 C에 대해서는 "DEFAULT" 값인 77777이 표기 되었으면 합니다.
어떻게 조인 하면은 될지 문의 드립니다.
WITH A AS ( SELECT 'A' CD FROM DUAL UNION ALL SELECT 'C' FROM DUAL ), B AS ( SELECT 'DEFAULT' CD ,'77777' CD_NO FROM DUAL UNION ALL SELECT 'A' CD ,'9999' CD_NO FROM DUAL ) SELECT CD, CD_NO FROM ( SELECT A.CD, B.CD_NO, ROW_NUMBER() OVER(PARTITION BY A.CD ORDER BY CASE WHEN A.CD = B.CD THEN 1 ELSE 2 END ) rn FROM A LEFT JOIN B ON A.CD = B.CD OR B.CD = 'DEFAULT' ) tbl WHERE rn = 1