Join & Subquery

데이터 베이스의 꽃은 뭘까?

"JOIN"

둘 이상의 테이블에서 데이터가 필요한 경우 테이블 조인이 필요

일반적으로 조인 조건을 포함하는 where절을 작성해야함 

조인 조건은 일반적으로 각 테이블의 PK, FK로 구성

 

 

INNER JOIN

OUTER JOIN

  LEFT OUTER JOIN

  RIGHT OUTER JOIN

 

JOIN조건의 명시에 따른 구분 

  NATURAL JOIN

  CROSS JOIN(FULL JOIN, CARTESIAN JOIN)

 

INNER JOIN : 어느 테이블을 먼저 읽어도 결과가 달라지지 않아 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다.

OUTER JOIN : 반드시 outer 가 되는 테이블을 먼저 읽어야하므로 옵티마이저가 조인 순서를 선택할 수 없다.

 

use ssafydb;
-- 사번이 100인 사원의 사번, 이름, 급여, 부서이름
select employee_id, first_name, salary
from employees 
where employee_id = 100;

select department_name
from departments
where department_id = 90;
-- 사번이 100인 사원의 사번, 이름, 급여, 부서이름
select employee_id, first_name, salary, department_name
from employees, departments 
where employee_id = 100;
-- 1개가 나와야하는데 27개가 나와버림 => cartesian product 1 * 27

select employees.employee_id, employees.first_name, employees.salary, departments.department_name
from employees, departments 
where employees.department_id = departments.department_id
and employee_id = 100;
-- 너무 길다.



select count(*)
from departments;

-- alias 사용
select e.employee_id, e.first_name, e.salary, d.department_name
from employees e, departments d 
where e.department_id = d.department_id
and employee_id = 100;


-- inner join
select e.employee_id, e.first_name, e.salary, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id -- 조인 조건   
where employee_id = 100;

select e.employee_id, e.first_name, e.salary, d.department_name
from employees e join departments d
on e.department_id = d.department_id -- 조인 조건   
where employee_id = 100;

-- Seattle에서 근무하는 사원의 사번, 이름, 부서이름, 주소

select e.employee_id, e.first_name, d.department_name, l.street_address
from employees e join departments d join locations l
on e.department_id = d.department_id -- 조인 조건  
and d.location_id = l.location_id
where l.city = 'Seattle';

select e.employee_id, e.first_name, d.department_name, l.street_address
from employees e , departments d , locations l
where e.department_id = d.department_id -- 조인 조건  
and d.location_id = l.location_id
and l.city = 'Seattle';


-- 일반적으로 join on join on
select e.employee_id, e.first_name, d.department_name, l.street_address
from employees e join departments d 
on e.department_id = d.department_id -- 조인 조건  
join locations l
on d.location_id = l.location_id
where l.city = 'Seattle';


select * 
from locations 
where city = 'Seattle';

select *
from employees
where department_id is null;

select *
from departments; 

-- using
select e.employee_id, e.first_name, d.department_name, l.street_address
from employees e join departments d 
using (department_id) -- 조인 조건 양쪽 공통 컬럼을 사용하므로 alias 사용할 수 없음
join locations l
using (location_id)
where l.city = 'Seattle';

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

-- natural join
select e.employee_id, e.first_name, e.salary, d.department_name
from employees e natural join departments d
-- using (department_id)-- 조인 조건 같은 행이 2개 2개행이 모두 같은것으로 처리
where employee_id = 100; 

select *
from employees;

select * 
from departments;


-- 부서번호가 10인 부서의 부서번호, 부서이름, 도시
select d.department_id, d.department_name, l.city 
from departments d NATURAL JOIN locations l
where d.department_id = 10;


-- 회사에 근무하는 모든 사원의 사번, 이름, 부서이름
-- 회사에 근무하는 사원수 
-- 107명
select count(employee_id)
from employees;




-- 회사에 근무하는 모든 사원의 사번, 이름, 부서이름
-- 106명 >> 문제 발생..
select e.employee_id, e.first_name, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id;



-- 부서가 없는(부서번호가 null) 사원 검색
select *
from employees
where department_id is null;


