꿈꾸는 개발자, DBA 커뮤니티 구루비

Left outer join 문의 드립니다.

by 동희 [SQL Query] outer join [2023.08.21 16:37:29]

WITH TMP_A AS (
SELECT 'A' CD, '20230101' STR_DY, '20230115' END_DY FROM DUAL UNION ALL
SELECT 'B' CD, '20230101' STR_DY, '20230105' END_DY FROM DUAL UNION ALL
SELECT 'C' CD, '20230201' STR_DY, '20230215' END_DY FROM DUAL UNION ALL
SELECT 'D' CD, '20230310' STR_DY, '20230330' END_DY FROM DUAL
)
, TMP_B AS (
SELECT 'A' CD, '20221201' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'A' CD, '20230105' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'B' CD, '20230120' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'C' CD, '20230201' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'C' CD, '20230202' DY, '02' PRC_CD FROM DUAL UNION ALL
SELECT 'D' CD, '20230310' DY, '02' PRC_CD FROM DUAL UNION ALL
SELECT 'D' CD, '20230314' DY, '04' PRC_CD FROM DUAL
)
, TMP_C AS (
SELECT '01' PRC_CD, 'DEV01' GB_CD FROM DUAL UNION ALL
SELECT '02' PRC_CD, 'DEV02' GB_CD FROM DUAL UNION ALL
SELECT '03' PRC_CD, 'DEV03' GB_CD FROM DUAL UNION ALL
SELECT '04' PRC_CD, 'DEV04' GB_CD FROM DUAL
)
SELECT * 
  FROM TMP_A A
     , TMP_B B
     , TMP_C C
 WHERE B.CD(+) = A.CD
   AND B.DY(+) BETWEEN A.STR_DY AND A.END_DY
   /* 여기 이부분 */
   AND C.PRC_CD(+) = B.PRC_CD
   AND C.GB_CD(+) IN ('DEV01', 'DEV02')
   
ORDER BY A.CD, A.STR_DY, B.DY, B.PRC_CD
;

다음과 같은 자료가 있을때..

TMP_A는 전부 가져오고 TMP_B와 TMP_C는 조인으로 되어야 할 경우 outer 조인을 어떻게 줘야 하나요?

원하는 결과

CD	STR_DY	END_DY	CD	DY	PRC_CD	PRC_CD	GB_CD
A	20230101	20230115	A	20230105	01	01	DEV01
B	20230101	20230105
C	20230201	20230215	C	20230201	01	01	DEV01
C	20230201	20230215	C	20230202	02	02	DEV02
D	20230310	20230330	D	20230310	02	02	DEV02

이런 결과를 원하는데 한방으로는 잘 안되네요..ㅠㅠ

SELECT * 
  FROM TMP_A A
  LEFT JOIN
      (SELECT A.CD
            , B.DY
            , C.PRC_CD
            , C.GB_CD
         FROM TMP_A A
            , TMP_B B
            , TMP_C C
        WHERE B.CD = A.CD
          AND B.DY BETWEEN A.STR_DY AND A.END_DY
          AND C.PRC_CD = B.PRC_CD
          AND C.GB_CD IN ('DEV01', 'DEV02')
      ) B
  ON B.CD = A.CD    
ORDER BY A.CD, A.STR_DY, B.DY, B.PRC_CD
;

이렇게 TMP_A를 한번 더 사용하여 해결은 됬는데 좀더 좋은 방법이 없을까요?

by 우주민 [2023.08.21 16:45:38]

WITH TMP_A AS (
SELECT 'A' CD, '20230101' STR_DY, '20230115' END_DY FROM DUAL UNION ALL
SELECT 'B' CD, '20230101' STR_DY, '20230105' END_DY FROM DUAL UNION ALL
SELECT 'C' CD, '20230201' STR_DY, '20230215' END_DY FROM DUAL UNION ALL
SELECT 'D' CD, '20230310' STR_DY, '20230330' END_DY FROM DUAL
)
, TMP_B AS (
SELECT 'A' CD, '20221201' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'A' CD, '20230105' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'B' CD, '20230120' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'C' CD, '20230201' DY, '01' PRC_CD FROM DUAL UNION ALL
SELECT 'C' CD, '20230202' DY, '02' PRC_CD FROM DUAL UNION ALL
SELECT 'D' CD, '20230310' DY, '02' PRC_CD FROM DUAL UNION ALL
SELECT 'D' CD, '20230314' DY, '04' PRC_CD FROM DUAL
)
, TMP_C AS (
SELECT '01' PRC_CD, 'DEV01' GB_CD FROM DUAL UNION ALL
SELECT '02' PRC_CD, 'DEV02' GB_CD FROM DUAL UNION ALL
SELECT '03' PRC_CD, 'DEV03' GB_CD FROM DUAL UNION ALL
SELECT '04' PRC_CD, 'DEV04' GB_CD FROM DUAL
)
SELECT * 
  FROM TMP_A A
LEFT OUTER JOIN TMP_B B
ON B.CD = A.CD
AND B.DY BETWEEN A.STR_DY AND A.END_DY
LEFT OUTER JOIN  TMP_C C
ON C.PRC_CD = B.PRC_CD
AND C.GB_CD IN ('DEV01', 'DEV02')   
ORDER BY A.CD, A.STR_DY, B.DY, B.PRC_CD

그냥 A 테이블에 B 테이블을 LEFT OUTER JOIN 하고

C 테이블을 B 테이블에 LEFT OUTER JOIN 하면 될꺼 같은데요

/ 그런데 처음에 만드셨던 쿼리 정상적으로 데이터 도출하지 않나요???

by 우주민 [2023.08.21 17:03:28]

제가 질문을 잘못 이해하고 있었네요. ㅎㅎ

결과값에 맞는 쿼리로 수정해봤습니다.

SELECT A.*, B.*, C.*
FROM TMP_B B
INNER JOIN TMP_C C
ON C.PRC_CD = B.PRC_CD
AND C.GB_CD IN ('DEV01','DEV02')
RIGHT OUTER JOIN TMP_A A
ON B.CD = A.CD
AND A.DY BETWEEN A.STR_DY AND A.END_DY

by 동희 [2023.08.21 17:12:26]

우주민님 답변 감사드립니다..^^

데이터베이스 질문과 답변