[210525화] 오라클 조인(등가/외부/셀프)

데이터베이스를 사용하는 이유

: 일관성 보장 

: 파일로 저장하게 되면 특정파일에 종속될 가능성이 높기 때문에 데이터베이스를 사용하는 것이다.

: 정형화된 데이터, 빅테이터는 정형화되어 있지 않은 데이터이기 때문에 SQL을 통해 정형화시킨다. NoSQL(NOT ONLY)

: 하나의 표의 중복성을 배제하기 위해 여러 개로 쪼개고(정규화), 쪼개진 테이블을 연결하기 위해 관계형성(FK를 사용)을 한다. 

 

Ex) 인터파크 정규화 신나게 쪼개놓았는데 그 결과 조인이 많아짐 → 조인이 많아지니 속도와 성능이 떨어짐 → 다시 정규화에 반하는 역정규화를 한다. 

 

테이블의 부모, 자식관계를 알기 위해서는 Foreign key를 살펴보면 알 수 있다. 

 

 

[테이블 살펴보기] 

▶DEPARTMENTS 테이블

부모가 2개이다. 어떻게 알 수 있냐면 FK로 MANAGER_ID, LOCATION_ID가 있기 때문이다. 

 

▶LOCATION테이블 

부모테이블 contries

 

▶contries테이블

region이 부모

 

▶job테이블

부모 없음

employee테이블

employee, department_id, job_id가 부모 

 

▶job_history테이블

DEPARTMENT_ID, JOB_ID가 부모테이블이다. 

 

 

 

[교재 p214]

▶ 조인

: 중복성을 배제하기 위해 여러 개로 쪼개 놓은 정규화시킨 테이블을 다시 연결시키는 것

: 여러 테이블을 하나의 테이블처럼 사용한다. 

: 관계가 있는 것들만 조인이 가능하다.(FP)

: 조인을 할 경우, 반드시 where이 있다고 생각하면 된다. 

: 어떤 테이블에 어떤 컬럼명이 있는지 알아야 사용할 수 있다. 

: 등가조인, 비등가조인, 자체조인, 외부조인 등

 

 

[카르테시안 조인]

: 좋지 않다.

select * 
from employees, departments;

 

 

[EQUI JOIN(등가조인)]

: 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식이다.

: 일반적으로 가장 많이 사용되는 조인방식이다. 

 

사장인 KING을 제외하고 106개의 데이터가 나온다. 

select * 
from employees, departments
where employees.department_id = departments.department_id; 

 

★테이블명에 별칭을 줄 때 as는 생략한다. 

 

[사원번호, 이름, 급여, 부서명]

 

  
select e.employee_id, e.first_name, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id; 

 

 

[d부서번호, d부서명, l지역 ]

select 
    d.department_id, d.department_name, l.city
from 
    departments d, locations l
where d.location_id = l.location_id; 

 

 

[도시명(LOCATION), 나라명(COUNTRIES)]

locations가 자식 

 

SELECT 
    l.city, c.country_name
from 
    locations l, countries c
where 
    l.country_id = c.country_id;
    

 

[사원번호, 이름, 급여, 부서명을 출력(급여가 10000이상인 사람) : AND, OR]

조인시 조건을 줄 때 대부분 AND의 경우가 많다.

SELECT 
    e.employee_id, e.FIRST_NAME, e.SALARY, d.DEPARTMeNT_NAME
FROM 
    employees e, departments d
where
    e.department_id = d.department_id
    AND e.salary >= 10000;

 

 

[사원번호, 이름, 급여, 부서명을 출력(급여가 10000이상~13000인 사람) : AND, OR ]

▶ AND 사용

SELECT 
    e.employee_id, e.FIRST_NAME, e.SALARY, d.DEPARTMeNT_NAME
FROM 
    employees e, departments d
where
    e.department_id = d.department_id
    AND 
    e.salary >= 10000 AND e.salary <= 20000;

 

 

▶ BETWEEN 사용

컬럼명 BETWEEN A AND B 

SELECT 
    e.employee_id, e.FIRST_NAME, e.SALARY, d.DEPARTMeNT_NAME
FROM 
    employees e, departments d
where
    e.department_id = d.department_id
    AND
    e.salary BETWEEN 10000 AND 13000;

 

 

[연습문제]

1. 부서번호, 부서명, 도시, 나라 출력

