03. 서브 쿼리, JOIN, SET

1. 서브 쿼리

 

 

 

서브 쿼리는 '쿼리 안에 쿼리' 라는 뜻이다.

 

서브쿼리는 사전에 추출된 내용에서 재 검색 하거나, 검색된 내용을 가상 컬럼을 만들어 추가 할 수 있다.

 

 

-- 서브 쿼리 : 쿼리 안의 쿼리
-- 하나의 쿼리문으로 받아온 데이터를 바탕으로 다른 내용을 검색할 때 사용
-- 1. 추출된 결과로 다른 내용을 검색 시 사용
-- 2. 검색된 내용을 가상의 컬럼으로 볼 때 사용

create table dept(
	deptno varchar(10) primary key
	,deptname varchar(20)
	,loc varchar(10)
);


create table emp(
	ename varchar(20)
	,job varchar(50)
	,deptno varchar(10)
	,hiredate date
);

alter table emp add constraint foreign key(deptno) references dept(deptno);

insert into dept(deptno, deptname, loc) values('1', 'sales', 'newyork');
insert into dept(deptno, deptname, loc) values('2', 'dev01', 'la');
insert into dept(deptno, deptname, loc) values('3', 'personnel', 'newyork');
insert into dept(deptno, deptname, loc) values('4', 'delevery', 'boston');

select * from dept;