-- 해결
select e.employee_id, e.first_name, d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id;


-- 회사에서 사원이 근무하는 모든 부서
-- 회사에 존재하는 모든 부서의 부서이름과 부서에서 근무하는 사원의 사번, 이름
-- 회사의 부서수 >> 27
select distinct department_id
from employees;

-- 사원이 근무하는 부서수 >> 11
select distinct department_id
from employees 
where department_id is not null;

-- 사원이 없는 부서의 정보는 출력이 않됨.
select d.department_name, e.employee_id, e.first_name
from employees e join departments d
where e.department_id = d.department_id;


-- 해결
select d.department_name, e.employee_id, e.first_name
from employees e right outer join departments d 
on e.department_id = d.department_id;


-- self join
-- 모든 사원의 사번, 이름, 매니저사번, 매니저이름
select e.employee_id 사번, e.first_name 사원이름, e.manager_id 매니저사번, m.first_name 매니저이름
from employees e join employees m
on e.manager_id = m.employee_id;

select *
from employees;



-- None-Equi join
-- 모든 사원의 사번, 이름, 급여, 급여등급
select employee_id, first_name, salary
from employees;


select *
from salgrades;

select e.employee_id, e.first_name, e.salary, s.grade
from employees e join salgrades s
on e.salary >= s.losal
and e.salary <= s.hisal;

select e.employee_id, e.first_name, e.salary, s.grade
from employees e join salgrades s
on e.salary between s.losal
and s.hisal;

-- 사번이 101인 사원의 근무 이력.
-- 근무 당시의 정보를 아래를 참고하여 출력.
-- 출력 컬럼명 : 사번e, 이름e, 부서이름d, 직급이름j, 시작일j_h, 종료일
-- 날짜의 형식은 00.00.00

-- 위의 정보를 출력 하였다면 위의 정보에 현재의 정보를 출력.
-- 현재 근무이력의 시작일은 이전 근무이력의 종료일 + 1일로 설정.
-- 종료일은 null로 설정.

select *
from job_history;

select *
from employees 
where employee_id = 101;

 

 Subquery 

비교연산자 오른쪽에 기술해야하고 반드시 괄호로 감싸져 있어야한다.

중첩 (Nested)

Where(S)

   단일 행 

   복수 행

   다중 컬럼

인라인 뷰

From (S) 

스칼라서브쿼리

S (S)

 

서브쿼리가 가능한 곳

select 

from 

where

having 

order by

insert 문의 values

update 문의 set

 

subquery사용이유 : mysql.. oracle 어떤 DB쓰느냐 및 인덱스가 있냐 없냐 어떻게 설계되어있는지에 따라 조인 vs 서브쿼리 다름

MySql의 경우 5.5 vs 5.6 가능한 join써라 5.6올라오면서도 조건을 따졌을때 join성능이 더 좋음

 

 

 

/*
join 
	N개 이상의 테이블의 데이터를 연결하여 하나의 데이터셋으로 만드는 기법 
--종류
	등호조인
		cross join
		inner join
		outer join(right, left, full)
		self join
		natural join
	비등호 조인
    
구현방법 
	traditional join (비표준)
    ansi join (표준안)
*/

use scott;
-- traditional join
-- 이름 급여 부서명
-- 2개의 테이블 필요 
select ename, sal, dname
from emp, dept
where emp.deptno = dept.deptno;

-- ansi 
select ename, sal, dname
from emp join dept on emp.deptno = dept.deptno;
-- where emp.deptno = dept.deptno;
select ename, sal, dname
from emp join dept using (deptno) ;

select count(*) from emp;
select count(*) from dept;

select * from emp;
select * from dept;

update emp set deptno = null where empno = 7839;

delete from emp where deptno is null;

-- 모든 사원의 이름 급여 부서명을 구하세요
select ename, sal, dname
from emp join dept using (deptno);

-- outer join 
select ename, sal, dname
from emp left outer join dept using (deptno)
union
select ename, sal, dname
from emp right outer join dept using (deptno);


select * from empno;


