--1번 WITH TMP1 AS ( SELECT 'A' CD, 'AA' CD2 FROM DUAL ), TMP2 AS ( SELECT 'A' CD, '과일' NM FROM DUAL UNION ALL SELECT 'B' CD, '야채' NM FROM DUAL UNION ALL SELECT 'C' CD, '고기' NM FROM DUAL ), TMP3 AS ( SELECT 'AA' CD, '사과' NM FROM DUAL ) SELECT * FROM TMP1, TMP2, TMP3 WHERE TMP1.CD2 = TMP3.CD AND TMP1.CD(+) = TMP2.CD ; --2번 WITH TMP1 AS ( SELECT 'A' CD, 'AA' CD2 FROM DUAL ), TMP2 AS ( SELECT 'A' CD, '과일' NM FROM DUAL UNION ALL SELECT 'B' CD, '야채' NM FROM DUAL UNION ALL SELECT 'C' CD, '고기' NM FROM DUAL ), TMP3 AS ( SELECT 'AA' CD, '사과' NM FROM DUAL ) SELECT * FROM ( SELECT TMP1.CD , TMP3.NM FROM TMP1, TMP3 WHERE TMP1.CD2 = TMP3.CD ) TMP1, TMP2 WHERE TMP1.CD(+) = TMP2.CD ;
왜 1번은 안되는지 이해가 안되네요...
원하는 결과는 2번 처럼 나오는 겁니다..도움 부탁드립니다..