insert into emp(ename, job, deptno, hiredate)
	values('kim', 'manager', '1', str_to_date('16/01/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('lee', 'staff', '1', str_to_date('15/01/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('han', 'staff', '1', str_to_date('16/03/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('kim', 'assistant', '1', str_to_date('15/09/22', '%Y/%m/%d'));


insert into emp(ename, job, deptno, hiredate)
	values('ahn', 'staff', '2', str_to_date('15/11/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('hwang', 'manager', '2', str_to_date('15/08/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('cha', 'assistant', '2', str_to_date('12/03/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('hong', 'staff', '2', str_to_date('14/08/02', '%Y/%m/%d'));
insert into emp(ename, job, deptno, hiredate)
	values('gang', 'staff', '2', str_to_date('10/01/02', '%Y/%m/%d'));

insert into emp(ename, job, deptno, hiredate)
	values('name', 'leader', '4', str_to_date('10/01/02', '%Y/%m/%d'));

select * from emp;

-- 문제1 > 'han' 의 근무 부서명은?
-- emp 테이블에서 han 의 deptno 를 알아낸다.
select deptno from emp where ename = 'han';
-- deptno 를 통해 dept 의 deptname 을 알아낸다.
select deptname from dept where deptno = '1';
-- 해결
select deptname, loc from dept where deptno = (select deptno from emp where ename = 'han');

-- 문제2 > 부서 위치가 'la' 또는 'boston' 인 부서 소속의 사람들의 이름, 직책은?
select ename, job from emp where deptno in (select deptno from dept where loc in('la', 'boston'));

-- 문제3 > sales 부서에 근무하는 사원 데이터(ename, job, hiredate) 가져오기
select deptno from dept where deptname = 'sales';
select ename, job, hiredate from emp where deptno = '1';
select ename, job, hiredate from emp where deptno = (select deptno from dept where deptname = 'sales');

-- 문제4 > 직책(job)이 manager 인 사원들(여러명 일 경우 빠른 직원 기준)보다 입사일이 빠른 직원(ename, job, hiredate)은?
select * from emp order by hiredate asc;
select * from emp where job = 'manager' order by hiredate asc limit 1;
select ename, job, hiredate from emp
	where hiredate < (select min(hiredate) from emp where job = 'manager') order by hiredate;

-- 문제5 > 부서별(deptno, deptname)로 직원이 몇명인지 확인
select * from dept; -- 1, 2, 3, 4
select count(deptno) from emp where deptno = 1; -- 4
select count(deptno) from emp where deptno = 2; -- 5
select count(deptno) from emp where deptno = 4; -- 1

select deptno, deptname from dept where deptno = 1;
select deptno, deptname from dept where deptno = 2;
select deptno, deptname from dept where deptno = 4;

-- 상하 관계 쿼리
-- 서브쿼리의 결과물이 본 쿼리의 일부로 사용될 경우
-- 서브쿼리의 내용이 메인 쿼리의 일부가 되는 것
-- 컬럼, 테이블로 활용이 된다.
select deptno, deptname, (select count(deptno) from emp where deptno = 1) as cnt from dept where deptno = 1;
select deptno, deptname, (select count(deptno) from emp where deptno = 2) as cnt from dept where deptno = 2;
select deptno, deptname, (select count(deptno) from emp where deptno = 4) as cnt from dept where deptno = 4;

-- 해결 >
select deptno, deptname, (select count(deptno) from emp where deptno = d.deptno) as cnt 
	from dept d; -- dept 'd' -> 별칭

-- group by 사용
select deptno, count(deptno) as cnt from emp group by deptno;

select deptno,
	(select deptname from dept where deptno = emp.deptno) as deptname,
	count(deptno) as cnt 
	from emp group by deptno;

 

 

 

 

 

2. JOIN

 

 

-- JOIN 이란?
-- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법 이다.
-- 두개의 테이블이 조인하기 위해서는 적어도 하나의 공통된 컬럼이 존재 해야 한다.
-- 그래서 일반적으로 부모(PK)와 자식(FK)간에 조인이 수월하게 이루어 진다.

-- JOIN 의 종류
-- CROSS JOIN, EQUI JOIN(등가조인, 내부조인)
-- NON-EQUI JOIN(거의 안쓰이기 때문에 다루지 않음)
-- SELF JOIN, OUTER JOIN

-- 0. CROSS JOIN
-- 두개의 테이블을 카다시안 곱을 수행한다.
-- emp(10) * dept(4) = 40

-- FROM [tableA] CROSS JOIN [tableB]
select e.ename,d.deptname from emp e cross join dept d;
-- cross join 은 생략 가능
select e.ename,d.deptname from emp e, dept d;

-- cross join 은 아직 정제되지 않은 순수한 조인 형태
-- 그래서 조건을 주어서 원하는 데이터를 뽑아낸다.

-- 1. Equi Join - 조인 하는 두 테이블 모두에 값이 있을 떄만 보여준다.
-- 가장 일반적으로 사용하는 조인(= 를 사용)

-- 1) 등가 조인
select e.ename,d.deptname from emp e, dept d where e.deptno = d.deptno;

insert into dept(deptno,deptname,loc)values('5','dev02','florida');
/* 양쪽에 데이터 불균형이 있을 경우
 * ename, deptname
 * (NULL), personnel
 * (NULL), dev02
 * 이런식으론 보여주지 않음
 */

-- 2) 내부 조인
-- FROM [tableA] INNER JOIN [tableB] 
-- inner 생략 가능, 조인을 위한 조건은 where 가아닌 on을 사용
select e.ename,d.deptname from emp e join dept d on e.deptno = d.deptno;
-- using 을 사용하여 조건을 줄 수 있다.
-- using(서브쿼리) 가 가능 하다.
select e.ename,d.deptname from emp e join dept d using (deptno);

-- 3) 네츄럴 조인
-- 네츄럴 조인은 두 테이블 사이 공통되는 컬럼이 있으면 자연스럽게 합쳐지게 된다.
-- 그래서 조인을 위한 조건절이 필요 없다.
-- 주의사항 : 공통되는 컬럼명은 단축명을 사용하지 않는다.(일부 DB 에서 에러 날 수 있음)
select deptno, e.ename,d.deptname from emp e natural join dept d;


-- 2. 셀프조인
-- Equi-Join 과 똑같다. 다만 두개의 테이블이 둘다 자신이라는 것만 다르다.
-- 셀프조인을 하면 두 데이터간 카다시안 곱을 수행 한다.
select a.ename,b.job from emp a, emp b where a.deptno = b.deptno;


-- 3. 외부조인 
-- [tableA] [left|right] outer join [tableB] ON 조건절
-- LEFT (왼쪽을 기준으로 더 있는 값을 보여줌)
-- RIGHT(오른쪽을 기준으로 더 있는 값을 보여줌)
-- FULL (서로 없는 값을 보여줌)
-- outer 는 생략 가능
select d.deptno, e.ename, d.deptname 
	from emp e right outer join dept d on e.deptno = d.deptno;

-- emp 에 deptno 6번을 추가
insert into emp(deptno,ename,job, hiredate)values('6','kim','assistant',str_to_date('14-06-02','%Y-%m-%d'));
-- 연계참조 무결성 제약조건에 의해서 부모(dept) 에게 없는 6번을 자식(emp)이 넣을 수 없다.
-- 그래서 부모-자식 관계를 해제 -> FK 를 삭제
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'emp';
alter table emp drop constraint emp_ibfk_1;


select * from emp;

select e.deptno, e.ename, d.deptname 
	from emp e left join dept d on e.deptno = d.deptno;

/* left join : join 을 기준으로 왼쪽 테이블의 데이터를 기준으로 보여준다.(오른쪽에 없는 내용은 null 처리) - 1,2,4,6
 * right join : join 을 기준으로 오른쪽 테이블의 데이터를 기준으로 보여준다.(왼쪽에 없는 내용은 null 처리) - 1,2,3,4,5
 * 서로 없는 내용을 보여줄수 있는 방법 : full outer join
 * */

 

 

 

 

 

4. SET

 

 

 

 

-- 다수의 테이블을 집합 연산 할 수 있다.
-- 합집합(UNION, UNION ALL), 교집합(INTERSECT), 차집합(MINUS)이 존재 한다.
-- 중복 제거를 해 주는 UNION 은 성능저하 문제로 권장하지 않는다.
-- [qry1] [UNION | UNION ALL | INTERSECT| MINUS] [qry2]

-- 1. UNION : 중복을 제거한 합집합
select deptno from emp
	union
select deptno from dept order by deptno;

-- left join 과 right join 을 union 하면 full outer join 효과를 얻을 수 있다.
select e.deptno, e.ename, d.deptname from emp e left join dept d on e.deptno = d.deptno
	union
select d.deptno, e.ename, d.deptname from emp e right join dept d on e.deptno = d.deptno;


-- 2. UNION ALL : 중복제거 없이 합집합 실행
-- UNION ALL 을 순수하게 실행한다기 보다는 정제해서 사용 한다.
select deptno from emp
	union all
select deptno from dept order by deptno;


-- 3. INTERSECT : 양 테이블에 존재하는 중복된 데이터만 확인 한다.
select deptno from emp
	intersect
select deptno from dept order by deptno;


-- 4. MINUS : 마리아디비에서는 차집합 지원하지 않는다.
select distinct deptno from emp; -- 1,2,4,6
select distinct deptno from dept; -- 1,2,3,4,5

-- emp - dept = 6
select deptno from emp where deptno not in (select deptno from dept);

-- dept - emp = 3, 5
select deptno from dept where deptno not in (select deptno from emp);

-- emp 와 dept 를 합집합한 내용을 emp 와 교집합
select deptno from emp
	union
select deptno from dept
	intersect
select deptno from emp;

-- union 을 대체할 경우?
-- 1. 일단 합친다.
-- 2. 합친 내용을 가지고 중복 제거
select distinct u.deptno from 
(select deptno from emp
	union all
select deptno from dept) u order by u.deptno asc;