SQL-JOIN과 하위쿼리

목차

1.JOIN ON

2.INNER JOIN

3.OUTER JOIN

4.CROSS JOIN

5.하위 쿼리

 

1. JOIN ON

select e.emp_name ,e.emp_id, d.dept_name ,e.salary   # 테이블명.속성명 으로 써줘야 어느테이블인지 알수있음

from employee as e

join deparment as d on e.dept_id=d.dept_id;

 

-- unit(부) -  department(자)
                 department(부) - employee(자)
                                        employee(부) - vacation(자) 

 

select e.emp_name, d.dept_name, u.unit_name

from vacation as v

join employee as e on (v.emp_id=e.emp_id) 

join department as d on(d.dept_id=e.dept_id)

join unit as u on (d.unit_id=u.unit_id)

where gender='f';

-- LEFT/RIGHT join ~ on ~

select d.dept_id, d.dept_name, u.unit_id, u.unit_name

from department as d join unit as u on (u.unit_id=d.unit_id);  # 왼쪽 department 테이블에서 가져옴. (null은 join안됨)
#               left join unit               이랑 위에랑 같음.


select d.dept_id, d.dept_name, u.unit_id, u.unit_name 
from department as d right join unit as u on (u.unit_id=d.unit_id); # 오른쪽 unit테이블에서 가져옴.

2.inner join

앞에서한 join문도 모두 (inner) join ~ on ~ 으로 inner가 앞에 생략되있음.

양쪽테이블에서 비교되는값이 일치하는 행만 가져옴!(null은 안가져옴)

 

예문들

# 직원정보조회(2014 입사한 재직자 기준)
select e.emp_name,d.dept_name
from (employee as e) inner join (department as d) on (e.dept_id=d.dept_id)
where e.hire_date between ('2014.1.1' and '2014.12.31') 
      and e.retire_date is null;
      
# 휴가정보(2014년도 재직자 기준)
select e.emp_name, v.begine_date,v.end_date,v.reason,v.duration
from (employee as e) inner join( vacation as v) on (e.emp_id=v.emp_id)
where e.hire_date between ('2014.1.1' and '2014.12.31') 
      and e.retire_date is null;
      
# 휴가정보(2017 상반기 재직자 기준)
select v.emp_id,e.emp_name,sum(v.duration) as 휴가기간
from (vacation as v) join (employee as e) on (v.emp_id=e.emp_id)
where v.begin_date between '2017.01.01' and '2017.6.30'
group by v.emp_id,e.emp_name
order by sum(duration) DESC;

3. outer join

 

left outer join

right outer join

full outer join  (단, mysql은 이거없음)

비교되는 값이 일치하지 않는 행도 가져옴(null도 가져옴)

 

-- 4월 입사직원의 휴가정보 조회(휴가가든 안가든 조회)   # null값 포함

select e.emp_id, e.emp_name, v.duration

from (employee as e ) left outer join (vacation as v) on (e.emp_id=v.emp_id)

where e.hire_date between ('2016.04.01' and '2016.04.30')

and retire_date is null;

 

-- (비교) 4월 입사직원의 휴가정보 조회(휴가안간직원 조회안됨) #null값 포함X

select e.emp_id,e.emp_name,v.duration

from (employee as e) inner join (vacation as v) on (e.emp_id=v.emp_id)

where e.hire_date between ('2016.04.01' and '2016.04.30')

and retire_date is null;

-- 여러테이블 조회

select e.emp_id,      d.dept_id,       u.unit_id,      v.begin_date

from (employee as e) inner join (department as d) on (e.dept_id=d.dept_id) 

                     left outer join (unit as u )on (d.unit_id=u.unit_id)

                     inner join (vacation as v) on (e.emp_id=v.emp_id)

where v.begin_date between '2017.1.1' and '2017.3.31'  

order by e.emp_id;                                               # asc로 정렬됨.

4.cross join 

 

실무에선 사용잘 안됨

on 절이 없어 모든경우의수만큼 결과 행얻음(대량의 test데이터 만들때 많이씀)

 

 

-- 직원과 부서간 cross join

SELECT emp_name, dept_name
    FROM employee AS e
    CROSS JOIN department AS d;

-- 여러연습문제들

#.나쁜예(중간에 정보바뀔수있음)

select * from employee

where hire_date='2013.1.1';



#나쁜예2(입사날짜 같은사람이 생기면 한명만나옴)

select * from employee

order by hire_date asc limit 1;



#좋은예(입사날짜 같은사람이 여러명이여도 나옴)

select * from employee

where hire_date=(select min(hire_date) 

                        from employee);

5.하위쿼리

괄호안에 또다른 쿼리문있는 쿼리문

대부분 join문 작성해서 같은결과 얻음(join 문 보다 쉬움)

 

 

-- 급여가장 많이받는 직원

select * from employee

where salary=(select max(salary)

                  from employee);

-- 가장 먼저 입사한 직원

select * from employee 

where hire_date=(select min(hire_date)

                       from employee);

-- 주의점

select * from employee

where dpet_id in (select dept_id

                       from department

                       where unit_id='B');             #1:N 비교에서는 =아닌 in을 사용!

#where dept_id =(select~~)    이거는 오류

-- 휴가간적있는 SYS 직원 조회

select * from employee

where dept_id='SYS'

        and emp_id in( select emp_id 

                             from vacation);

#and emp_id not in(select~)    휴가간적없는 sys직원조회