[DB] Join(inner, outer, self, natural,non-equi), Union, Index

Inner join

아래 세가지 경우 모두 완벽하게 동일하게 작동한다. 그러나 세번째 경우처럼 join의 조건은 on에 설정하고, 일반적인 조건은 where에 설정하여 한눈에 파악하기 쉽게 하는 것이 선호된다.

 

select employee_id, first_name, salary, department_name
from employees, departments
where employees.department_id = departments.department_id
and employee_id = 100;

select employee_id, first_name, salary, department_name
from employees, departments
where employees.department_id = departments.department_id
and employee_id = 100;

select employee_id, first_name, salary, department_name
from employees inner join departments
on employees.department_id = departments.department_id
where employee_id = 100;

 

 

또한, join 조건이 여러개일 때에도 방법은 여러가지이나, 아래 경우에서도 3번째 경우가 더 눈에 직관적으로 다가오기에 추천된다.

select employee_id, first_name, salary, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;

-- 위를 아래처럼 수정하면 더 보기 좋아짐. 조인조건(on)과 일반조건(where) 분리
select employee_id, first_name, salary, d.department_name, l.city
from employees e inner join departments d inner join locations l
on e.department_id = d.department_id
and d.location_id = l.location_id
where e.employee_id = 100;

-- 위를 아래처럼 하면 더 직관적으로 join마다 해당하는 on조건이 붙어있게 되서 더 잘 보임
select employee_id, first_name, salary, d.department_name, l.city
from employees e inner join departments d 
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id
where e.employee_id = 100;

 

using 사용하기

두개의 테이블에서 칼럼이름이 같을때 아래처럼 하면 편하게 사용가능하다.

select employee_id, first_name, salary, department_id, department_name
from employees e inner join departments d
using (department_id)
where e.employee_id = 100;

natural join

using마저도 귀찮다!! 알아서 연결해줌. 근데 주의해야 함. 공통 칼럼이 두개가 있으면 두개도 만족하게 되는 것만 가져오게 되서 내 예상과 다를 수 있음. 

-- 방법1(MySQL구문)
select last_name, e.department_id, e.manager_id
from employees e, departments d
where e.department_id=d.department_id and e.manager_id=d.manager_id; 

 

-- 방법2(Ansi표준)
select last_name, department_id, manager_id
from employees join departments
using(department_id,manager_id);


-- 방법3(natural 조인이용)
select last_name, department_id, manager_id
from employees
natural join departments;

공통 칼럼이 한개인줄 알고 썼는데 알고보니 같은게 두개 있으면 예상과 다른 결과가 나오니 매우 조심해야 함.

 

outer join

목적에 따라 left, right를 사용하면됨(아님 테이블 위치를 바꿔주거나 그게그거)

 

상황1)

회사에 근무하는 모든 사원의 부서정보를 보고 싶어. 그니까 employees랑 departments랑 join해야 함. 근데 지금 부서가 없는 사람도 있을텐데 걍 조인하면 그사람 안나옴. 따라서 outer조인을 해야 함. 아래처럼 left로 하면 왼쪽의 employee는 다~ 나오고 그에 매칭되는 부서id가 null이어도 괜찮다는 거임.

select e.employee_id, e.first_name, ifnull(d.department_name, '승진발령')
from employees e left outer join departments d
on e.department_id = d.department_id;

 

상황2)

근데 위처럼 하면 사원이 없는 부서는 안보임. 사원없어도 부서 다 보고 싶으면, 아래처럼 right하면됨.

select department_id, department_name, employee_id, first_name
from employees e right outer join departments
using (department_id);

 

union

full outer join은 mysql에 없음. 대신 union으로 구현가능.

union all은 겹쳐도 여러번 보임.

union은 합집합.

사용할 땐 union으로 있는 각각의 select절의 칼럼 순서를 동일하게 유지해줘야 함.

select ifnull(d.department_name, '승진발령'),e.employee_id, e.first_name
from employees e left outer join departments d
on e.department_id = d.department_id
union
select  department_name, employee_id, first_name
from employees e right outer join departments
using (department_id);

 

self join

사입장에서의 테이블과 매니저입장에서의 테이블을 따로 생각하면서 하면 생각하기 편하다.

-- 모든 사원의 사번, 이름, 매니저사번, 매니저이름
select e.employee_id, e.first_name, e.manager_id, m.employee_id, ifnull(m.first_name, '사장님')
from employees e left outer join employees m
on e.manager_id = m.employee_id;

 

non-equi join

fk가 아닌 애들로도 조인이 가능하다.

-- 모든 사원의 사번, 이름, 급여, 급여등급
select e.employee_id, e.first_name, e.salary
from employees e join salgrades s
on e.salary between s.losal and s.hisal;

그러나 단점: pk가 아니니까 인덱스가 없어서 full scan을 하게 됨.

 

이때 단점인 full scan과 관련하여 인덱스에 대해 더 알아보자.

 

index

board라는 테이블에 no는 pk이고, test는 일반 칼럼이라고 치자.

select * 
from board
where no between 9000 and 9500;

select *
from board
where test between 9000 and 9500;

이러면 차례로 no는 index range scan을 하고, test는 full scan을 한다. full scan이면 ㄹㅇ 모든 로우를 다 보는거임.

 

board의 index를 봐보자. pk로서 no칼럼이 인덱스에 걸려있고, BTREE알고리즘을 쓴다는 것을 보여준다.

 

no를 이용해 다른 검색도 해보자.

explain select *
from board
where no = 9000;

explain을 앞에 붙이면 아래처럼 type을 통해 무슨 검색인지 알 수 있음. 이러면 const 검색방법을 이용했음을 알 수 있다.

 

 

 

..그에 반해 index가 아닌 칼럼인 test칼럼을 써보면 type이 all로 나옴을 확인할 수 있다.

explain select *
from board
where test = 9000

 

이제 인덱스를 추가해보자

alter table board add index board_test_idx (test);

추가됐다!

다시 test=9000를 찾는 쿼리를 실행하면 이전에 했던 결과와 달리 type이 ref로 바뀌었고, ref에는 const로 설정되었다.

 

속도 측정은 수업에서는 확실히 줄었음을 확인할 수 있었다.

 

그러나 단점도 존재:

1. 인덱스 많아지면 메모리 차지

2. 데이터 수정삭제가 빈번한 칼럼은 index하면 안좋음(책 페이지처럼, 300페이지에 인덱스 설정해놨는데 그 앞에 계속 페이지가 추가되면 인덱스는 그대로 300인데 실제 페이지는 300+a페이지가 됨). 즉 pk처럼 수정이 거의 안되는 칼럼을 index로 설정해주는 게 좋다.

 

연습문제 - 여러 테이블 join

--   부서ID,나라ID,부서위치를 연결해서 다음과 같이 완성하시오   -- 1건
--        (관련테이블 : employees,locations2, departments,countries)
--      조건1 : 사원번호,사원이름,부서이름,도시,도시주소(street_address),나라명로 제목을 표시하시오
--      조건2 : 도시주소에  Vi 또는 St가 포함되어 있는 데이터만 표시하시오
--      조건3 : 나라명, 도시별로 오름차순정렬하시오
--      조건4 : 모든사원을 포함한다

select employee_id as "사원번호", last_name as "사원이름", department_name as "부서이름", 
       city as "도시",street_address as "도시주소", country_name as "나라이름"
from employees
left join departments using(department_id)
left join locations2 on(location_id=loc_id)
left join countries using(country_id)
where street_address like '%Vi%' or street_address like '%St%'
order by 6, 4;