SQLBOOSTER - INNER JOIN

 

INNER-JOIN 이란

 

• 이너 조인은 조인 조건을 만족하는 데이터만 결합하여 결과를 내보낸다. (T1.CUS_ID = T2.CUS_ID)
• WHERE 절에서 사용하는 조건은 필터 조건과 조인 조건 두 가지가 있다. 
• FROM 절에 사용된 테이블이 두 개 이상이면 필터 조건과 조인 조건이 동시에 있을 수 있다.

SELECT T1.COL_1, T2_COL_1
FROM (
	SELECT 'A' COL_1 FROM DUAL UNION ALL
    SELECT 'B' COL_1 FROM DUAL UNION ALL
    SELECT 'A' COL_1 FROM DUAL 
)T1,
(
	SELECT 'A' COL_1 FROM DUAL UNION ALL
    SELECT 'B' COL_1 FROM DUAL UNION ALL
    SELECT 'B' COL_1 FROM DUAL UNION ALL
    SELECT 'D' COL_1 FROM DUAL 
)T2

WHERE T1.COL_1 = T2.COL_1;

• T1.COL_1의 속성값이 T2.COL_1의 속성 값과 하나씩 비교하면서 같은 것을 추림 
• 조인 조건을 만족하는 데이터만 결합되어 결과에 나올 수 있다.
  • 이때 조인 조건은 같다 뿐만 아니라 다른 조건식도 사용할 수 있다.
• 한 건과 M(Many)건이 조인되면 M건의 결과가 나온다.
  • T1의 B 한 건이 T2의 B 두 건과 결합해 두 건의 결과가 나왔다.

 

---

INNER_JOIN 처리 과정 

 

SELECT T1.CUS_ID, T1.CUS_GD, T2.ORD_SEQ, T2.CUS_ID, T2.ORD_DT
FROM M_CUS T1, T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.CUS_GD='A'
AND T2.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T2.ORD_DT < TO_DATE('20170201','YYYYMMDD');

 

 

1. M_CUS에서 CUS_GD가 A인 데이터만 찾는다 (필터 조건 처리)
2. M_CUS의 첫번째 로우인 CUS_1과 같은 CUS_ID가 T_ORD에 있는지 검색 
   1. T_ORD에 CUS_1이 존재하지만 ORD_DT가 2017년 2월이므로 조인에 실패 (이는 필터 조건 2017 1월 주문만 처리하는 조건 때문이다.) [조인조건]
3. M_CUS의 세 번째 로우인 CUS_3과 같은 CUS_ID T_ORD에 있는지 검색한다. 
4. ..이를 반복 

 

필터조건을 먼저 처리하고 난 뒤에 이를 만족한 데이터에 한해서 조인에 참여한다. 

또한, 1:M으로 조인하면, 결과는 M의 값이 나온다는 점도 다시 한번 알아 두자 

 

위 SQL에서 두 조인의 순서를 완전히 뒤바꿔도 같은 결과가 나올 것이다.

INNER 조인은 순서를 바꾼다고 결과가 달라지지 않는다. (성능에는 영향이 있을 수도 있다)

 

*조인 조건을 보통 =으로 두어 같은 값 끼리만 조인한다고 생각할 수 있는데, inner  join은 같은 값을 가진 데이터를 비교하는게 아니라, 조인 조건을 만족하는 데이터 간 조인이다.

 

---

 

여러 테이블의 조인 

 

• 여러 테이블의 조인을 잘하기 위해선 조인을 테이블과 테이블 간의 연결이 아니라, 데이터와 데이터 집합 간의 연결이라고 생각을 바꾸는 것이 좋다. 
  • 테이블 자체를 데이터 집합으로 볼 수 있다.
  • WHERE절의 필터 조건을 거친 결과를 데이터 집합이라 할 수 있다.
  • 두 개의 데이터 집합이 조인된 결과는 새로운 하나의 데이터 집합이다. 
• A테이블과 B테이블을 조회하면, 물리적인 테이블을 합치는 느낌보다 두개의 테이블이 연결된 새로운 데이터 집합인 것
• 아무리 많은 테이블을 조인해도 한 번의 조인에는 두 개의 데이터 집합만 사용되고, INNER JOIN은 JOIN순서를 바꾼다고 결과가 달라지지 않는다. 

---

잘 못 작성한 조인 (M:1:M조인)

 

조인을 사용하다 중복된 데이터 때문에 강제로 DISTINCT를 사용하는 경우가 있다. 이는 잘못작성한 SQL이다.

 

정확한 조인을 구사하기 위해 다음을 고려해보자 

 

• 1:1 관계 조인 = 1건의 결과 발생 
• 1:M 관계 조인 = M건의 결과 발생 
• M*M관계 조인 = M*M건의 결과 발생 

다대다 관계에서 조인은 OLTP환경에서 거의 발생할 일이 없다 (사실 없어야한다.) 

 

보통 세 개의 테이블을 M:1:M의 관계일 때 조인에서 실수가 발생한다.

 

(EX 1은 중심 테이블이고 M인 테이블은 각각 1의 키 값을 참조하는 실적테이블)

 

SELECT T1.CUS_ID, T1.CUS_NM,COUNT(DISTINCT T2.ITM_ID ||'-'|| TO_CHAR(t2.evl_lst_no))EVAL_CNT
,COUNT(DISTINCT T3.ORD_SEQ)ORD_CNT
FROM M_CUS T1, T_ITM_EVL T2, T_ORD T3
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.CUS_ID = T3.CUS_ID
AND T1.CUS_ID = 'CUS_0023'
AND T2.EVL_DT >= TO_DATE('20170301','YYYYMMDD')
AND T2.EVL_DT <  TO_DATE('20170401','YYYYMMDD')
AND T3.ORD_DT >=  TO_DATE('20170301','YYYYMMDD')
AND T3.ORD_DT < TO_DATE('20170401','YYYYMMDD')
GROUP BY T1.CUS_ID,T1.CUS_NM;

 

