[Oracle] Join

-- 2024년 2월 2일(금)

 

*** 조인(Join)

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

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

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

- 조인을 하는 이유는 데이터의 무결성을 유지하기 위하여 테이블을 정규화하는

작업으로 인해 둘 이상으로 나뉘어진 정보를 조회하기 위한 목적을 가지고 있다.

 

 

** Cross Join(=카르테시안 조인) : A x B

-- 직원이름, 월급, 부서명

select e.first_name, e.salary, d.department_name

from departments d, employees e;

 

 

** EQUI Join : PK와 FK가같아야 조인 처리된다

-- 직원이름, 월급, 부서명

select e.first_name, e.salary, d.department_name

from departments d, employees e

where d.department_id = e.department_id;

 

-- 부서명(departments)과 도시명(locations)을 조회

select * from departments;

select * from locations;

 

select d.department_name, l.city

from departments d, locations l

where l.location_id = d.location_id;

 

 

-- 부서명, 나라명, 도시명

select * from countries;

 

select d.department_name, c.country_name, l.city

from departments d, countries c, locations l

where

d.location_id = l.location_id and

c.country_id = l.country_id;

 

 

 

*** Inner Join 

-- 결과는 EQUI Join과 동일

-- 문법은 다르다

-- inner 키워드는 생략 가능

-- where --> on

 

 

-- 부서명(departments)과 도시명(locations)을 조회

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 사용하여 출력

-- 1) 오라클 문법

select e.first_name, e.salary, d.department_name

from employees e, departments d

where e.department_id = d.department_id;

 

 

-- 2) ANSI 표준 (join ~ on) : inner 생략 가능

select e.first_name, e.salary, d.department_name

from employees e join departments d

on e.department_id = d.department_id;

 

 

-- 3) ANSI 표준 (join ~ using)

select e.first_name, e.salary, d.department_name

from employees e join departments d

using (department_id);

 

 

 

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

-- [연습] 부서명, 도시명, 나라명을 조회

 

-- 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) : join한 결과에 다시 join

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);

 

 

-- [연습] 이름, 입사일, 근속년수, 부서명, 직급이름, 급여 조회

-- 1) 오라클 문법

select

e.first_name, e.hire_date,

floor(months_between(sysdate, hire_date) / 12) as 근속년수,

d.department_name, j.job_title, e.salary

from employees e, departments d, jobs j

where

e.department_id = d.department_id and

e.job_id = j.job_id

order by 4;

 

 

-- 2) join ~ on

select

e.first_name, e.hire_date,

floor(months_between(sysdate, hire_date) / 12) as 근속년수,

d.department_name, j.job_title, e.salary

from employees e join departments d

on e.department_id = d.department_id

join jobs j

on e.job_id = j.job_id

order by 4;

 

 

-- 3) join ~ using

select

e.first_name, e.hire_date,

floor(months_between(sysdate, hire_date) / 12) as 근속년수,

d.department_name, j.job_title, e.salary

from employees e join departments d

using (department_id)

join jobs j

using (job_id)

order by 4;

 

 

-- 4) 뷰로 만들기

create view MY_VIEW as (

select

e.first_name, e.hire_date,

floor(months_between(sysdate, hire_date) / 12) as 근속년수,

d.department_name, j.job_title, e.salary

from employees e, departments d, jobs j

where

e.department_id = d.department_id and

e.job_id = j.job_id

);

 

 

select * from my_view;

drop view my_view;

 

 

 

*** Outer Join 

-- FK를 가진 테이블의 데이터가 null일 때 조회되지 않는 레코드가 생기므로,

-- 조회되지 않는 데이터까지 조회하기 위한 조인 방법

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

-- 종류: left outer, right outer, full outer

 

 

** Left Outer

-- 먼저 왼쪽 테이블의 데이터를 가져오고, 오른쪽 테이블을 가져온다

-- 이 때 join 조건이 맞지 않으면 null로 채운다

-- PK 부모 테이블을 앞에, FK 자식 테이블을 뒤에 둠

-- 부모 테이블에 (+)를 붙인다

 

 

-- 이름과 부서명을 조회

-- 1) 오라클 문법

-- 부모의 PK 쪽에 (+)를 붙임

select e.first_name, d.department_name

from departments d, employees e

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

 

 

-- 2) LEFT OUTER JOIN ~ ON

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);

 

 

-- 3) RIGHT 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가 하나의 테이블 내의 존재

 

-- 직원 아이디, 이름, 매니저 아이디, 이름을 조회

-- 니나의 매니저는 스티븐

-- 1) 오라클 문법

select e2.employee_id, e2.first_name, e2.manager_id, e1.first_name

from employees e1, employees e2

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 join employees e2

on e2.manager_id = e1.employee_id;

 

 

-- 3) JOIN ~ USING은 컬럼명이 달라서 사용할 수 없음

 

 

-- 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;

 

 

-- [연습문제]

-- 1. join을 이용하여 사원번호가 100인 사원의 사원번호, 부서번호, 부서이름 조회

