[SQL] 조인의 모든 것 - Join, Cartesian Product, EQUI, Non-Equi, Outer, Sefl

 

여러 테이블의 데이터를 조회하기 - JOIN

 

SQL 에서 Join 은 언제 사용할까요?

group by 절을 설명할 때에도 한 번 언급하긴 했었는데,

여러 테이블로 나뉘어진 데이터를 조회하기 위해 사용한다고 생각하시면 될 것 같아요!

 

 예를 들자면, 은행에는 대량의 고객 데이터가 있을 것입니다.

개인정보, 예금 정보, 적금 정보, 카드 실적 등등.. 다 분리가 되어있겠죠?
이런 데이터들을 하나의 테이블에 다 넣게되면, 데이터 조회 시

불필요한 데이터까지 모두 스캔되어버려 메모리 낭비를 초래하기 때문이죠.

고객의 카드 실적 정보만 보고싶은데, 적금 정보, 예금 정보, 개인정보 등등.. 까지 조회가 되버리죠.

 
 그래서! 테이블들을 적절하게, 그리고 잘게 쪼개는 것도 중요합니다. 목적은 속도를 빠르게 하기 위함이죠

 

 데이터를 합치는 것은 SQL이 R, Python보다 훨씬 빠르고 효율적이기 때문에 잘 숙지해두는게 좋겠죠?

R, Python은 조인에 한계가 있다고 하니깐 말이죠..

JOIN 문을 작성하는 방법

1. 어떤 테이블과 합칠지 정하기

2. 그 테이블과 어떤 관계가 있는지 확인하기

3. 조인 대상 테이블들을 FROM 절에 작성하기

4. 관계가 있는 컬럼을 WHERE 절에 작성하기


 학생 테이블에서 '학생 이름', 교수님 테이블에서 '지도교수님 이름' 출력

=> 1. 학생 테이블에서 교수님의 정보를 조회해야 하므로, 교수님 테이블과 합쳐야 합니다.

     2. PROFESSOR 테이블과 어떤 관계가 있는지 확인해보면, PROFNO 컬럼이 관계가 있는 것을 알 수 있습니다.

        STUDENT 테이블에도 PROFNO 컬럼이, PROFESSOR 테이블에도 PROFNO 컬럼이 존재하기 때문이죠.

     3. 그렇다면, STUDENT 테이블은 기준이 되는 테이블이고, 조인을 할 PROFESSOR 테이블을 FROM 절에 함께 작성해줍니다.

     4. 관계가 있는 컬럼은 WHERE 절에 작성하여 저 컬럼이 관계가 있다고 명시해줍니다.

         n 개의 테이블을 조인하려면 최소 n-1개의 조인 조건이 필요합니다.

         이 예제는 2 개의 테이블을 조인했으므로 1개의 조인 조건을 사용한 것이죠!

  * 컬럼 구별을 위해 테이블명.컬럼명 으로 작성해주는데, 여기서 테이블에도 Alias 를 사용해주면 좀 더 간결하게 작성할 수 있습니다.

    단, 테이블의 Alias 는 FROM 절에서 정의하고 SELECT와 달리 AS 는 써주지 않습니다.
   

 

 

 

1) Cartesian Product 
  

Cartesian Product (카티시안 곱)은 발생가능한 모든 경우의 수의 행이 출력되는 것을 의미합니다. 

N 개의 행을 가진 테이블과 M 개의 행을 가진 테이블의 카티시안 곱은 N*M 이 되는거죠!

 이 현상은 조인 조건을 생략한 경우이거나

               조인 조건이 부적합할 경우 발생합니다.
 
 
 조인 조건을 생략한 경우

 =>두 개의 테이블을 조인했지만, 조인 조건을 생략한 경우 Cross Join 이 발생하여 불필요한 N*M 만큼의 데이터가 조회됩니다.

     데이터가 방대할 경우 CPU 과부화에 딱 좋은 상황이죠..

     그러므로 JOIN 을 사용할 경우 조인 조건을 꼭! 작성해야 한다는 것!!

 

 

 

 

2) Equi Join 

 
  기존 테이블과 조인 테이블에서 공통 칼럼을 '='(equal) 비교하여,

같은 값을 가지는 행을 연결하고 결과를 생성하는 조인 방법입니다.

가장 많이 사용되는 조인 방법이라고도 합니다.

 학생 테이블과 부서 테이블을 EQUI JOIN하여 이름, 학과 번호, 소속 학과 이름을 출력

 

 

 

 

 학생 테이블과 학과 테이블, 교수 테이블을 JOIN 하여 학생의 이름과 학과이름, 지도교수 이름을 출력
 => 세 개의 테이블을 조인하는 경우, 기준이 되는 테이블이 중요합니다. 

      여기서 기준이 되는 테이블은 학생 테이블이겠죠? 학생에 대한 모든 정보가 주된 목적이니까요!

      세 개의 테이블을 어떻게 묶느냐에 따라 결과가 달라지기도 하니 기준을 잘 정해야합니다.
      그 후 각각 테이블의 연결고리를 잘 확인합니다.

      STUDENT 테이블과 DEPARTMENT 테이블은 DEPTNO 컬럼을 통해 연결,

      STUDENT 테이블과 PROFESSOR 테이블은 PROFNO 컬럼을 통해 연결되었죠?

      JOIN 된 테이블은 3 개니까 조인 조건은 2 개가 된 것이고요!

 

 
