7-10. OUTER JOIN과 집계함수

OUTER JOIN과 COUNT

아우터 조인에서 COUNT(*)와 COUNT(참조집합컬럼)에 따라 집계 결과가 다름에 주의가 필요하다.

COUNT(컬럼명)은 컬럼 값이 NULL이면 0으로 카운트
COUNT(*)은 컬럼 값 자체는 상관 없이 레코드 건수를 카운트

아래 SQL과 같이 아우터 조인만 수행 한 결과를 확인한 후에, 다시 GROUP BY와 COUNT를 수행해보자. COUNT(*)와 COUNT(참조집합컬럼)에 따라 결과가 다른 것을 알 수 있다.

-- [SQL-7-10-1] Shop과 Ord를 아우터 조인한 SQL
SELECT  T1.ShopId ,T1.ShopNm, T2.ShopId ,T2.OrdNo ,T2.OrdAmt
FROM    startdb.Shop T1
        LEFT OUTER JOIN startdb.Ord T2
        ON (T2.ShopId = T1.ShopId
           AND T2.OrdDtm >= STR_TO_DATE('20220120','%Y%m%d')
           AND T2.OrdDtm <  STR_TO_DATE('20220123','%Y%m%d'))
WHERE   T1.ShopId IN ('S001','S002','S003')
ORDER BY T1.ShopId;

ShopId  ShopNm           ShopId  OrdNo  OrdAmt    
------  ---------------  ------  -----  --------  
S001    New York-1st     NULL    NULL   NULL      
S002    Los Angeles-1st  NULL    NULL   NULL      
S003    Chicago-1st      S003    25728  4000.000  
S003    Chicago-1st      S003    25725  4000.000  
S003    Chicago-1st      S003    25722  8500.000  

-- [SQL-7-10-2] Shop과 Order를 아우터 조인 후 GROUP BY 처리
-- COUNT(*)과 COUNT(참조컬럼)의 결과가 다르다.
SELECT  T1.ShopId ,MAX(T1.ShopNm) ShopNm
        ,COUNT(*) CNT_ALL ,COUNT(T2.ShopId) CNT_COL_PK
FROM    startdb.Shop T1
        LEFT OUTER JOIN startdb.Ord T2
        ON (T2.ShopId = T1.ShopId
           AND T2.OrdDtm >= STR_TO_DATE('20220120','%Y%m%d')
           AND T2.OrdDtm <  STR_TO_DATE('20220123','%Y%m%d'))
WHERE   T1.ShopId IN ('S001','S002','S003')
GROUP BY T1.ShopID
ORDER BY T1.ShopId;

ShopId  ShopNm           CNT_ALL  CNT_COL_PK  
------  ---------------  -------  ----------  
S001    New York-1st     1        0           
S002    Los Angeles-1st  1        0           
S003    Chicago-1st      3        3

Untitled

OUTER JOIN과 SUM

아우터 조인 후 참조집합의 SUM 처리 역시 주의가 필요하다. SUM은 집계 대상이 모두 NULL이면 NULL 값으로 출력된다.

단, SUM 처리 대상 중에 수치가 하나라도 존재하면 NULL은 무시된다.
- 일반 덧셈에서는 NULL + 1은 NULL이 되지만, NULL과 1을 SUM하면 결과는 1이다.

다음과 같이 참조집합의 수치를 SUM 할 때는 IFNULL을 사용한 치환을 고려해야 한다.

-- [SQL-7-10-3] 아우터 조인만 수행한 SQL
-- ShopId에 따른 OrdAmt 값에 주목하자.
SELECT  T1.ShopId ,T1.ShopNm, T2.ShopId ,T2.OrdNo ,T2.OrdAmt
FROM    startdb.Shop T1
        LEFT OUTER JOIN startdb.Ord T2
        ON (T2.ShopId = T1.ShopId
           AND T2.OrdDtm >= STR_TO_DATE('20220120','%Y%m%d')
           AND T2.OrdDtm <  STR_TO_DATE('20220123','%Y%m%d'))
WHERE   T1.ShopId IN ('S001','S002','S003')
ORDER BY T1.ShopId;

ShopId  ShopNm           ShopId  OrdNo  OrdAmt    
------  ---------------  ------  -----  --------  
S001    New York-1st     NULL    NULL   NULL      
S002    Los Angeles-1st  NULL    NULL   NULL      
S003    Chicago-1st      S003    25728  4000.000  
S003    Chicago-1st      S003    25725  4000.000  
S003    Chicago-1st      S003    25722  8500.000 

-- [SQL-7-10-4] 아우터 조인 후 GROUP BY SUM 처리
-- 상황에 따라 OrdAmt에 IFNULL 처리가 필요하다.
-- IFNULL을 제거하고 실행해보기 바란다.
SELECT  T1.ShopId ,MAX(T1.ShopNm) ShopNm
        ,IFNULL(SUM(T2.OrdAmt),0) OrdAmt
FROM    startdb.Shop T1
        LEFT OUTER JOIN startdb.Ord T2
        ON (T2.ShopId = T1.ShopId
           AND T2.OrdDtm >= STR_TO_DATE('20220120','%Y%m%d')
           AND T2.OrdDtm <  STR_TO_DATE('20220123','%Y%m%d'))
WHERE   T1.ShopId IN ('S001','S002','S003')
GROUP BY T1.ShopId
ORDER BY T1.ShopId;

ShopId  ShopNm           OrdAmt     
------  ---------------  ---------  
S001    New York-1st     0.000      
S002    Los Angeles-1st  0.000      
S003    Chicago-1st      16500.000

Untitled