오라클 FULL OUTER JOIN -> MariaDB에서 표현을 어떻게하나요..?? 0 3 4,064

by 꼬막 [SQL Query] Oracle MariaDB [2021.08.25 10:11:08]


오라클에서 마리아디비로 열심히 이관중인 신입입니다..

아래와같이 FULL OUTER JOIN의 쿼리가있는데, 마리아디비에선 어떻게 표현해야 할까요..?!

SELECT NVL(B1.SITE_CODE, B2.SITE_CODE) AS SITE_CODE, NVL(B1.YYYYMM, B2.ACT_YYYYMM) AS ACT_YYYYMM, B1.SCOPE_TYPE_CODE
     , CASE WHEN COUNT(DECODE(B2.SITE_CODE,  NULL, '', 1)) = 0 THEN '1'
            WHEN COUNT(DECODE(B2.INPUT_FLAG, NULL, 1)) > 0 THEN '2'
            ELSE '3'
        END AS STATUS_CODE
  FROM ( SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
           FROM E05_ACT_MON_DATA A1
          WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
            AND A1.SITE_CODE    = '1'
       ) B2 FULL OUTER JOIN
       ( SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
           FROM E05_ESRC A0
              , E05_ESRC_DETAIL A1
              , COPY_YYYYMM A2
          WHERE A1.ESRC_CODE = A0.ESRC_CODE
            AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
            AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
            AND A1.SCOPE_TYPE_CODE IS NOT NULL
            AND A0.SITE_CODE    = '1'
       ) B1
      ON B1.SITE_CODE       = B2.SITE_CODE
      AND B1.ESRC_CODE       = B2.ESRC_CODE
      AND B1.YYYYMM          = B2.ACT_YYYYMM
      AND B1.MET_CODE        = B2.MET_CODE
      AND B1.GHG_CODE        = B2.GHG_CODE
      AND B1.ENERGY_CODE     = B2.ENERGY_CODE
      AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
    GROUP BY NVL(B1.SITE_CODE, B2.SITE_CODE), NVL(B1.YYYYMM, B2.ACT_YYYYMM), B1.SCOPE_TYPE_CODE;

 

by allen [2021.08.25 10:22:25]

B2 LEFT JOIN B1

UNION 

B2 RIGHT JOIN B2


by 꼬막 [2021.08.25 10:26:49]
SELECT *
                  FROM (
                        SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
                          FROM E05_ACT_MON_DATA A1
                         WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
                           AND A1.SITE_CODE    = '1'
                     ) B2 LEFT JOIN
                     (
                        SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
                          FROM E05_ESRC A0
                             , E05_ESRC_DETAIL A1
                             , COPY_YYYYMM A2
                         WHERE A1.ESRC_CODE = A0.ESRC_CODE
                           AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
                           AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
                           AND A1.SCOPE_TYPE_CODE IS NOT NULL
                           AND A0.SITE_CODE    = '1'
                     ) B1
                    ON B1.SITE_CODE       = B2.SITE_CODE
                   AND B1.ESRC_CODE       = B2.ESRC_CODE
                   AND B1.YYYYMM          = B2.ACT_YYYYMM
                   AND B1.MET_CODE        = B2.MET_CODE
                   AND B1.GHG_CODE        = B2.GHG_CODE
                   AND B1.ENERGY_CODE     = B2.ENERGY_CODE
                   AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
                 UNION
                 SELECT *
                  FROM (
                        SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
                          FROM E05_ACT_MON_DATA A1
                         WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
                           AND A1.SITE_CODE    = '1'
                     ) B2 RIGHT JOIN
                     (
                        SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
                          FROM E05_ESRC A0
                             , E05_ESRC_DETAIL A1
                             , COPY_YYYYMM A2
                         WHERE A1.ESRC_CODE = A0.ESRC_CODE
                           AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
                           AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
                           AND A1.SCOPE_TYPE_CODE IS NOT NULL
                           AND A0.SITE_CODE    = '1'
                     ) B1
                    ON B1.SITE_CODE       = B2.SITE_CODE
                   AND B1.ESRC_CODE       = B2.ESRC_CODE
                   AND B1.YYYYMM          = B2.ACT_YYYYMM
                   AND B1.MET_CODE        = B2.MET_CODE
                   AND B1.GHG_CODE        = B2.GHG_CODE
                   AND B1.ENERGY_CODE     = B2.ENERGY_CODE
                   AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
                   ;

 

이런식으로 사용했는데, 기존 FULL 데이터 갯수랑, 안맞는건 어쩔수없는걸까요..??


by 마농 [2021.08.25 11:09:38]

원본 쿼리 의문점(1) scope_type_code
- scope_type_code 는 조인 조건에 없네요?
- scope_type_code 는 SELECT 에서 NVL 처리를 안했네요?
원본 쿼리 의문점(2) status_code
- 1. b2 가 하나도 없으면(즉, b1 만 있으면) -> 1
- 2. b2 가 없는게 있으면 -> 2
- 3. 기타(b2 가 다 있는 경우) -> 3
- 이게 뭘 의미하는 건가요?
원본 쿼리 의문점(3)
- 전반적인 쿼리의 의미를 알 수 있을지?
- 두 집합 b1 과 b2 의 의미
- 두 집합 b1 과 b2 의 관계가 어떻게 되나요? (1:1) (1:다) (다:1) (다:다)
- full outer join 이 필요한 걸까요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입