04. JOIN (조인문)

 

ㅁ 조인이 관계형 데이터베이스의 꽃이다.

 

ㅁ 조인은 여러 테이블에서 데이터를 조회하기 위해 사용한다. 

- 두 개 이상의 테이블에서 데이터를 가져와 하나의 결과 집합으로 결합합니다.

- EMPLOYEE 테이블에서 직급 코드만 가져올 것이 아니라, 조인을 해서 JOB 테이블에 있는 직급명도 가져올 수 있다.

 

 

ㅁ < JOIN >

- 두 개 이상의 테이블에서 데이터를 조회하고자할 때 사용된다.

- 조회 결과는 하나의 결과물로 조회된다.

- 매칭되는 컬럼(동일한 데이터를 가지고 있는 컬럼) 을 가지고 조건을 작성하여 조인한다.

 

ㅁ 조인 문법은 크게 2가지로 나뉜다.

- ANSI만 알아도 되긴 함. ANSI 방식이 오라클에서도 돼서. 근데 회사에서 오라클 방식 쓸 수도 있으니.

 

(1) 오라클 전용 구문 방식

(2) ANSI(미국국립표준협회) 방식

- 오라클 DBMS가 아니더라도 다른 DMBS에서도 사용 가능

- 회사에서 오라클이 아닌 DBMS를 쓸 수 도 있으니 두 방법 다 학습한다.

 

 

※ 종류

 

                              오라클 전용                                                         ANSI

==========================================================================

                                등가 조인                                            내부 조인 (INNER JOIN)

                            (EQUAL JOIN)                                      자연 조인 (NATURAL JOIN)

----------------------------------------------------------------------------------------------------------------------------------

                                포괄 조인                                        왼쪽 외부 조인 (LEFT OUTER JOIN)

                           (LEFT OUTER)                                 오른쪽 외부 조인 (RIGHT OUTER JOIN)

                          (RIGHT OUTER)                                   전체 외부 조인 (FULL OUTER JOIN)

----------------------------------------------------------------------------------------------------------------------------------

                      자체 조인 (SELF JOIN)                                     JOIN ON (딱히 용어 없음)

               비등가 조인(NON EQUAL JOIN)

----------------------------------------------------------------------------------------------------------------------------------

             카테시안 곱(CARTESION PRODUCT)                       교차 조인(CROSS JOIN)

 

 

 

- 실행 결과는 동일한데 용어와 작성문법이 다르다.

 

- 세 개의 외부 조인을 통틀어서 외부 조인이라고 한다.

- 등가조인, 포괄조인보다는 내부조인, 외부조인이라고 많이들 얘기함.

 

 

 

 

ㅁ 등가 조인 (EQUAL JOIN) / 내부 조인 (INNTER JOIN)

- 테이블간에 컬럼을 가지고 동등비교 수행해서 조회

- 조건: A 테이블의 컬럼 = B 테이블의 컬럼

 

- 매칭시키는 컬럼의 값이 "일치하는 행들만" 조인되서 조회

(일치하는 값이 없는 행은 조회에서 제외된다.)

- 그런데 매칭시키는 컬럼의 값이 중복될 수도 있고 중복되지 않을 수도 있음.

 

 

(1) 오라클 전용 구문

- FROM 절에 조회하고자 하는 테이블들을 나열 (컴마로)

- WHERE절에 매칭시킬 컬럼에 대한 조건 작성

 

i) 매칭시킬 컬럼명(연결할 두 컬럼명)이 중복되지 않음

- 전 사원들의 사번, 사원명, 부서코드, 부서명 조회

 SELECT EMP_ID, EMP_NAME, DEPT_CODE
 FROM EMPLOYEE;
 
 SELECT DEPT_ID, DEPT_TITLE
 FROM DEPARTMENT;

ㅇ(오라클만 가능?)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT;

 

오류가 나진 않았는데 원하는 데이터가 나오진 않음.

23개의 데이터인데 결과는 207개의 데이터가 나옴.

JOIN 조건을 명시하지 않으면 EMPLOYEE의 행과 DEPARTMENT의 행이 곱해져서 나옴.

 

ㅇ

SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;

 

