SQL JOIN

1. JOIN

필요한 데이터가 두 개 이상의 테이블에 나눠져 있을 때 데이터를 합쳐서 가져오고자 할 때 사용

 

- 직원 ID가 100번인 직원의 부서 이름을 출력
- 직원 ID : 직원테이블
- 부서 이름 부서테이블

 

- 1. 100번의 부서ID 찾기

SELECT employee_id, department_id
FROM employees
WHERE employee_id = 100;

 

- 2. 부서 ID가 90인 부서의 이름찾기

SELECT department_id, department_name
FROM departments
WHERE department_id = 90;

 

- JOIN 문법 사용시

SELECT E.employee_id, D.department_id, D.department_name
FROM employees E,
           departments D
WHERE E.employee_id = D.department_id
AND E.employee_id = 100;

 

2. FROM절

- 여러개의 테이블을 사용 가능, 테이블에 별칭도 생성이 가능하다.

- 테이블 여러개 사용시 어느 테이블에서 가지고 온 컬럼인지 명확하게 구분을 해야 된다.

- 방법 : 테이블(별칭).컬럼

SELECT 출력하고 싶은 컬럼
FROM 테이블 A 별칭, 테이블 B 별칭

SELECT D.department_id
FROM employees E, Departments D;

 

- employee_id은 직원테이블에만 있는 컬럼이므로 따로 명시 안해줘도 오류발생하지 않는다.

SELECT employee_id
FROM employees E, Departments D;

 

- 별칭이 있는 테이블은, 그 이후 오는 실행절(SELECT, WHERE 등등)에는 모두 별칭을 사용을 해야 한다.

SELECT D.department_id
FROM employees E
    , Departments D;

 

3. CROSS JOIN

- 카티션 곱이라고 불린다.
- 조건 없이 모든행을 JOIN해서 출력
- 테이블 1행의 개수 * 테이블2 행의 개수 = CROSS JOIN한 행의 개수

- 108개 * 27개 = 2916개 출력

SELECT E.employee_id
       , D.department_id
       , D.department_name
from employees E
     , departments D;

 

4. INNER JOIN

- join == inner join
- 테이블간의 교집합

 

- ex)직원테이블의 부서 ID = 부서테이블 직원 ID가 같아야 한다.

 

4.1 WHERE 조건절 사용

SELECT employee_id, E.department_id, department_name
FROM employees E, departments D
WHERE E.department_id = D.department_id
ORDER BY 1;

4.2  ANSI 문법 모든 DBMS에서 사용가능

SELECT employee_id, E.department_id, department_name
FROM employees E INNER JOIN departments D
ON(E.department_id = D.department_id);

 

4.3 실습

- 직원 테이블과 직업 테이블을 이너 조인을 사용하여 직원ID 직업ID 직업TITLE 출력
- WHERE 조건절과 annsi 문법 둘다 사용

-- where 절
SELECT employee_id
     , E.job_id
     , job_title
FROM employees E
     , jobs J
WHERE E.job_id = J.job_id
ORDER BY 1;

-- ansi 문법
SELECT employee_id
     , E.job_id
     , job_title
FROM employees E INNER JOIN jobs J
ON( e.job_id = j.job_id)
ORDER BY 1;

 

5. OUTER JOIN

- INNER JOIN의 경우 NULL이 포함되지 않는다.
- NULL값을 출력하고 싶을때 사용

- LEFT OUTER JOIN : 왼쪽 테이블 기준으로 OUTER JOIN(왼쪽 NULL 값 포함)
- RIGHT OUTER JOIN : 오른쪽 테이블 기준으로 OUTER JOIN(오른쪽 NULL 값 포함)
- FULL OUTER JOIN : 두 테이블 기준으로 OUTER JOIN(두 테이블 NULL 값 포함) 

 

5.1 LEFT OUTER JOIN

- LEFT가 직원 테이블이기 때문에 직원 테이블에 있는 모든 NULL을 포함한 모든 값을 출력

 

- ANSI 문법

SELECT employee_id
     , E.department_id
     , department_name
FROM employees E LEFT OUTER JOIN departments D
ON(E.department_id = D.department_id);

 

- ORACLE 문법 (+)
- LEFT OUTER JOIN >> 반대쪽인 오른쪽 테이블에 + 기호를 사용

SELECT employee_id
     , E.department_id
     , department_name
FROM employees E
     , departments D
WHERE E.department_id = D.department_id(+);

 

5.2 RIGHT OUTER JOIN

- ANSI 문법

SELECT employee_id
     , E.department_id
     , department_name
FROM employees E RIGHT OUTER JOIN departments D
ON(E.department_id = D.department_id);

 

- ORACLE 문법 (+)

SELECT employee_id
     , E.department_id
     , department_name
FROM employees E
     , departments D
WHERE E.department_id(+) = D.department_id;

 

5.3 FULL OUTER JOIN

- ANSI 문법

SELECT employee_id
     , E.department_id
     , department_name
FROM employees E FULL OUTER JOIN departments D
ON(E.department_id = D.department_id);

 

- ORACLE 문법

- (+)로 표현이 불가능, UNION연산자(합집합)으로 사용

- UNION(합집합) = 중복 포함X

- UNION ALL(합집합) = 중복 포함O

UNION
SELECT employee_id
     , E.department_id
     , department_name
FROM employees E
     , departments D
WHERE E.department_id = D.department_id;

 

6. 실습

- 부서테이블과 직원 테이블을 INNER JOIN을 사용해서 부서 NAME 매니저 ID 직원ID 출력

SELECT department_name
     , D.manager_id 
     , E.employee_id 
FROM departments D INNER JOIN employees E 
ON(D.manager_id = E.manager_id)
ORDER BY 1;

- 부서 테이블을 기준으로 LEFT OUTER JOIN을 사용해서 부서 NAME 매니저 ID 직원ID 출력

SELECT department_name
     , D.manager_id 
     , E.employee_id 
FROM departments D LEFT OUTER JOIN employees E 
ON (D.manager_id = E.manager_id)
ORDER BY 1;

 

- 부서 테이블을 기준으로 RIGHT OUTER JOIN을 사용해서 부서 NAME 매니저 ID 직원ID 출력

SELECT department_name
     , D.manager_id 
     , E.employee_id 
FROM departments D RIGHT OUTER JOIN employees E 
ON (D.manager_id = E.manager_id)
ORDER BY 1;

 

- FULL OUTER JOIN을 사용해서 부서 NAME 매니저 ID 직원ID 출력

SELECT department_name
     , D.manager_id 
     , E.employee_id 
FROM departments D FULL OUTER JOIN employees E 
ON (D.manager_id = E.manager_id)
ORDER BY 1;

 

- 부서테이블, 위치테이블, 나라테이블을 INNER JOIN하여 각 부서의 도시와 나라 이름을 출력

SELECT C.country_name, L.city
FROM departments D INNER JOIN locations L
ON D.location_id = L.location_id
INNER JOIN countries C
ON L.country_id = C.country_id;