insert into emp ( empno, ename, job, hiredate, sal, comm)
value(7839, 'KING', 'PRESIDENT', '1981-11-17', 5000,3500);
-- self 
-- 이름 급여 관리자 이름 관리자 급여
select a.empno, a.ename, a.sal, b.empno, b.ename, b.sal
from emp a inner join emp b on a.mgr = b.empno
where a.empno = 7369;

-- 비등호 조인
select * from salgrade;

select a.ename, a.sal, b.grade
from emp a join salgrade b on a.sal >= b.losal and a.sal <= b.hisal ;

select * from dept;
-- 모든 사원의 이름 급여 관리자 명 부서명 급여 등급 관리자 급여등급 구하세요
select s.ename, s.sal, m.ename, d.dname, sg.grade, sg2.grade
from emp s left join emp m 
on s.mgr = m.empno
left join dept d
on s.deptno = d.deptno
left join salgrade sg
on s.sal >= sg.losal and s.sal <= sg.hisal
left join salgrade sg2
on m.sal >= sg2.losal and m.sal <= sg2.hisal;

-- 정답
select a.ename, a.sal, b.ename, c.dname, d.grade, e.grade
from emp a left outer join emp b on a.mgr = b.empno
		   left outer join dept c on a.deptno = c.deptno
		join salgrade d on a.sal between d.losal and d.hisal
        left outer join salgrade e on b.sal between e.losal and e.hisal;
 
 /*
	-- subquery
    inner query + outer query 
    
    실행 순서 : subquery -> outerquery 실행 
	위치에 따라 
		where (조건절 서브쿼리)
			단일행
            다중행
            다중컬럼
        from (인라인 뷰)
        
        column (스칼라 서브쿼리)
			단일행 
				조인하는 방식으로 사용되는 상호연관서브쿼리
                
 */
 -- 이름이 smith인 사원과 같은 부서원의 정보를 출력하세요 
 
 select deptno from emp where ename = 'SMITH';
 -- where 단일행 서브쿼리
 select *
 from emp 
 where deptno=(select deptno from emp where ename = 'SMITH');
 
 -- 20번 부서의 급여를 받는 사원의 정보를 출력하세요
select *
 from emp 
 where sal in (select sal from emp where deptno=20);
 
 -- 20번 부서의 급여보다 많이 받는 정보를 출력하세요
 select *
 from emp
 where sal > ALL (select sal from emp where deptno = 20);
 
-- 인라인 뷰
-- 이름 급여 급여합 구하세요
select ename, sal, a.s -- 집계함수와 같이쓸수 없음
from emp, (select sum(sal) s from emp) a;
 
-- 이름 급여 자신이 속한 부서의 급여합을 구하세요 
select ename, sal, a.s, b.ds
from emp join (select sum(sal) s from emp) a 
		 join (select deptno, sum(sal) ds from emp group by deptno) b on emp.deptno = b.deptno;

select deptno, sum(sal) from emp group by deptno;

-- 스칼라 서브쿼리
-- 이름 급여 급여합을 구하세요 
select ename,(select sum(sal) from emp) as sum
from emp;

-- 상호연관 서브쿼리
-- 이름 급여 자신이 속한 부서의 급여합을 구하세요
select a.ename
	,(select sum(sal) from emp) as sum
    ,(select sum(sal) from emp b where a.deptno = b.deptno) dsum
from emp a;
    
-- homework

create table emp_bk
select*from emp;

create table empdept
select empno, ename, sal, dname
from emp join dept using (deptno)
where sal >1000;

select*from empdept;

delete from empdept;

-- String sql = new String()
-- sql = 
select *
from emp
where 1
and sal >1000
and deptno = 10;


create table aa
select * from emp -- 테이블은 만들어지지만 데이터는 안들어가도록 만듬
where 1=0;

select * from empdept;

insert into empdept 
select empno, ename, sal, dname
from emp join dept using (deptno);

select* from empdept;

insert into empdept (empno, ename)
values((select empno from emp where deptno=20 and sal = 800), 1000);

update empdept set empno = (subquery), ename =(subquery)
where empno = (subquery)

delete from empdept where empno = (subquery)