2/27[ Database#6] Join

[ JOIN과 SubQuery ]

1. JOIN

4) Non-Equi Join : equal condition 이외의 비교연산자에 의한 join

    * 테이블의 어떤 컬럼도 join할 테이블의 컬럼에 일치하지 않을 때 사용

-- 모든 사원의 사번, 이름, 급여, 급여등급

select e.employee_id, e.first_name, e.salary, g.grade_level

from employees e, job_grades g

where e.salary between g.lowest_sal and g.highest_sal

order by e.salary desc;

5) Outer Join : 동일 조건에서 조인 조건을 만족하는 값이 없는 row를 조회하기 위해 사용

    * equi join 에서만 사용??

- join 조건 설정시 값이 없는 테이블 측에 (+) 입력

-- 모든 사원의 사번, 이름, 부서번호, 부서이름

-- 단, 부서가 미지정일 경우 부서이름을 '대기발령중'으로 출력

select e.employee_id, e.first_name, e.department_id, nvl(d.department_name, '대기발령중')

from employees e, departments d

where e.department_id = d.department_id;

        -- 이렇게 출력하면, department_id가 null 값인 Kimberely는 누락됨. 이럴때 Outter Join 사용!!

            -- * 외래키는 참조 테이블에 있는 값만 입력 가능. 단, null 값은 허용됨

            -- * 고유키에는 null 값이 허용되지 않음

select e.employee_id, e.first_name, e.department_id, nvl(d.department_name, '대기발령중')

from employees e, departments d

where e.department_id = d.department_id(+);

    >>> 위의 문제에서 (+)를 직원테이블에 입력하면?

    

        >> 킴벌리 행이 사라지고, 부서테이블에는 있는데 직원테이블에는 없는 contracting 부서 행이 출력됨

- (+)를 두개 쓸 수 없음. subquery나 union 등을 사용해야 함

 

▷ 예제

-- 모든 사원의 사번, 이름, 상관사번, 상관이름, 부서이름

-- 단, 상관이 없을 경우 상관이름에 '사장'으로 출력

-- 단, 부서가 미지정일 경우 부서이름을 '대기발령중'으로 출력

select e.employee_id 사번, e.first_name 이름, e.manager_id 상관사번,

        nvl(m.first_name, '사장') 상관이름,

        nvl(d.department_name, '대기발령중') 부서이름

from employees e, employees m, departments d

where e.manager_id = m.employee_id(+)

        and e.department_id = d.department_id(+);

    >>> 위 문제에서 departments를 join할 때, m 테이블의 manager_id를 가져오면 manager의 부서명이 나와버림

                    

    

* 아래 두 문제의 차이점 확인할 것

6) ANSI Join

- ANSI Join 의 defaul 값은 Inner Join

- 하나의 query 안에서 ANSI Join과 Oracle Join을 혼용하여 쓰지 않는다.

(1) Cross Join : 각 테이블의 행을 1:1 매칭하여 join (oracle join의 catesian join과 동일)

select *

from employees cross join departments;

(2) Inner Join : equality condition에 의한 join (oracle join의 equi join과 동일)

select e.employee_id, e.first_name, d.department_name

from employees e join departments d

on e.department_id = d.department_id

where e.department_id = 50;    -- on절 안에서 and를 붙여도 되긴 하나 권장X (cross 조인 결과값 안에서 추가로 조건 검색을 하므로 비효율적임)

- 일반조건을 추가할 때, On절 안에서 and로 붙여도 에러는 발생하지 않으나, 권장되지 않음.

    (테이블끼리 1:1 매칭된 결과값 안에서 일반조건을 판별하게 되므로 비효율적임. )

- ANSI Join의 defaul 값은 Inner Join이므로 Inner 생략 가능!

- Using : on절을 대체하여 간략하게 표현할 수 있음

- using절을 이용하는 경우 where절에서 해당 컬럼에 테이블 지정하면 에러 발생함

-- using 절 이용

select e.employee_id, e.first_name, d.department_name

from employees e join departments d      -- inner 생략 가능

using (department_id)    -- using절에 이용하는 컬럼은 테이블 지정하면 에러 발생함

where e.department_id = 50;

- Join 절 다음에는 On 절이 와야함. 따라서 3개이상의 Table을 Join할 경우,

    A Join B  On join 조건1  Join C  On join 조건2 의 형태로 써야 하며 이때 조건1과 2가 바뀌면 에러 발생함

 

-- 'seattle'에 근무하는 사번, 이름, 부서이름, 도시

select e.employee_id, e.first_name, d.department_name, l.city

from employees e join departments d     -- ansi join

                    on e.department_id = d.department_id

                        join locations l

                            on d.location_id = l.location_id

where lower(l.city) = lower('seattle');

(3) Natural Join : 자동으로 설정된 조인조건에 따라 테이블을 결합

- 같은 데이터형식과 컬럼명을 사용하고 있는 컬럼들이 join 조건(동일조건)이 됨

    * join 조건으로 설정되는 컬럼이 2개 이상이 될 수 있기에, 공통 값을 가진 컬럼이 1개인 경우에만 사용하는 것이 안전함.

select e.employee_id, e.first_name, d.department_name

from employees e natural join departments d

where department_id = 50;

    >> department_id 와 manager_id 두개의 컬럼 값을 이용해 같은 행을 골라내므로 위의 Inner Join 과 다른 결과값이 나옴

(4) Outer Join : 조건에 맞지 않는 행까지 반환하는 join (oracle join의 outer join과 동일)

▷ Left/Right Outer Join : join을 기준으로 값이 없는 테이블의 위치에 따라 left | right

-- 모든 사원의 사번, 이름, 부서번호, 부서이름

-- 부서가 미지정인 경우 부서이름은 '대기발령중'

select e.employee_id, e.first_name, e.department_id,

        nvl(d.department_name, '대기발령중')    -- nvl은 oracle의 함수임. db에 따라서는 nvl 쓰면 에러날 수 있음

from employees e left outer join departments d

on e.department_id = d.department_id;

-- 모든 부서에 근무하는 사원의 사번, 이름(사원없음), 부서번호, 부서이름

select e.employee_id, nvl(e.first_name, '사원없음'), d.department_id, d.department_name

from employees e right outer join departments d

on d.department_id = e.department_id(+);

▷ Full Outer Join : 기준에 맞지 않는 값이 모든 테이블 각각에 존재하는 경우 모든 테이블에 outer 설정을 하는..

    * oracle join에는 없는 기능!

select e.employee_id, nvl(e.first_name, '사원없음'),

        d.department_id, nvl(d.department_name ,'대기발령')

from departments d full outer join employees e

on d.department_id = e.department_id;

- end.