MySQL(5) - SELECT 조인(내부 조인, 외부 조인)

<목차>

1) SELECT 조인

   1-1) 내부 조인

      1-1-1) 등가 조인

      1-1-2) 비등가 조인

      1-1-3) 자체 조인

   1-2) 외부 조인

      1-2-1) left join

      1-2-2) right join

   1-3) 예제

 

 

 

 

 

1) SELECT 조인

 

 

 

 

/*
	JOIN
	JOIN 문은 서로 연관성 있는 테이블을 결합시킬 때 사용되며
	Inner Join과 Outer Join으로 나뉜다.
	여기서 연관성이란, 두 테이블 간 일치되는 컬럼을 말한다.
	
	- 내부 조인, 외부 조인
*/

 

 

 

1-1) 내부 조인

/*
	내부 조인
	- 등가 조인, 비등가 조인, 자체 조인
*/

 

 

1-1-1) 등가 조인

 

-- 등가 조인
-- where

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

 

 

 

-- Inner Join
select
	e.employee_id, e.department_id, d.department_name
from
	employees e
	inner join departments d    -- 여기서 "inner"는 생략 가능함!
	on e.department_id = d.department_id;

 

 

 

 

1-1-2) 비등가 조인

 

-- 비등가 조인
create table salarygrade (  -- 테이블 생성
	grade      varchar(10),
	losalary   int,
	hisalary   int
);

drop table salarygrade;  -- 테이블 삭제

select * from salarygrade;

-- 여러 sql 실행 단축키 : alt + x
insert into salarygrade values('A', 20000, 99999);
insert into salarygrade values('B', 12000, 19999);
insert into salarygrade values('C', 8000, 11999);
insert into salarygrade values('D', 4000, 7999);
insert into salarygrade values('E', 0, 3999);

 

 

 

-- 비등가 조인
select * from salarygrade s;
select
	employee_id, salary, grade
from
	employees, salarygrade
where 
	salary between losalary and hisalary;

 

 

 

 

1-1-3) 자체 조인

 

-- 자체 조인
select
	e1.employee_id, e1.last_name, e1.manager_id,
	e2.employee_id as 'mgr',
	e2.last_name as 'mgr_name'
from
	employees e1, employees e2
where 
	e1.manager_id = e2.employee_id;

 

 

 

 

1-2) 외부 조인

/*
	외부 조인
	- left join, right join
*/

 

 

1-2-1) left join

 

-- left join
select 
	e.employee_id, d.department_id, d.department_name
from
	employees e left join departments d   -- 여기서 "outer"는 생략 가능함
	on e.department_id = d.department_id
;

 

 

 

-- 전 사원의 사원번호와 사원이름, 부서번호와 부서이름
-- 도시이름과 국가코드를 조회하겠다.
select * from employees e;
select * from departments d;
select * from locations l;

select 
	e.employee_id, e.last_name,
	d.department_id, d.department_name,
	l.location_id, l.city, l.country_id
from
	employees e left join departments d
		on e.department_id = d.department_id
	left join locations l
		on d.location_id = l.location_id;

 

 

 

 

1-2-2) right join

 

-- right(outer) join
select
	e1.employee_id, e1.last_name, e1.manager_id,
	e2.employee_id as 'mgr',
	e2.last_name as 'mgr_name'
from
	employees e1
	right outer join   -- 여기서 "outer"는 생략 가능함
	employees e2
	on e1.manager_id = e2.employee_id
;

 

 

 

select 
	e.employee_id, d.department_id, d.department_name
from
	employees e right join departments d   -- 여기서 "outer"는 생략 가능함
	on e.department_id = d.department_id
;

 

 

 

 

+) full outer join

-- full outer join : MySQL에서 지원 안 함!(따라서 바로 아래 코드는 실행 안 되고, 에러 발생함!)
select
	e1.employee_id, e1.last_name, e1.manager_id,
	e2.employee_id as 'mgr',
	e2.last_name as 'mgr_name'
from 
	employees e1 full outer join employees e2
	on e1.manager_id = e2.employee_id
;


-- 간접적으로 구현하는 방법
-- left join 테이블과 right join 테이블을 합집합시킴!
select
	e1.employee_id, e1.last_name, e1.manager_id,
	e2.employee_id as 'mgr',
	e2.last_name as 'mgr_name'
from 
	employees e1 left join employees e2
	on e1.manager_id = e2.employee_id
union 
select
	e1.employee_id, e1.last_name, e1.manager_id,
	e2.employee_id as 'mgr',
	e2.last_name as 'mgr_name'
from 
	employees e1 right join employees e2
	on e1.manager_id = e2.employee_id
;

 

 

 

 

 

1-3) 예제

/*
	문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
	문제2) 30번 부서의 사원들의 이름, 직업, 부서명을 출력하라
	문제3) 커미션을 받는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
	문제4) 지역번호 2500에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
	문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
	문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
	문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
	문제8) 급여가 3000에서 5000사이인 사원의 이름과 소속 부서명을 출력하라
	문제9) ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력하라
	문제10) 급여가 3000 이하인 사원의 이름과 급여, 근무지를 출력하라
*/

 

-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.department_id,
	d.department_name
from employees e, departments d
where e.department_id = d.department_id;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.department_id,
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
;

-- 교수님 version
select
	e.first_name,
	e.department_id,
	d.department_name
from employees e, departments d
where e.department_id = d.department_id;

 

 

 

-- 문제2) 30번 부서의 사원들의 이름, 직업, 부서명을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.job_id,
	d.department_name
from employees e, departments d
where
	e.department_id = d.department_id
	and
	e.department_id = 30