SELECT 
    d.department_id, 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
    order by d.department_id; 

 

 

2. 부서번호, 부서명, 도시, 나라, 대륙 출력

departments 테이블과 regions 테이블, locations 테이블과 regions 테이블에는 FK키가 없다. 

select d.department_id, d.department_name, l.city, c.country_name, r.region_name
from departments d, locations l, countries c, regions r
where d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id;

 

 

3. 사원이름, 직책명, 급여

select e.first_name, j.job_title, e.salary

from employees e, jobs j

where e.job_id = j.job_id;

 

 

4. 커미션을 받지 않은 모든 사원의 이름, 부서명

select e.first_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id and e.commission_pct is not null;

 

 

5. 서브쿼리 문제  

Seattle에 근무하는 모든 사원의 이름, 업무명, 부서명

SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME, J.JOB_TITLE, L.CITY
FROM EMPLOYEES E, JOBs J, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND E.JOB_ID = J.JOB_ID
    AND L.LOCATION_ID = D.LOCATION_ID
    AND L.CITY = 'Seattle';

 

6. 사원번호, 이름, 성, 부서명

EQUI JOIN(등가조인)이 가지고 있는 특징때문에 내부, 외부 조인의 개념이 나왔다.

데이터의 개수는 총 107개이지만 KING을 제외한 데이터의 값 106개만 출력한다. 

employees테이블의 최고직급이므로 상급자가 존재하지 않아, null로 되어있다. 

 

▶ 외부조인 

: 조인 기준 열의 NULL을 처리하는 것을 목적으로 자주 사용되는 조인 방식이다. 

→ 두 테이블간 조인 수행에서 조인 기준 열의 어느 한쪽이 null이어도 강제로 출력하는 방식을 외부조인이라고 한다. 외부조인은 좌우를 따로 나누어 지정하는데 where절에 조인 기준 열 중 한쪽에 (+) 기호를 붙여줍니다. 

→ 즉, 왼쪽 외부 조인은 왼쪽 열을 기준으로 오른쪽 열의 데이터 존재 여부에 상관없이 출력하라는 뜻이다. 

→ 오른쪽 외부 조인 또한 오른쪽 열을 기준으로 왼쪽 열 데이터의 존재와 상관없이 데이터를 출력하라는 뜻이다. 

 

 

 

 

[LEFT OUTER JOIN(왼쪽 외부 조인)]

★NULL을 가지고 있지 않은 쪽에 +를 붙인다. 

OUTER JOIN은 PRIMARY KEY를 가진 키에게 +를 붙인다.

 

데이터 107개 모두 출력된 것을 확인할 수 있다. 

 

 

[P231]

[셀프조인(자체조인)]

: 하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식

: select문 방식으로는 현재 행을 벗어나 다른 행의 데이터를 가져올 수 없다. 이 문제를 해결할 쉬운 방법으로는 해당 테이블과 똑같은 테이블을 하나 더 만들어 조인하는 것이다. 

: 물리적으로 동일한 테이블에서 데이터를 나란히 출력해야 할 때 발생할 수 있는 문제점을 해결한다.  

 

select employee_id, first_name, manager_id
from employees 
order by employee_id; 

 

 

(사원번호, 임직원명, 매니저명)

 

select e1.employee_id 사원번호, e1.first_name 임직원명, e2.first_name 매니저명
from employees e1, employees e2
where e1.manager_id = e2.employee_id(+)
order by 1; 

 

 

[Steven Kin의 매니저는 xxx입니다. ]

매니저 

Steven Kin은 매니저가 없어  null값임에도 외부 조인을 이용하여 출력되었다. 

★null은 화면에 표시되지 않는다.

 

만일, 외부조인을 사용하지 않는다면 아래와 같이 Steven은 제외된채로 순서에 밎지 않게 출력될 것이다. 

 

[테이블 job_history]

 

job_history에서 employee_id 대신 이름이 나오게 하고 싶다면? (이름, 근무기간, 직책명, 부서명)

 

 

SELECT 
    e.first_name 이름, 
    j1.start_date||'~'|| j1.end_date 근무기간,
    j2.job_title 직책, 
    d.department_name 부서명 
from employees e, job_history j1, jobs j2, departments d
where 
    e.employee_id = j1.employee_id
and j2.job_id = j1.job_id
and d.department_id = j1.department_id;