EMPLOYEE 테이블의 전체 사원 23명 중 DEPT_CODE가 NULL인 2명을 제외하고 21개 데이터가 나옴.

DEPARTMENTE 테이블의 DEPT_ID가 D3, D4, D7은 제외하고 전체 부서 9개 중 3개를 뺀 6개의 부서만 나옴.

 

 

ii) 매칭시킬 컬럼명(연결할 두 컬럼명)이 중복됨

- 사번, 사원명, 직급코드, 직급명을 조회

 

 SELECT EMP_ID, EMP_NAME, JOB_CODE
 FROM EMPLOYEE;
 

 SELECT JOB_CODE, JOB_NAME
 FROM JOB;

 

ㅇ

SELECT EMP_ID, EMP_NAME,JOB_CODE,JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB;

 

- 오류. column ambiguously defined. 애매하다. 중복되어서.

 

ㅇ
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB

WHERE JOB_CODE = JOB_CODE;

 

- 오류. column ambiguously defined

 

ㅇ 해결방법1. 테이블명 이용

SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;

 

- 오류. WHERE에 어떤 테이블건지를 표시하는것만으로는 부족함. 

 

 

SELECT EMP_ID, EMP_NAME,  EMPLOYEE.JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;

 

- 어떤 테이블건지도 표시를 해줘야 오류가 안 남.

 

 

ㅇ 해결방법2. 테이블에 별칭을 부여해서 이용하기

 

SELECT EMP_ID, EMP_NAME,  E.JOB_CODE, JOB_NAME
FROM EMPLOYEE "E", JOB J
WHERE E.JOB_CODE = J.JOB_CODE;

 

- 바로 위랑 결과는 동일함.

 

 

 

 

[바로 위까지는 오라클 전용. 다른 DBMS에서는 안될수도 있다.]

(2) ANSI

- FROM절에 기준이되는 테이블 하나만 기술

- JOIN절에 같이 조회하고자 하는 추가적인 테이블을 기술하고 매칭시킬 컬럼에 대한 조건 작성

- JOIN ON, JOIN USING 방식이 있다. 

 

 

i) 매칭시킬 컬럼명(연결할 두 컬럼명)이 중복되지 않음

- JOIN ON 방식만 이용 가능

- 전 사원들의 사번, 사원명, 부서코드, 부서명 조회

 

SELECT EMP_ID, EMP_NAME, DEPT_ID, DEPT_TITLE

FROM EMPLOYEE

[INNNER] JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

 

- 오라클 방식과 결과 동일.

- 원래는 INNTER 조인인데 생략 가능. 

 

 

 

ii) 매칭시킬 컬럼명(연결할 두 컬럼명)이 중복됨

- 사번, 사원명, 직급코드, 직급명을 조회

- JOIN ON, JOIN USING 방식 이용 가능

 

 

SELECT EMP_ID, EMP_NAME,  JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB ON (JOB_CODE = JOB_CODE);

 

- 오류. column ambiguously defined

JOB_CODE가 누구건지 모름.

 

 

ㅇ 해결방법1. 테이블명 이용

 

SELECT EMP_ID, EMP_NAME,  JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB ON (EMPLOYEE.JOB_CODE = JOB.JOB_CODE);

 

- 오류. column ambiguously defined

 

 

SELECT EMP_ID, EMP_NAME,  JOB.JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB ON (EMPLOYEE.JOB_CODE = JOB.JOB_CODE);

 

- 잘 됨.

 

 

ㅇ 해결방법2. 테이블의 별칭 이용

 

SELECT EMP_ID, EMP_NAME,  J.JOB_CODE, JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);

 

- 잘 됨.

 

 

ㅇ 해결방법3. JOIN USING 구문 이용 (컬럼명이 일치할 때만 사용 가능)

 

SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME

FROM EMPLOYEE

JOIN JOB USING (JOB_CODE)

 

 

[위의 것들은 다 INNTER JOIN이었다.]

※ 참고

- 자연 조인(NATURAL JOIN) : 조인할 두 테이블간에 동일한 이름의 컬럼이 단 한 개만 존재할 경우

