내부조인 / 외부조인

• 조인

조인이란 관계형 데이터베이스(RDBMS)의 주요 연산 중 하나로 다수개의 테이블에 분산된 자료를 테이블 간 설정된 관계를 이용하여 서로를 참조하는 연산이다.

즉, 필요한 데이터를 내부에서 혹은, 외부에서 뽑아 온다고 생각하면 된다.
또한, 테이블을 두 개 사용한다면 서브쿼리가 아닌 이상 조인조건이 필수적 이라고 생각하면 된다.

조인의 종류

• 내부조인 (innerJoin)
• 카타시안 조인 (CartasianJoin)
• 외부조인 (OuterJoin)

조인은 크게 내부, 카타시안, 외부조인으로 나뉜다.
그리고 세부적으로 형태에 따라 일반조인, ANSI조인으로 나뉜다.

• ANSI 조인 : 미국표준위원회에서 제정한 (89년 기준안) 조인문 형식

  ---

카타시안 조인
서술에 앞서 여기에서는 카타시안 조인을 설명하지 않을 것입니다.
그 이유를 먼저 살펴보자면 카타시안 조인은 모든 경우를 고려하기 때문입니다.
모든 경우를 고려한다는 말은 다르게 말하면 실제 테이블의 행보다 더욱 많은 행이 결과로 나온다는 것을 의미합니다.

만약 행 10개를 가진 A 테이블과 / 행 300개를 가진 B 테이블을 조인하게 되면 두 테이블의 관계에서 나올 수 있는 모든 경우의 수 10 * 300 = 3000 의 행이 결과로 나오게 됩니다.

내가 원하는 행을 보기 위해 조인을 하였지만 3000개의 데이터 속에서 내가 원하는 정보를 찾기란 정말 힘듭니다.
때문에 조인의 조건이 없거나, 반드시 필요한 경우가 아니면 사용되지 않는 조인입니다. 따라서 여기선 설명을 생략하도록 하겠습니다.

1. 내부 조인

가장 일반적인 조인형식으로 가장 쉽고 간편하게 사용할 수 있습니다.
두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인입니다.

조인을 위해선 꼭 두개 이상의 테이블이 필요합니다.
또한, 테이블을 복수개 사용하다 보니 별칭을 사용하는 것이 편합니다.

일반조인 사용예)

       SELECT A.EMPLOYEE_ID AS 사원번호,
               A.EMP_NAME AS 사원명,
               B.DEPARTMENT_NAME AS 부서명,
               A.HIRE_DATE AS 입사일
          FROM HR.EMP A, HR.DEPT B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND EXTRACT(YEAR FROMA.HIRE_DATE) >= 2008

조인조건과 일반조건의 기술순서는 Query 수행과 관계없다.

ANSI조인 사용예)

        SELECT A.EMPLOYEE_ID AS 사원번호,
               A.EMP_NAME AS 사원명,
               B.DEPARTMENT_NAME AS 부서명,
               A.HIRE_DATE AS 입사일
          FROM HR.EMP A
    INNER JOIN HR.DEPT B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID
               AND EXTRACT(YEAR FROM A.HIRE_DATE) >= 2008);

일반조인과 다르게 INNER JOIN임을 기술하고 ON 뒤에 조인조건
AND 뒤에 일반조건을 적는 것을 볼 수 있다.

---

---

2. 외부 조인

외부조인은 자료의 종류가 부족한 테이블에 NULL 행을 추가 삽입한 후 조인 연산을 실행한다.
즉, 상호 테이블간에 일치하는 값으로 연결되는 내부조인과 다르게 어느 한 테이블에 공통 컬럼값이 없더라도 해당 ROW들이 조회결과에 포함되고 조인 조건에서 부족한 ROW를 가진 컬럼명 뒤에(+)를 사용합니다.

외부조인 주의사항

• 테이블 자신에게 (+)를 붙일 수 없습니다. 이런 경우에는 셀프 조인을 사용합니다.
• 외부조인은 서브쿼리와 동시에 사용될 수 없습니다.
• (+)는 한 테이블만 지정할 수 있습니다.
• 부족한 행은 NULL로 대체되어 나오기 때문에 COUNT(*)시 실제 행보다 많이 나올 수 있습니다.

일반조인 사용예)

        SELECT A.LPROD_GU AS 분류코드,
               A.LPROD_NM AS 분류명,
               COUNT(PROD_ID) AS "상품의 수"
          FROM LPROD A,PROD B
         WHERE A.LPROD_GU = B.PROD_LGU(+)

ANSI조인 사용예)

        SELECT A.LPROD_GU AS 분류코드,
               A.LPROD_NM AS 분류명,
               COUNT(PROD_ID) AS "상품의 수"    
          FROM LPROD A
          LEFT OUTER JOIN PROD B ON(A.LPROD_GU = B.PROD_LGU)
      GROUP BY A.LPROD_GU, A.LPROD_NM;

ANSI 조인에서는 (+) 를 기재하지 않고 적은 테이블의 순서를 기준으로 방향을 기재한다.

• FULL -> 양 쪽 모두 데이터가 부족할 때
  :    FULL OUTER JOIN
• LEFT -> FROM 절에 기술된 테이블이 JOIN절에 기술된 테이블보다 자료가 더 많은 경우
  :    LEFT OUTER JOIN
• RIGHT -> FROM 절에 기술된 테이블보다 JOIN절에 기술된 테이블의 자료가 더 많은 경우
  :    RIGHT OUTER JOIN

---

번외 : 3.SELF 조인

하나의 테이블에 2개 이상의 별칭을 부여하여 자신의 테이블 사이에서 발생하는 조인

• 마치 서로가 다른 테이블인것처럼 조인한다.
  즉, 자기와 자기를 서로 조인

주로 자기 자신 테이블이 특수한 상황에 놓였을 때와 비교하기 위해서 주로 사용한다.

SELF조인 사용예)

        SELECT C.EMPLOYEE_ID AS 사원번호,
               C.EMP_NAME AS 사원명,
               C.DEPARTMENT_ID AS 부서번호,
               B.DEPARTMENT_NAME AS 부서명,
               C.SALARY AS 급여
          FROM HR.EMP A, HR.DEPT B, HR.EMP C
         WHERE A.EMPLOYEE_ID = 120--일반조건
           AND A.SALARY <= C.SALARY--조인 조건
           AND C.DEPARTMENT_ID = B.DEPARTMENT_ID--조인조건
      ORDER BY 3, 5 DESC;

별칭 A 와 C 는 같은 테이블이다.

즉, A 테이블로 120번 사원을 조인하고 -> B부서와 같은 사람들과 전체 사원 중 같거나 더 많은 급여를 받는 사원을 찾는 Query이다.
<hr>