select employee_id, department_id, department_name

from employees join departments

using (department_id)

where employee_id = 100;

 

 

-- 2. inner join을 이용하여 직원의 이름과 그 직원이 속한 부서의 도사명, 지역명을 조회

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, departments d, jobs j, locations l

where

e.department_id = 100 and

j.job_id = e.job_id and

e.department_id = d.department_id and

d.location_id = l.location_id;

 

 

 

[연습문제]

 

-- 1. JOIN을 이용하여 사원ID가 100번인 사원의 부서번호와 부서이름을 출력하시오

select e.department_id, d.department_name

from employees e, departments d

where

e.employee_id = 100

and

d.department_id = e.department_id;

 

 

-- 2. INNER JOIN을 이용하여 사원이름과 함께 그 사원이 소속된 도시이름과 지역명을 출력하시오

select e.first_name, l.city, r.region_name

from employees e, locations l, regions r, departments d, countries c

where

d.department_id = e.department_id

and

d.location_id = l.location_id

and

l.country_id = c.country_id

and

c.region_id = r.region_id;

 

 

-- 3. INNER JOIN과 USING 연산자를 사용하여 100번 부서에 속하는

-- 직원명과 직원의 담당 업무명, 속한 부서의 도시명을 출력하시오.

-- (100번 부서에는 직원 6명있음)

select e.first_name, j.job_title, l.city

from employees e, departments d, jobs j, locations l

where

e.department_id = 100

and

j.job_id = e.job_id

and

e.department_id = d.department_id

and

d.location_id = l.location_id;

 

 

-- 4. JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서ID, 도시명을 출력하시오

select e.first_name, d.department_id, l.city

from employees e, departments d, locations l

where

e.commission_pct is not null

and

e.department_id = d.department_id(+)

and

d.location_id = l.location_id(+)

order by 1;

 

 

-- 5. INNER JOIN과 와일드카드를 사용하여 이름에 A가 포함된 모든 사원의 이름과 부서명을 출력하시오(단, 대소문자 구분 없음)

select e.first_name, d.department_name

from employees e, departments d

where

d.department_id = e.department_id

and

e.first_name like '%A%';

 

 

-- 6. JOIN을 사용하여 Seattle에 근무하는 모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오

select first_name, job_title, department_id, department_name

from employees join departments

using (department_id)

join jobs

using (job_id)

join locations

using (location_id)

where

city = 'Seattle';

 

 

-- 7. SELF 조인을 사용하여 사원의 이름 및 사원번호와 매니저 이름 및 매니저 번호와 함께 조회하시오.

select e2.first_name, e2.employee_id, e1.first_name, e2.manager_id

from employees e1, employees e2

where e2.manager_id = e1.employee_id;

 

 

-- 8. OUTER JOIN, SELF JOIN을 사용하여 관리자가 없는 사원을 포함하여 사원번호를 기준으로 내림차순 정렬하여 조회

select e2.first_name as "직원명", e2.employee_id, e1.first_name as "매니저명", e2.manager_id

from employees e1 right outer join employees e2

on e2.manager_id = e1.employee_id

order by 1 desc;

 

select e2.first_name as "직원명", e2.employee_id, e1.first_name as "매니저명", e2.manager_id

from employees e2 left outer join employees e1

on e2.manager_id = e1.employee_id

order by 1 desc;

 

 

-- 9. SELF JOIN을 사용하여 'Oliver' 사원의 부서명, 그 사원과 동일한 부서에서 근무하는 동료 사원의 이름을 조회.

-- 단, 각 열의 별칭은 부서명, 동료로 할 것.

select d.department_name as 부서명, temp.first_name as 동료 from

(select first_name, department_id from employees

where department_id = (select department_id

from employees

where first_name = 'Oliver')

) temp, departments d

where temp.department_id = d.department_id

order by 1;

 

 

-- 10. SELF JOIN을 사용하여 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 매니저 이름 및 입사일과 함께 출력하시오

select e1.first_name, e1.hire_date, e2.first_name as "매니저이름", e2.hire_date as "입사일"

from employees e1 join employees e2

on e1.manager_id = e2.employee_id

and e1.hire_date < e2.hire_date;

 

 

-- 11. Last name이 ‘King’을 Manager로 둔 사원의 이름과 급여를 조회하시오.

select e1.first_name, e1.salary

from employees e1, employees e2

where

e1.manager_id = e2.employee_id

and

e2.last_name = 'King';

 

 

-- 12. Finance부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오

select department_id, first_name, job_title

from departments join employees

using (department_id)

join jobs

using (job_id)

where department_name = 'Finance';

 

 

-- 13. 평균 급여보다 많은 급여를 받고 이름에 M이 포함된 사원과 같은 부서에서

-- 근무하는 사원의 사원번호, 이름, 급여를 조회하시오

select employee_id, first_name, salary

from employees

where

salary >= (select avg(salary)

from employees)

and

first_name like '%M%';