- 사실 자연 조인도 JOIN USING도 잘 안쓰임. JOIN ON만 많이 쓰임.  

(2 테이블이 아닌 여러 테이블을 조인시킬 경우, 어떤 테이블의 컬럼을 조건으로 쓰는지 명시하는게 좋음)

 

 

SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME

FROM EMPLOYEE

NATURAL JOIN JOB;

 

 

ㅁ 추가 조건 기술도 가능하다.

ㅁ 예제

- 직급이 대리인 사원의 사번, 이름, 급여 조회 ('직급코드가 J3인'이 아님!)

 

(1) 오라클 방식

 

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE;

 

- '사원의 사번, 이름, 급여'만 조회할거면 위 문장만으로도 됨.

그런데 직급명이 '대리'이려면 JOB 테이블과 조인이 필요함.

 

 

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE
      AND EMPLOYEE.JOB_CODE = 'J6'         //        여기에 앞에 테이블 표시 안하면 오류
;

 

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE
      AND JOB_NAME = '대리'                     //          이건 안겹쳐서 테이블 표시 없어도 됨.
;

 

 

(2) ANSI 방식

 

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리';

 

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
JOIN JOB ON (EMPLOYEE.JOB_CODE = JOB.JOB_CODE)
WHERE JOB_NAME = '대리';

 

 

 

 

 

ㅁ 실습문제1                       //                     매칭시킬 컬럼명이 중복되지 않음.

- 부서가 인사관리부인 사원들의 사번, 이름, 보너스 조회

 

(1) 오라클

 

SELECT EMP_ID, EMP_NAME, BONUS
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
      AND DEPT_TITLE = '인사관리부'
;

 

- 3개

 

(2) ANSI

 

SELECT EMP_ID, EMP_NAME, BONUS
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        WHERE DEPT_TITLE = '인사관리부'
;

 

- 컬럼명이 일치하지 않으므로 JOIN USING 못 쓰고, 테이블명 표시 필요 없다.

- 3개

- 여기서 WHERE 대신 AND도 결과는 같음.

 

 

ㅁ 실습문제2                        //                     매칭시킬 컬럼명이 중복되지 않음.

- 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명 조회

 

(1) 오라클

 

SELECT EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
               AND BONUS IS NOT NULL
;

 

- 8개

 

(2) ANSI

 

SELECT EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
          WHERE BONUS IS NOT NULL
;

 

- 8개

 

 

ㅁ 실습문제3                        //                     매칭시킬 컬럼명이 중복되지 않음.

- 부서가 총무부가 아닌 사원들의 사원명, 급여 조회

 

(1) 오라클

 

SELECT EMP_NAME, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
               AND DEPT_TITLE <> '총무부'
;

 

- 18개

 

 

(2) ANSI

 

SELECT EMP_NAME, SALARY
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
         WHERE DEPT_TITLE ^= '총무부'
;

 

- 18개

 

 

ㅁ 실습문제4                        //                     매칭시킬 컬럼명이 중복되지 않음.

- DEPARTMENT와 LOCATION을 참고해서 전체 부서들의 부서코드, 부서명, 지역코드, 지역명 조회

 

(1) 오라클

 

SELECT DEPT_ID, DEPT_TITLE, LOCAL_CODE(LCATION_ID도 상관 無), LOCAL_NAME
FROM DEPARTMENT, LOCATION
WHERE LOCATION_ID = LOCAL_CODE
;

 

- 9개

 

(2) ANSI

SELECT DEPT_ID, DEPT_TITLE, LOCAL_CODE, LOCAL_NAME
FROM DEPARTMENT
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
;

 

- 9개

 

(2-1) ANSI + ASIAN1 지역에 있는 부서만

 

SELECT DEPT_ID, DEPT_TITLE, LOCAL_CODE, LOCAL_NAME
FROM DEPARTMENT
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
AND LOCAL_NAME ='ASIA1'
;

 

 

 

ㅁ ANSI 구문에서 'AND' 대신 'WHERE'도 가능하다.

- 그런데 여기까지가 조인에 대한 조건이고 추가적인 조건을 분리해서 보기 편하게 하기 위해서 WHERE로 쓰는거.

