[오라클] 조인 JOIN

조인 JOIN

- 둘 이상의 테이블들을 결합하여 데이터를 조회하는 것

- 일반적으로 PK와 FK를 갖고 있는 테이블을 통해 검색을 한다.

- 관계형 데이터베이스의 가장 큰 장점이면서 가장 중요한 핵심 기능이다.

- 조인을 하는 이유는 데이터의 무결성을 유지하기 위하여 테이블을 정규화하는 작업으로 인해 둘 이상으로 나뉘어진 정보를 조회하기 위한 목적을 가지고 있다.

 

CROSS JOIN( = 카르티시안 조인) A x B 

[Q] 직원 이름, 월급, 부서명

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM DEPARTMENTS d, EMPLOYEES e;
-- 107 x 27

코드	의미
FROM DEPARTMENTS d, EMPLOYEES e;	별칭을 만들어 사용한다. 별칭을 따로 만들지 않으면 테이블명을 앞에 써야한다.

 

NATURAL JOIN    A PK X B FK

= EQUL JOIN : PK와 fK가 같아야 조인처리 됨
- 부모의 프라이머리 키와 자식의 포린 키가 같은 것끼리 

SELECT * FROM DEPARTMENTS; --> 자식
SELECT * FROM LOCATIONS; --> 부모
SELECT * FROM COUNTRIES;

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM DEPARTMENTS d, EMPLOYEES e
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID;

PK : Primary Key : 기본키	FK : Foreign Key : 외래키
각 데이터를 고유하게 식별하는 용도	다른 테이블에 있는 기본 키를 참조해서 사용할 때
외래키가 기본키가 될 수 있을까? ==> 외래키가 기본키가 되었다면, 참조 관계를 없애고 테이블을 합쳐야 한다. 하지만 외래키가 기본키 집합의 일부가 될 수는 있다.

 

[Q] 부서명(DEPARTMENTS)과 도시명(LOCATIONS)을 조회하시오

SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L
WHERE L.LOCATION_ID = D.LOCATION_ID;

 

[Q] 부서명(DEPARTMENTS), 나라명(LOCATIONS), 그 나라의 도시명(COUNTRIES)

SELECT D.DEPARTMENT_NAME, C.COUNTRY_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L, COUNTRIES C
WHERE D.LOCATION_ID = L.LOCATION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID;

 

INNER JOIN

- 결과는  EQUI JOIN 과 동일

- 문법이 다르다.

- INNER 라는 키워드는 생략가능

- WHERE -> ON

 

','-> INNER JOIN ,  WHERE -> ON 

(JOIN ON JOIN 이라고도 한다.)

SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D INNER JOIN LOCATIONS L
ON L.LOCATION_ID = D.LOCATION_ID;

 

SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D INNER JOIN LOCATIONS L
ON L.LOCATION_ID = D.LOCATION_ID;

-- INNER 키워드 생략
SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D JOIN LOCATIONS L
ON L.LOCATION_ID = D.LOCATION_ID;

-- ON -> USING
SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D JOIN LOCATIONS L
USING(LOCATION_ID);

 

이름, 급여, 부서명을 JOIN~ON, JOIN~USING

JOIN~ON (ANSI표준)

ELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

코드	의미
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;	같은 이름의 키끼리 앞에 테이블의 별칭을 넣어 조인한다.

 

JOIN~USING

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID);

코드	의미
USING (DEPARTMENT_ID);	공통된 키를 USING( ) 안에 작성한다.

 

3개의 테이블에서 조인하기

[Q] 부서명, 도시명, 나라명을 조회하시오

1. 오라클 문법

SELECT D.DEPARTMENT_NAME, L.city,C.COUNTRY_NAME
FROM DEPARTMENTS D, LOCATIONS L ,COUNTRIES C
WHERE D.LOCATION_ID = L.LOCATION_ID
    AND
    L.COUNTRY_ID = C.COUNTRY_ID;

 

2. ANSI 표준 JOIN~ON

SELECT D.DEPARTMENT_NAME, L.city,C.COUNTRY_NAME
FROM DEPARTMENTS D JOIN LOCATIONS L 
ON D.LOCATION_ID = L.LOCATION_ID
JOIN COUNTRIES C
ON  L.COUNTRY_ID = C.COUNTRY_ID;

 

3. ANSI 표준 JOIN~USING

SELECT D.DEPARTMENT_NAME, L.city,C.COUNTRY_NAME
FROM DEPARTMENTS D JOIN LOCATIONS L 
USING (LOCATION_ID)
JOIN COUNTRIES C USING (COUNTRY_ID);

 

[Q] 이름, 입사일, 근속연수, 부서명, 직급 이름, 급여

SELECT E.FIRST_NAME, E.HIRE_DATE, TRUNC((SYSDATE - HIRE_DATE)/365) AS "근속연수",
    D.DEPARTMENT_NAME, J.JOB_TITLE, E.SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID)
JOIN JOBS J
 -- USING (JOB_ID)
ON E.JOB_ID = J.JOB_ID 
-- USING은 컬럼의 이름이 동일해야 쓸 수 있음
-- 셀프조인(내가 나를 참조하는것) 
ORDER BY 근속연수 DESC;

ON 과 USING 같이 사용할 수 있다. (셀프조인 때 많이 사용한다.)

 

