SQL join종류와 테이블 복사하기

join

join은 정규화 작업시 분리시켜놨던 테이블들이 가지고있는 공통된 KEY를 가지고 합하여 한꺼번에 하나의 테이블로 출력하는 걸 말한다.

 

join의 종류

• inner join
• leftouter join
• rightouter join
• full outer join
• self join
• natural join
• cross join cross join

등이 있다.

오라클의 경우 주의해야할점은 표준 조인과 쿼리문 작성방법에 약간의 차이를 가진다.

 

 

inner join

--inner join--
select * from emp;
select * from dept;

--부서정보와 사원정보 한꺼번에 출력하기--
select e.empno, e.ename, e.job, e.sal, e.deptno, d.dname
from emp e ,dept d
where e.deptno = d.deptno;

--mysql의 경우--
select e.empno, e.ename, e.job, e.sal, e.deptno, d.dname
from emp e inner join dept d
on e.deptno = d.deptno;

<emp> <dept>

inner join의 경우 join의 조건에 성립하는 행들만 반환해준다.

오라클에서는 inner join이라고 따로 적지 않고 from절에 join할 테이블을 나열하며, join의 조건은 where절에 명시한다.

 

 

outer join

--outer join 진행할 카피 테이블 생성--
create table emp_copy
as select empno, ename, job
from emp;

select * from emp_copy;
delete emp_copy where job='CLERK';
delete emp_copy where empno between 7400 and 7500;

alter table emp_copy add hobby varchar2(100);
update emp_copy set hobby='음악듣기' ;

일단 outer join에 사용할 테이블 생성먼저 해주고..!

 

oueter join에는 left, right, full 3가지의 조인이존재한다. 

 

left outer join

--left outer join--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e, emp_copy c
where e.empno= c.empno(+);
--오라클의 경우 outer join이라고 적지 않고 (+)표시 사용--

--mysql의 경우--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e left outer join emp_copy c
on e.empno=c.empno;

outer join의 경우 inner join과는 다르게 join조건에 성립하지 않는 데이터도 함께 출력하는 방식이다. outer join앞에 적는 left는 나열된 테이블의 기준을 지정하며, left의 경우 왼쪽테이블의 값들은 join조건이 성립하지 않아도 전부 출력되며, 조건에 맞지 않는 값은 null로 표시된다.

 

mysql에서는 left outer join으로 join하며, 조건은 on절에 적는다.

오라클에서는 따로  left outer join을 적지 않고 (+)라는 기호를 사용하는데, left라면 오른쪽에 표시를, right라면 왼쪽에 표시해야한다.

 

 

right outer join

--right outer join--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e, emp_copy c
where e.empno(+)=c.empno;

--mysql의 경우--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e right outer join emp_copy c
on e.empno = c.empno;

rightouter join은 오른쪽을 기준으로 join된다. rightouter join이기때문에 (+)기호는 왼쪽에 붙인다.

 

 

fullouter join

--fullouter join--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e full outer join emp_copy c
on e.empno=c.empno;

join할 두 테이블 전체를 기준으로 join하며, 중복데이터는 삭제 후 출력된다. fullouter join은 inner join, leftouter join,right outer join의 결과를 모두 볼 수 있다.

 

 

self join

--self join--
select e.ename, e.empno, e.job, m.ename, m.mgr
from emp e, emp m
where e.empno = m.mgr;

한개의 테이블안에서 join 조건을 찾아 join하는 방식이다. 하나의 테이블을 가지고 별칭으로 구분하여 같은테이블 내에서 필요한 조합으로 join하여 결과를 출력한다.

위의 쿼리문은 하나의 emp직원테이블 정보에서 각각의 매니저의 이름을 함께 출력한 쿼리이다.

 

 

natural join

--natural join--
select empno, ename, job, sal, hobby
from emp
natural join emp_copy;
--별칭사용X, 같은 컬럼명끼리 조인하여 중복발생할 수 있음--

join조건을 따로 적지 않으며, 나열된 두 테이블에서 같은 이름을 가진 컬럼끼리 join하여 결과를 반환한다.

 

 

cross join

--cross join--
select e.empno, e.ename, e.job, e.sal, c.hobby
from emp e
cross join emp_copy c;
--카티시안 곱 발생--

join조건 생략 시 나열된 두 테이블에서 발생가능한 모든 조합으로 행을 출력한다. 그렇기때문에 카티시안 곱이 발생할 수있으며,

컬럼의 행이 많은 경우 성능저하를 초래하기도 한다.

 

---

테이블 복사하기

실무에서는 이미 만들어둔 테이블을 동일하게 복사해서 사용하거나, 그 테이블의 구조만 따로 복사해서 재사용 하는 경우가 많다.

이때 다양하게 테이블을 복사하는 방법을 알아보자.

 

1. 테이블 그대로 복사하기

--테이블 복사하기--
create table emp_copy1
as select *
from emp
; --완전히 똑같은 테이블--

테이블을 복사할때는 테이블을 생성할때와 동일하게 create table로 시작하고, as select 구문을 적어  from절에 복사할 테이블명을 적어준다. 이때 * 표시를 사용하고 다른 조건을 추가하지 않는다면 완벽하게 동일한 테이블이 생성된다. 

단 테이블을 복사하더라도 모든 제약조건까지 복사되진 않으므로 필요한 제약조건은 따로 만들어주어야 한다.

 

 

2. 필요한 행만 골라서 복사하기

create table emp_copy2
as select ename, deptno --원하는 컬럼만 지정가능--
from emp;

테이블을 복사해야하는데 모든 컬럼이 필요없다면, as select 구문  뒤에 필요한 컬럼명을 따로 적어 복사한다.

 

 

3. 테이블의 구조만 복사하기

--테이블 구조만 복사--
create table emp_copy3
as select *
from emp
where 1 <> 1 ;

테이블을 복사해야하는데 데이터는 복사하고 싶지 않을때 사용하는 방식이다. 완벽하게 똑같은 테이블을 복사하는 구문과 동일하게 적어주되, where절을 추가하여 무조건 거짓이 출력되도록 한다면, where조건에 참으로 적용되는 데이터의 값이 없기때문에 해당 테이블은 아무 데이터도 가져오지 않고, 컬럼들만 동일하게 복사된다. 

 

---

학습일기

join..진짜 헷갈리면서도 자주사용하는 기능이여서 더 열심히 익혀야한다..! 사실 코드짤때는 그렇게 어렵거나 한게 전혀없었는데 막상이걸 sqld자격증 공부할때는 직접 코드를 작성할수도 없고 출력된 결과도 머릿속에서 다 구상해야해서 이게 이렇게 헷갈렸던거였나..? 싶었던 구간이었다. 실무에서는 대부분 정규화 작업시 이행적 종속성을 없애기 위해 하나의 테이블에 전부 다 저장하는 경우는 정말 거의 없다. 즉 그만큼 join도 엄청나게 사용된다는 뜻이다..! 

테이블 복사의 경우 요긴하게 사용하는 기능이긴 했다. 학원에서 쿼리문을 짤때도, 혼자 연습한다고 테이블 만들기 귀찮을때도...ㅎ

join의 경우는 어떻게 join할건지, join조건을 어떻게 할것인지에 따라서도 결과값이 굉장히 달라지니까 이 부분을 중점으로 좀더 공부하면 될것같다..!!!