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
SQL
복사
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
SQL
복사
아우터 조인을 하면서 GROUP BY를 할 때는 COUNT 대상 컬럼에 생각해볼 필요가 있으며, SUM 처리 시에도 적절하게 IFNULL 처리가 필요한지 고민해야 한다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-10-1
플래티넘 등급이면서 2020년 1월에 가입한 회원에 대해, 회원별 2020년 2월 주문 금액과 주문 건수를 보여주세요. 주문이 없는 회원도 출력해주세요.
•
대상 테이블: 회원(Member), 주문(Ord)
•
조회 조건
◦
JoinDtm이 2020년 1월이면서 MemberGd가 PLAT인 회원들의
◦
OrdDtm이 2020년 2월달 주문 데이터
•
조회 컬럼: MemberId ,NickNm ,OrdAmtSum ,OrdCnt
•
추가 조건
◦
MemberId별로 GROUP BY 처리합니다.
◦
OrdAmtSum은 MemberId별 OrdAmt의 합계입니다.
◦
OrdCnt는 MemberId별 주문 건수입니다.
◦
주문이 없는 회원은 OrdAmtSum과 OrdCnt를 0으로 처리하시오.
•
정렬 기준: MemberId로 오름차순 정렬하시오.
MemberId NickNm OrdAmtSum OrdCnt
-------- ----------- ---------- ------
M1001 Air20 155000.000 29
M1018 Green20 0.000 0
M1042 Stone20 0.000 0
M1051 Air21 0.000 0
M1053 Cloud21 0.000 0
… 생략 …
SQL
복사
Upper: 7. JOIN