;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.job_id,
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	e.department_id = 30
;

-- 교수님 version
select 
	e.first_name,
	e.job_id,
	d.department_name
from employees e join departments d
	on e.department_id = d.department_id
where 
	e.department_id = 30
;

 

 

 

-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
-- left join version
select 
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.job_id,
	e.department_id,
	d.department_name
from
	employees e
	left join departments d
	on e.department_id = d.department_id
where 
	e.commission_pct is not null
;

-- 교수님 version
select 
	e.first_name,
	e.job_id,
	e.department_id,
	d.department_name
from
	employees e left join departments d
	on e.department_id = d.department_id
where 
	e.commission_pct is not null
;

 

 

 

-- 문제4) 지역번호 2500에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.job_id,
	e.department_id,
	d.department_name
from employees e, departments d
where
	e.department_id = d.department_id
	and
	d.location_id = 2500
;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.job_id,
	e.department_id,
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	d.location_id = 2500
;

-- 교수님 version
select
	e.first_name,
	e.job_id,
	e.department_id,
	d.department_name
from employees e, departments d
where 
	e.department_id = d.department_id
	and
	d.location_id = 2500
;

 

 

 

-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	d.department_name
from employees e, departments d
where 
	e.department_id = d.department_id
	and
	concat(e.first_name, ' ', e.last_name) like '%A%'
;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	concat(e.first_name, ' ', e.last_name) like '%A%'
;

-- 교수님 version
select
	e.first_name,
	d.department_name
from
	employees e
	join departments d
	on e.department_id = d.department_id
where 
	e.first_name like '%A%'
;

 

 

 

-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
select
	concat(e1.first_name, ' ', e1.last_name) as 'Full Name',
	e2.last_name as 'mgr_name'
from
	employees e1, employees e2
where 
	e1.manager_id = e2.employee_id
;

-- 교수님 version
select
	e1.first_name,
	e2.first_name as 'mgr_name'
from
	employees e1, employees e2
where 
	e1.manager_id = e2.employee_id
;

 

 

 

-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
select 
	d.department_name,
	e.salary
from employees e, departments d
where 
	e.department_id = d.department_id
	and
	e.salary >= 3000
;

-- inner join version
select
	d.department_name,
	e.salary
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	e.salary >= 3000
;

-- 교수님 version
select
	e.first_name,
	d.department_name,
	e.salary
from
	employees e
	left join departments d
	on e.department_id = d.department_id
where 
	e.salary >= 3000
;

 

 

 

-- 문제8) 급여가 3000에서 5000사이인 사원의 이름과 소속 부서명을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	d.department_name
from employees e, departments d
where
	e.department_id = d.department_id
	and
	e.salary between 3000 and 5000
;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	e.salary between 3000 and 5000
;

-- 교수님 version
select
	e.first_name,
	d.department_name
from employees e, departments d
where
	e.department_id = d.department_id
	and
	e.salary between 3000 and 5000
;

 

 

 

-- 문제9) ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력하라
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.hire_date,
	d.department_name
from employees e, departments d
where 
	e.department_id = d.department_id
	and
	d.department_name = 'Accounting'
;

-- inner join version
select
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.hire_date,
	d.department_name
from
	employees e
	inner join departments d
	on e.department_id = d.department_id
where 
	d.department_name = 'Accounting'
;

-- 교수님 version
select
	e.first_name,
	e.hire_date
from
	employees e
	join departments d
	on e.department_id = d.department_id
where 
	d.department_name = 'Accounting'
;

 

 

 

-- 문제10) 급여가 3000 이하인 사원의 이름과 급여, 근무지를 출력하라
-- left join version
select 
	concat(e.first_name, ' ', e.last_name) as 'Full Name',
	e.salary,
	l.city
from
	employees e left join departments d
		on e.department_id = d.department_id
	left join locations l
		on d.location_id = l.location_id
where 
	e.salary <= 3000
;

-- 교수님 version
select 
	e.first_name,
	e.salary,
	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.salary <= 3000
;

 

 

 

 

+) 추가 예제

-- 문제11)
-- 사원별로 급여에 급여 * commission 비율을 더한 금액을
-- 모든 사원에게 지급하려고 한다. (= 지급 금액)
-- 사번(employee_id), 부서이름(department_name),
-- 급여(salary), commission 비율(commission_pct),
-- 지급금액을 사번 순으로 출력하라.
-- (참조 테이블 : employees, departments)

-- 교수님 version
select
	e.employee_id,
	d.department_name,
	e.salary,
	ifnull(e.commission_pct, 0) as 'commission_pct',
	e.salary + (e.salary * ifnull(e.commission_pct, 0)) as '지급 금액'
from 
	employees e left join departments d
	on e.department_id = d.department_id
order by e.employee_id;

 

 

 

-- 문제12) 
-- 모든 사원의 job 이력을 알고 싶다.
-- 사번(employee_id), last_name, job 이력의 횟수,
-- job 이력이 한 번도 없으면 'zero', 한 번 있으면 'one',
-- 두 번 있으면 'two', 나머지는 'many'라고 등급을 정렬하여 출력하라.
-- (참조 테이블 : employees, job_history)

-- 교수님 version
select 
	e.employee_id,
	e.last_name,
	count(jh.start_date) as 'job이력 횟수',
	(
		case count(jh.start_date)
			when 0 then 'zero'
			when 1 then 'one'
			when 2 then 'two'
			else 'many'
		end 
	) as 'job이력'
from
	employees e left join job_history jh
	on e.employee_id = jh.employee_id
group by e.employee_id, e.last_name
order by `job이력` desc;