뷰로 만들기

- VIEW 를 만들때는 ORDER_BY 가 들어가면 안된다
- 하나의 객체이기 때문에 두개를 만들 수 없다.

CREATE VIEW MY_VIEW AS (
    SELECT E.FIRST_NAME, E.HIRE_DATE, TRUNC((SYSDATE - E.HIRE_DATE)/365) AS "근속연수",
        D.DEPARTMENT_NAME, J.JOB_TITLE, E.SALARY
    FROM EMPLOYEES E JOIN DEPARTMENTS D
    USING (DEPARTMENT_ID)
    JOIN JOBS J
    -- USING (JOB_ID)
    ON E.JOB_ID = J.JOB_ID
);

 

사용 방법

SELECT * FROM MY_VIEW
ORDER BY "근속연수";

 

삭제 방법

DROP VIEW MY_VIEW;

 

OUTER JOIN

- FK 를 가진 테이블의 데이터가 NULL 일때 조회되지 않는 레코드가 생기므로,  조회되지 않는 데이터까지 조회하기 위한 조인 방법

- JOIN 조건이 같이 않을 경우에도 결과를 반환하고자 할때

- 종류: LEFT OUTER, RIGHT OUTER, FULL OUTER

LEFT OUTER

먼저 왼쪽 테이블의 데이터를 가져오고 오른쪽 테이블을 가져오는데, 이때 조인 조건이 맞지 않으면 NULL로 채운다.

[Q] 이름, 부서명을 조회하시오.

1) 오라클 문법 D: PK  E: FK

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM  DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID(+) = E.DEPARTMENT_ID;
-- 부모 PK 쪽에 + 를 붙임 (NULL을 가지고 있지 않은 쪽)

 

2)  LEFT OUTER JOIN~ON (LEFT / RIGHT : 자식테이블에 방향을 알려주는 것)

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM  EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D  
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;

 

3)  LEFT OUTER JOIN~USING

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM  EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D  
USING (DEPARTMENT_ID);

 

4) RIGHT OUTER JOIN~ON

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM DEPARTMENTS D RIGHT OUTER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;

 

5)  LEFT OUTER JOIN~USING

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM DEPARTMENTS D RIGHT OUTER JOIN EMPLOYEES E 
USING (DEPARTMENT_ID);

 

SELF JOIN

PK, FK 가 하나의 테이블 내에 존재

[Q] 직원의 아이디, 직원의 이름과 매니저 이름을 조회하시오.

니나의 상관은 스티븐 입니다. ( E1 부모 / E2 자식 )

1) 오라클 문법

SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME, E2.MANAGER_ID, E1.FIRST_NAME
FROM EMPLOYEES E1 , EMPLOYEES E2 --EMPLOYEES 가 마치 두 개인 것 처럼
WHERE E2.MANAGER_ID = E1.EMPLOYEE_ID;

 

2) JOIN~ON

SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME, E2.MANAGER_ID, E1.FIRST_NAME
FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 --EMPLOYEES 가 마치 두 개인 것 처럼
ON E2.MANAGER_ID = E1.EMPLOYEE_ID;

 

3) JOIN~USING ==>  사용 못 한다.

 

[Q] 107명 모든 직원의 정보가 출력 되도록 OUTER JOIN 하시오.

SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME, E2.MANAGER_ID, E1.FIRST_NAME
FROM EMPLOYEES E1 RIGHT OUTER JOIN EMPLOYEES E2
ON E2.MANAGER_ID = E1.EMPLOYEE_ID
ORDER BY 1;

코드	의미
SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME, E2.MANAGER_ID, E1.FIRST_NAME	자식 테이블의 사원번호, 이름, 매니저 번호  - 부모테이블의 이름

 

연습문제

1. JOIN 을 이용하여 사원번호가 100번인 사원번호, 부서번호, 부서 이름을 조회 하시오

SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E JOIN DEPARTMENTS D 
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE  E.EMPLOYEE_ID =100;

 

2. INNER JOIN 을 이용하여 사원명과 함께 그 사원이 속한 부서의 도시이름(CITY), 지역이름(REGION_NAME)을 조회하시오

SELECT E.FIRST_NAME, L.CITY, R.REGION_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L,COUNTRIES C, REGIONS R

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND
    D.LOCATION_ID = L.LOCATION_ID
    AND
    L.COUNTRY_ID = C.COUNTRY_ID
    AND
    C.REGION_ID = R.REGION_ID;

 

3. 100 번 부서에 속하는 직원의 이름과 직원의 담당 업무명(JOB_TITLE), 부서가 있는 도시명을 조회하시오.

SELECT E.FIRST_NAME, J.JOB_TITLE, L.CITY
FROM EMPLOYEES E,JOBS J, LOCATIONS L, DEPARTMENTS D 
WHERE  D.DEPARTMENT_ID =100
    AND
    D.DEPARTMENT_ID = E.DEPARTMENT_ID
    AND
    J.JOB_ID = E.JOB_ID
    AND
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND
    D.LOCATION_ID = L.LOCATION_ID;

 

[참고] HR  ENTITY DIAGRAM

hr 의 엔티티 다이어그램을 보면 조회하고 싶은 것과 다른 테이블이 왜 필요한지 알 수 있다.