STUDENT, DEPARTMENT, EXAM_01 테이블을 사용하여 학생 이름, 제1전공명, 시험 점수 출력

=> 여기서도 STUDENT 테이블이 기준이 됩니다.

     STUDENT 테이블과 DEPARTMENT 테이블은 DEPTNO 컬럼을 통해 연결,

     STUDENT 테이블과 EXAM_01 테이블은 STUDNO 컬럼을 통해 연결됩니다.

     분리되어있는 테이블에서 연결점을 찾는게 중요합니다!

 

 

 

3) Non-Equi Join 

 

Equi Join 은 공통 칼럼을 '='(equal) 비교한다면,

Non-Equi Join 은 '=' 조건을 사용하지 않고 '대소비교' 를 하는 경우 사용하는 조인입니다.

 

바로 적용해보죠!

 

GOGAK 테이블과 GIFT 테이블을 사용하여 각 고객의 포인트별로 받아갈 수 있는 가장 좋은 상품명 출력

=> between A and B 는 'A' 이상 'B' 이하니까 대소비교겠죠?

   고객의 POINT 점수로 받을 수 있는 가장 좋은 상품을 출력해주게됩니다.

                 <고객 테이블>                                            <상품 테이블>

  

<결과>

 

 

비슷지만, 이번에는 받아갈 수 있는 모든 상품을 출력해볼까요?

GOGAK 테이블과 GIFT 테이블을 사용하여 각 고객의 포인트별로 받아갈 수 있는 모든 상품명 출력

=> order by 절에서 컬럼명 말고 컬럼의 순서를 사용해도 가능합니다.

    여기서 1은 고객명이니 고객명 기준으로 정렬이겠죠?

               <고객 테이블>                                            <상품 테이블>

  

<결과>

 

 

 

이번에도 약간 비슷하지만, 상품을 제공하는 주최측 기준으로 보면,

GOGAK 테이블과 GIFT 테이블을 사용하여 각 상품별 최대 기대 수량 출력

=> 문제 그대로 바로 하려하지 말고, 한 단계씩, 중간 결과값을 출력해보면서 차근차근 해보면 쉬울꺼에요!

     group by 를 바로 사용하려고하지 말고, 먼저 출력되는 값들을 확인한 후, 그룹으로 묶어보세요!

     group by 로 고객의 이름을 그룹지은 이유는, 상품을 받을 수 있는 고객 수를 count 하기 위함입니다.

             <고객 테이블>                                            <상품 테이블>

  

<결과>

 

다른 테이블을 사용해보면,

STUDENT, EXAM_01, HAKJUM 테이블을 사용하여 학생의 학점을 구하기

                                                                            <학생 테이블> 

                                <학생 점수 테이블>                                       <학점 테이블>

                                    

<결과>

 

 

 

 

 

4) OUTER Join 

 

OUTER JOIN 은 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL 일 경우

'='(equal) 결과가 거짓이 되어 NULL 값이 생략되는 정보를 표시하기위해 사용합니다.

 

만일 학생 데이터를 조회할 때, 특정 학생에게 지도교수가 아직 없다면, join 에서 매칭이 잘 되지 않습니다.

join에서 주의할 점은 조인 조건이 Null 이나 기타 조건을 만족하지 않을 때 생략되는 데이터가 있을 수 있기 때문이죠.

이러한 생략된 정보까지 포함된 전체 출력을 하기 위해 사용하는 것이 바로!! OUTER JOIN 이라는 것이죠. 

아우터 조인.. 이 친구 상당히 중요합니다. Null 값을 가진 데이터를 없는 데이터처럼 만들어버리니까요..

사용 방법은 간단해요!

오라클 기준으로 WHERE 컬럼 뒤 한쪽에 (+) 기호 하나만 붙여주면 됩니다.
(+) 기호의 방향은 Null 이 포함된 컬럼이 있는 테이블의 반대 컬럼, 즉, 기준이 되는 테이블의 반대 테이블을 말하죠.

STUDENT, PROFESSOR 테이블을 사용하여 학생의 지도교수 조회하기

=> OUTER JOIN 을 사용하지 않았다면 지도교수가 없는 학생(허우, 김주현 ...)은 출력이 안 되겠지만,

     OUTER JOIN 을 기준이 되는 테이블(STUDENT) 반대 테이블에 적용해주면서, 지도교수가 없는 학생(Null)들도

     포함하여 출력이 되는 거죠!
     기준이 왼쪽이면 LEFT OUTER JOIN, 오른쪽이면 RIGHT OUTER JOIN 이라고 부릅니다.

     참고로, Oracle은 조인하고자하는 FROM 절의 테이블 순서에 영향을 받지 않지만, ANSI 표준은 순서에 영향을 받는다는 것.

 

 