• 문법상 문제는 없지만 COUNT(DISTINCT)를 사용해서 총 결과를 강제로 맞힌 SQL이므로 좋은 방식은 아니다. 
• DISTINCT사용하지 않고 SQL을 작동시키면 아이템 평가 횟수와 주문 횟수가 모두 6건인데, 이를 각각 2건, 3건으로 맞추기 위해 DISTINCT를 사용했다.

 

해결방법 

 

1 UNION ALL 사용 

 

SELECT T1.CUS_ID,MAX(T1.CUS_NM) CUS_NM, SUM(T1.EVL_CNT) EVL_CNT, SUM(T1.ORD_CNT) ORD_CNT
FROM(
    SELECT T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, COUNT(*) EVL_CNT, NULL ORD_CNT
    FROM M_CUS T1, T_ITM_EVL T2
    WHERE T1.CUS_ID = T2.CUS_ID
    AND T2.CUS_ID = 'CUS_0023'
    AND T2.EVL_DT >= TO_DATE('20170301','YYYYMMDD')
    AND T2.EVL_DT < TO_DATE('20170401','YYYYMMDD')
    GROUP BY T1.CUS_ID,T1.CUS_NM 
    UNION ALL 
SELECT T1.CUS_ID,MAX(T1.CUS_NM) CUS_NM,NULL EVL_CNT,COUNT(*) ORD_CNT
FROM M_CUS T1, T_ORD T3
WHERE T1.CUS_ID = T3.CUS_ID
AND T3.CUS_ID = 'CUS_0023'
AND T3.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T3.ORD_DT < TO_DATE('20170401','YYYYMMDD')
GROUP BY T1.CUS_ID, T1.CUS_NM
)T1
GROUP BY T1.CUS_ID;

• UNION ALL 컬럼을 맞춰주기 위해 NULL ORD_CNT 처럼 속성을 표현할 수 있다. 
• 각각 조인 후에 결과를 UNION ALL로 결합 

SELECT T1.CUS_ID,MAX(T1.CUS_NM) CUS_NM, SUM(T4.EVL_CNT) EVL_CNT, SUM(T4.ORD_CNT) ORD_CNT
FROM M_CUS T1,
    (
    SELECT T2.CUS_ID, COUNT(*) EVL_CNT, NULL ORD_CNT
    FROM  T_ITM_EVL T2
    WHERE T2.CUS_ID = 'CUS_0023'
    AND T2.EVL_DT >= TO_DATE('20170301','YYYYMMDD')
    AND T2.EVL_DT < TO_DATE('20170401','YYYYMMDD')
    GROUP BY T2.CUS_ID 
    UNION ALL 
SELECT T3.CUS_ID, NULL EVL_CNT,COUNT(*) ORD_CNT
FROM  T_ORD T3
WHERE T3.CUS_ID = 'CUS_0023'
AND T3.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T3.ORD_DT < TO_DATE('20170401','YYYYMMDD')
GROUP BY T3.CUS_ID
)T4
WHERE T1.CUS_ID = T4.CUS_ID
GROUP BY T1.CUS_ID;

• M_CUS를 맨 밖에 한번만 사용했다 (성능이 쫌 더 낫다)

 

2. 1:1:1로 조인 

 

• 나머지 M인 테이블들을 GROUP BY을 통해 1로 만든 후 1:1:1조인을 수행하는 것이다. 

SELECT T1.CUS_ID,T1.CUS_NM,T2.EVL_CNT,T3.ORD_CNT
FROM M_CUS T1,(
    SELECT T2.CUS_ID,COUNT(*) EVL_CNT
    FROM T_ITM_EVL T2
    WHERE T2.CUS_ID = 'CUS_0023'
    AND T2.EVL_DT >= TO_DATE('20170301','YYYYMMDD')
     AND T2.EVL_DT < TO_DATE('20170401','YYYYMMDD')
     GROUP BY T2.CUS_ID)T2
     ,(
    
    SELECT T3.CUS_ID,COUNT(*) ORD_CNT
    FROM T_ORD T3
    WHERE T3.CUS_ID = 'CUS_0023'
    AND T3.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
     AND T3.ORD_DT < TO_DATE('20170401','YYYYMMDD')
     GROUP BY T3.CUS_ID)T3
     
   WHERE T1.CUS_Id = T2.CUS_ID
   AND T1.CUS_ID = T3.CUS_ID
   AND T1.CUS_ID ='CUS_0023';

 

• 1로만드는 GROUP BY 기준은 1테이블과 M테이블을 조인할때 사용하는 컬럼을 기준으로 생각해보자

---

 

• 이너 조인 주의점
  • 모든 데이터가 각각 존재할 때 조인이 가능하다 
    • 무조건 값이 나오게 하려면 OUTTER 조인을 사용하자 
  • 주요 마스터 하나에 다른 테이블 (M:1:M)관계일때는 주의를 기울이자. 
    • 무조건 성공적인 조인 조건을 적을 것이 아니라, 데이터 건수가 어떻게 변하는지 상상해보고 조인을 작성하자

 

 

---

참고자료 

 

https://www.yes24.com/Product/Goods/82818767

SQL BOOSTER - 예스24