- 지금까지는 같은데, 나중에 좀더 복잡한 구조의 디비같은경우 조인할 때 반드시 on에 추가적인 조건을 써야만할때가 있어서, 구분하기 쉽게 WHERE 쓰는게 나을 것 같다. 

 

 

 

 

 

 

 

ㅁ < 포괄 조인 / 외부 조인 (OUTER JOIN) >

- 두 테이블간에 JOIN시 일치하지 않는 행도 포함시켜서 조회 가능

 

 

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

- 내부 조인은 23개의 사원 중 DEPT_CODE가 NULL인 2명을 제외하고 21명의 사원 데이터만 출력됨.

 

- 부서 배치가 아직 안 된 사원 2명도 조회 (LEFR OUTER JOIN)                 21 + 2 = 23개 데이터 반환.

- 부서에 배정된 사원이 없는 부서 3개도 조회 (RIGHT OUTER JOIN)         21 + 3 = 24개 데이터 반환.

- FULL OUTER JOIN은 교집합 중복은 제거.                                                21 + 2 + 3 = 26개 데이터 반환.

 

(1) 오라클 방식  - LEFT OUTER JOIN

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE, DEPARTMENT

WHERE DEPT_CODE = DEPT_ID(+);

 

- 오라클 방식은 LEFT, RIGHT 개념이 없음.

- 기준으로 삼고자하는 테이블 반대편 컬럼에 (+)을 붙임.

- 기준인 DEPT_CODE는 EMPLOYEE 테이블의 컬럼. EMPLOYEE의 모든 데이터가 조회됨.

- 테이블 순서가 중요한 건 아니고, 

 

(2) 오라클 방식 - RIGHT OUTER JOIN

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE, DEPARTMENT

WHERE DEPT_CODE(+) = DEPT_ID;

 

- 기준인 DEPT_ID는 DEPARTMENT 테이블의 컬럼. DEPARTMENT의 모든 데이터가 조회됨.

 

 

(3) ANSI 방식 - LEFT OUTER JOIN

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE
LEFT [OUTER] JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

 

 

(4) ANSI 방식 - RIGHT OUTER JOIN

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE
RIGHT [OUTER] JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
;

 

(5) ANSI 방식 - FULL OUTER JOIN

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY*12
FROM EMPLOYEE
FULL  [OUTER] JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
;

 

(6) 오라클은 FULL OUTER JOIN이 없다.

 

 

 

 

 

 

ㅁ < 카테시안 곱 CARTESIAN PRODUCT / 교차조인 CROSS JOIN >

- 쓸 일이 없다. 과부하 위험. 실제 회사에서 다루는 데이터는 1만건, 10만건.

- 실수로나 나옴.

- 모든 테이블의 각 행들이 서로 매핑된 테이블이 조회된다. (곱집합)

 

(1) 오라클 방식

SELECT EMP_NAME, DEPT_TITLE

FROM EMPLOYEE, DEPARTMENT;

 

(2) ANSI 방식

SELECT EMP_NAME, DEPT_TITLE

FROM EMPLOYEE

CROSS JOIN DEPARTMENT;

 

 

 

 

ㅁ < 비등가 조인 NON EQUAL JOIN >

- 매칭시킬 컬럼에 대한 조건 작성시 '=' 사용하지 않는 조인문

 

 

ㅁ 예제1

- EMPLOYEE 테이블에 SALARY 컬럼 있고,

SAL_GRADE 테이블에 MIN_SAL, MAX_SAL 컬럼이 있음.

(1) 오라클 방식

 

SELECT EMP_NAME, SALARY, SAL_LEVEL

FROM  EMPLOYEE, SAL_GRADE

//   WHERE  SALARY >= MIN_SAL  AND  SALARY <= MAX_SAL;

WHERE SALARY    BETWEEN   MIN_SAL   AND   MAX_SAL;

 

- 조인에 대한 조건이 '일치하는(=)'을 쓰지 않음.

 

 

(2) ANSI 방식

 

SELECT EMP_NAME, SALARY, SAL_LEVEL

FROM  EMPLOYEE

JOIN SAL_GRADE ON SALARY BETWEEN MIN_SAL AND MAX_SAL;

 