STUDENT, PROFESSOR 테이블을 사용하여 교수의 지도학생 조회하기

=> 이번에는 교수 기준, 지도학생을 조회하는 예시인데요, 이번에는 교수가 기준이므로 PROFESSOR 테이블을 먼저 사용하였습니다.
     마찬가지로 OUTER JOIN 은 기준 테이블의 반대인 STUDENT 테이블에 적용하였고요.

     같은 조인 결과더라도 테이블의 순서에 따라서 다른 해석이 나올 수 있다는 것!

     * 참고로, 데이터의 생략은 '='(equal) 이 아닌 대소비교에서도 발생할 수 있습니다.

 

 

STUDENT, PROFESSOR 테이블을 사용하여 교수 별 지도학생의 수 조회하기

=> 이번에는 JOIN 과 GROUP BY 를 같이 사용하였는데, 교수 별 이라는 그룹 형식의 결과를 출력해야하므로

     GROUP BY 를 사용한 것입니다.

     [SQL] 그룹, 분리 조건 - group by, having <- GROUP BY는 이 글을 참고해주세요!

 

 

5) SELF Join 

Self Join 은 말 그대로 해석하자면, 자기 자신을 조인하겠다. 라는 뜻입니다.

Join은 보통 1:*(일대다) 형식으로, 한 행씩 수행됩니다.

 

그렇다면 같은 테이블의 다른 행 데이터를 사용하려면 어떻게 해야할까요?

이럴 때, 자신의 테이블 내의 행과 비교하려면 Self join 이 필요하답니다!

동일한 테이블을 마치 다른 테이블인 것 처럼 Join 하여 스캔하는 방식이죠.

Self Join 이 필요할 때는,

1. Join 하고자하는 대상이 동일 테이블에 존재하는 경우. 한 번의 테이블로 원하는 값을 가져올 수 없을 때,
2. 원하는 정보가 한 테이블에 있는데, 동일한 테이블내의 정보를 단 한번의 스캔만으로 가져올 수 없을 때,
이럴 때, 동일 테이블을 두 번 스캔하는 목적으로 사용됩니다.

현업에서도 같은 테이블로 조인을 해야할 상황이 많다고 하니 그만큼 중요하겠죠?!

동일 테이블 내에서 스캔이 두 번 발생한다는 것을 잘 이해하고 인지해야합니다.

 

 

'테이블 1'은 각 행에 대한 개별 정보를 찾기 위한 테이블이고,

'테이블 2' 가져올 추가적인 정보를 스캔할 테이블입니다.
먼저, 두 테이블을 어떤 용도로 사용할지 정하는것이 중요합니다.

 

 

EMP 테이블을 Self Join 하여 직원의 연봉과, 해당 직원의 상위 관리자 연봉 조회하기

 

EMP 테이블을 Self Join 하여 상위 관리자의 연봉보다 연봉이 높은 하위 직원 조회하기
=> 내가 원하는 정보가 무엇인지 찾고(상위 관리자의 연봉보다 연봉이 높은 하위 직원),

     그것을 찾기 위해 어떻게 해야하는지(EMP 테이블을 Self Join)를 먼저 생각해야합니다.

     -  e 1 테이블은 상위 관리자 기준의 테이블, e 2 는 하위 직원을 스캔하기위한 테이블입니다.

     - 그래서 WHERE 절에서 테이블 간 관계를 찾아주고, e1.SAL > e2.SAL 조건을 준 것이죠.
     * 셀프 조인에서는 아웃풋 조인이 자주 사용되는데, 이는 셀프 조인 사용 시 90% 이상 생략되는 정보 생기기 때문입니다.

 

PROFESSOR 테이블을 사용하여 각 교수의 각 이름, 입사일, 해당 교수보다 먼저 입사한 교수의 이름과 입사일 출력

 => 입사 날짜가 빠른 선배 이름 출력이 목적인데요, 날짜가 빠르다는것은 날짜가 작은 것을 의미합니다.

      ( 1981/10/23 < 1998/03/22 ) 81년도보다 98년도가 더 느리다(크다).
      테이블 1은 교수 정보, 테이블 2는 해당 교수의 선배 정보를 스캔하기 위해 사용합니다.

      먼저 입사한 교수를 찾아야 하니, "교수의 입사일 > 선배 교수의 입사일" 이 성립하겠죠?

 

 

 

 위의 예제를 활용해서,
 PROFESSOR 테이블을 사용하여 각 교수별로 먼저 입사한 교수(선배교수)의 수 출력
 => 단, 선배가 없는 경우도 항상 출력이라는 조건이 들어가는데요, 선배가 없다면 Null 이겠죠?

      그렇다면 Null 까지 포함해서 출력하기 위해 Outer Query 를 사용해야겠죠?

 
 

참고: KIC 캠퍼스 머신러닝기반의 빅데이터분석 양성과정