- ANSI 방식으로는 JOIN ON만 가능.

 

 

ㅁ 예제 (급여등급이 S5인 사원의 사원명, 급여 조회)

 

SELECT EMP_NAME, SALARY

FROM EMPLOYEE

JOIN SAL_GRADE ON SALARY BETWEEN MIN_SAL AND MAX_SAL

WHERE SAL_LEVEL = 'S5';

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ㅁ < 자체 조인 SELF JOIN >

- 같은 테이블을 다시 한번 조인하는 경우

 

 

ㅁ 예제 (전체 사원들의 사번, 사원명, 사수사번, 사수명 조회)

 

(1) 오라클 방식

 

 

- MANAGER_ID도 EMP_ID의 하나다.

 

 

 

ㅇ 변형 (MANAGER_ID가 없는 사원들도 조회하기)

 

- LEFT, RIGHT 조인도 가능.

- MANAGER_ID가 없는 사원들도 나옴.

 

 

 

ㅇ 변형 (사원과, 사수의 부서명도 조회)

 

 

 

 

(2) ANSI 방식

 

 

 

 

 

 

- 위의 첫번째 예시를 오라클로 하면,

 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, LOCAL_NAME

FROM EMPLOYEE, DEPARTMENT, LOCATION

WHERE (DEPT_CODE = DEPT_ID)

AND (LOCATION_ID = LOCAL_CODE)

;

 

 

- ANSI는 순차적으로 조인이 됨. (오라클은 상관 無)

LOCATION과 DEPARTMENT 순서 바꾸면 에러남.

EMPLOYEE와 LOCATION 조인이 먼저 일어나는데, 그 조인 조건인 LOCATION_ID는 DEPARTMENT 테이블의 것이라서.

 

 

ㅁ 예제 (사번, 사원명, 부서명, 지역명, 국가명, 급여등급, 직급명 조회)

 

(1) 오라클 

 

 

- 빨간 박스 조건 없으면 <  카테시안 곱 CARTESIAN PRODUCT / 교차조인 CROSS JOIN > 됨. 105개.

- FROM으로 테이블을 다 가지고 있긴 하지만, 데이터를 다 연결시키기 위해서는,

LOCATION의 NATIONAL_CODE와 NATIONAL의 NATIONAL_CODE만 조인하면 될 게 아니고,

EMPLOYEE와 DEPARTMENT를 연결시키는 거랑도 조인을 해서 연결시켜야 함.

- 23명의 사원 중 21명만 조회된다. DEPT_CODE가 NULL인 2명 제외.

 

WHERE EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID(+) 한다고 23개가 나오지는 않음.

이 문장에서는 23개지만, 그 다음 LOCATION_ID도 NULL일 거라서 거기서 또 제외되서.

 

WHERE EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID(+)
AND DEPARTMENT.LOCATION_ID = LOCATION.LOCAL_CODE(+)
AND LOCATION.NATIONAL_CODE = NATIONAL.NATIONAL_CODE(+)
AND EMPLOYEE.SALARY BETWEEN MIN_SAL AND MAX_SAL
AND EMPLOYEE.JOB_CODE = JOB.JOB_CODE

 

이렇게하면 23개 나옴.

아래 두 조건은 다 가지고 있어서 LEFT JOIN 안해도 되고.

 

 

 

(2) ANSI 방식

 

- ANSI 방식은 조인 순서가 중요하다.

- 조인 테이블과 조인 조건은 두 방식 다 동일.

 

- 23개의 행을 보고싶다면, 

 

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME, SAL_LEVEL, JOB_NAME
FROM EMPLOYEE

LEFT JOIN DEPARTMENT ON EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID
LEFT JOIN LOCATION ON DEPARTMENT.LOCATION_ID = LOCATION.LOCAL_CODE
LEFT JOIN NATIONAL ON LOCATION.NATIONAL_CODE = NATIONAL.NATIONAL_CODE
JOIN SAL_GRADE ON EMPLOYEE.SALARY BETWEEN MIN_SAL AND MAX_SAL
JOIN JOB ON EMPLOYEE.JOB_CODE = JOB.JOB_CODE
;