SQL-11) JOIN 문법 - 1 (오라클 조인 문법)

안녕하세요 이승혁입니다.

 

이번 포스팅은 다른 테이블의 데이터를 하나의 결과로 확인하는 JOIN 문법에 대해 실습을 진행해 보도록 하겠습니다.

JOIN 문법의 오라클 조인 문법 equi, non equi, outer, self 조인에 대한 실습을 진행하겠습니다.

 

※ JOIN 문법 종류

 

1.오라클 조인 문법

- equi join       : 조인 조건에서 사용하는 연산자가 equal(=)

- non equi join : 조인 조건에서 사용하는 연산자가 equal이 아닌경우

- outer join      : equi 조인으로는 볼 수 없는 결과를 볼 때 사용하는 조인

- self join         : 자기 자신의 테이블과 조인하는 조인

 

2. 1999 ansi 조인 문법 (American National Standard Institute)

- on 절을 사용한 조인

- using 절을 사용한 조인

- left/right/full outer 조인

- cross 조인

 

※ 실습에 사용할 테이블 확인하기

 

- salgrade 테이블 생성

create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );

insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);
 
commit;

- emp , dept , salgrade 테이블 확인

 

좌측부터 차례로 emp, dept, salgrade 테이블의 모습입니다

 

ㆍ 오라클 조인 - equi join

 

    조인 조건에서 사용하는 연산자가 equal(=)연산인 문법

    where table1.column = table2.column

 

실습 1. equi join, 각 사원의 부서 위치와 이름을 확인해보기

select ename,dname,loc
from emp,dept
where emp.deptno=dept.deptno;

emp 테이블과 dept 테이블에 공통으로 존재하는 deptno 칼럼을 연결 key로 사용합니다.

emp 테이블의 ename칼럼, dept 테이블의 부서 이름, 부서위치 칼럼을 함께 출력합니다.

 

※ 연결 key로 사용한 칼럼을 함께 확인하기

다음과 같이 select 절에 연결키 칼럼을 그냥 기술하면 에러가 발생합니다.

emp 테이블의 deptno 인지, dept 테이블의 deptno인지 알 수 없기 때문입니다.

따라서 두 테이블에 공통으로 존재하는 칼럼을 사용하려면 테이블을 함께 작성합니다.

 

- 수정 후

select ename,emp.deptno,dname,loc
from emp,dept
where emp.deptno=dept.deptno;

정상적으로 부서번호, 부서이름, 부서위치를 확인할 수 있습니다.

 

※ 조인 조건을 부여하지 않는다면 ?

 

선행하는 테이블이 뒤따르는 테이블의 모든 행을 조인합니다.

조인 조건을 where절에 작성하지 않았을 때 조인 방법

select ename,emp.deptno,dname,loc
from emp,dept;

emp 14행 , dept 4 행 = 56행 출력

실습 2. 조건 추가 join, NEW YORK에 근무하는 사원의 이름, 부서명, 위치 출력해보기

select ename,dname,loc
from emp,dept
where loc='NEW YORK' and emp.deptno=dept.deptno;

실습 3. 조건 추가 join, 이름이 'S'로 시작하는 사원의 이름, 부서명, 위치 출력해보기

select ename,dname,loc
from emp,dept
where ename like 'S%' and emp.deptno = dept.deptno;

실습 4. group by, order by , join, 지역별 토탈 월급이 낮은 부서부터 차례로 출력해보기

select d.loc,sum(e.sal)
from emp e ,dept d
where d.deptno=e.deptno
group by d.loc
order by 2;

※ 테이블도 칼럼처럼 별칭을 부여해서 사용할 수 있습니다.

※ 하나의 테이블에만 존재하는 칼럼에도 앞에 테이블명을 작성해 처리 속도를 높일 수 있습니다.

 

ㆍ 오라클 조인 - non equi join

 

    조인하려는 두 테이블 사이의 연결고리 연산자가 = 아닌 경우의 조인

 

실습 5. non equi join, 해당 사원의 월급의 등급 출력해보기

select e.ename,e.sal,s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

실습 6. listagg + non equi join, 등급별 해당 사원 이름을 가로로 출력해보기

 

select s.grade,listagg(e.ename,'.') within group(order by e.ename) 등급목록
from emp e, salgrade s
where e.sal between s.losal and s.hisal
group by s.grade;

ㆍ 오라클 조인 - 2개 이상 테이블 조인하기

 

dept -------------------   emp  ------------------------- salgrade

          조인조건                              조인조건

  d.deptno = e.deptno           e.sal between s.losal and s.hisal

 

실습 7. 3개 테이블 조인, 이름(emp), 월급(emp), 부서위치(dept), 등급(salgrade) 출력해보기

select e.ename,e.sal,d.loc,s.grade
from emp e, dept d, salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

 

조인 조건이 여러개가 있어도 당황하지 말고 하나씩 차분히 생각하며 조건을 입력합니다.

 

실습 8. 구구단 2단 출력해보기

select '2x'||num1||'='||2*num1 as "2단"
from (select level num1
       from dual
       connect by level<=9);

connect by 절을 사용해 1~9가 나열되어있는 테이블을 생성해 서브쿼리로 작성합니다.

아래는 connect by 절을 사용해 만들어진 테이블입니다.

select level num1
 from dual
 connect by level<=9;

해당 테이블을 사용해 구구단 2단을 완성할 수 있습니다.

 

실습 9. 9x9까지 구구단 완성해보기

select t1.num1||'x'||t2.num2||'='||t1.num1*t2.num2 구구단
from(select level num1
       from dual
       connect by level<=9) t1,
    (select level num2
       from dual
       connect by level<=9) t2;

ㆍ 오라클 조인 - Outer join

 

     equi join으로는 볼 수 없는 결과를 볼 때 사용하는 조인 문법

     ex) dept 테이블에는 NEW YORK, DALLAS, CHICAGO,BOSTON 총 4가지의 지역이 존재한다.

          emp 테이블에는 BOSTON 사원의 정보가 없다. 

          따라서 equi 또는 non equi 조인을 통해 조인하면 BOSTON이라는 지역은 나오지 않는다.

select e.ename,d.loc
from emp e,dept d
where e.deptno = d.deptno
order by 2;

         outer join을 사용해 나오지 않은 BOSTON의 결과도 확인할 수 있다.

select e.ename,d.loc
from emp e,dept d
where e.deptno(+)=d.deptno;

outer sign은 (+) 기호로 나타낸다.

dept 테이블에는 나타낼 데이터가 추가로 있지만, emp 테이블은 비어있기 때문에 (+)가 대신해준다고 생각하며 공부했습니다.

 

실습 10. group by + sum + outer join, 모든 부서를 빠짐없이 총 월급 합계를 부서별로 출력해보기

select d.loc,nvl(sum(e.sal),0) 총월급
from dept d,emp e
where e.deptno(+)=d.deptno
group by d.loc;

※ 그럼 두개 다 outer sign을 작성하면 편하지 않을까

 

where e.deptno(+) = d.deptno(+);

-> 위의 결과는 오라클 조인 문법으로는 결과가 출력되지 않는다.

    1999 ansi 조인 문법을 사용해야 한다.

 

==>

select e.ename, d.loc

from emp e full outer join dept d

on (e.deptno = d.deptno);

-> 오라클 조인 문법과 다른점 from절 콤마 대신 full outer join

    where 절 대신 on 절을 사용한다는 것이다

 

실습 11. rank + group by + join, 부서위치, 이름, 월급, 월급에 대한 순위를 부서 위치별로 파티션해서 출력해보기

select d.loc,e.ename,e.sal,rank() over(partition by d.loc
                                        order by sal desc) 순위
from emp e, dept d
where e.deptno=d.deptno;

ㆍ 오라클 조인 - Self join

 

      자기 사진의 테이블과 조인하는 조인 문법입니다.

 

      emp 테이블에는 mgr(매니저,직속 상사 번호) 와 empno(사원 번호)가 존재합니다.

      이를 사용해 직속 상사 - 사원 의 관계를 확인할 수 있습니다.

      이러한 관계를 확인할 때 self join 문법이 사용됩니다.

 

실습 12. self join, 사원 - 직속상사 관계 확인해보기

select 사원.ename 사원,관리자.ename 관리자
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno;

다음 결과를 통해 KING은 BLAKE, CLARK, JONES 3명을 직속 후배로 두고 있음을 알 수 있습니다.

BLAKE 아래에 MARTIN,ALLEN,TURNER,JAMES,WARD 5명이 존재하고,

CLARK 아래에는 MILLER가 직속 후배로,

JONES 아래에는 FORD와 SCOTT이 직속 후배로 존재합니다.

또한 FORD와 SCOTT은 각각 직속 후배를 1명씩 데리고 있습니다.

 

※ R프로그램으로 시각화하기

 

위 결과를 R프로그램으로 시각화 하면 다음과 같이 한 눈에 확인할 수 있습니다.

 

실습 13. 관리자보다 월급이 많은 사원을 출력해보기

select 사원.ename 사원,사원.sal 사원월급,관리자.ename 관리자,관리자.sal 관리자월급
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno and 사원.sal>관리자.sal;

다음과 같은 코드를 통해 JONES사원은 후배보다 월급이 적은 것을 알 수 있습니다.

퇴사자 예측 시 활용할 수 있는 결과입니다.

 

실습 14. 관리자보다 일찍 입사한 사원 출력해보기

select 사원.ename 사원,사원.hiredate 사원입사,관리자.ename 관리자,관리자.hiredate 관리자입사
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno and 사원.hiredate<관리자.hiredate;

자신보다 늦게 입사한 사람을 상관으로 모시는 것 역시 불만 사항으로 작용할 수 있습니다.

이러한 변수를 새로 추가해 머신러닝 훈련 시 파생 데이터로 사용할 수 있습니다.

 

실습 15. 파생데이터 추가하기, 관리자가 자신보다 입사는 늦게, 월급을 많이 받으면 1, 아니면 0 으로 retire_pred 칼럼 생성해보기

select e.ename 사원,e.hiredate 사원입사,e.sal 사원월급,
       m.ename 관리자,m.hiredate 관리자입사,m.hiredate 관리자월급,
       case when e.sal<m.sal and e.hiredate<m.hiredate then 1 else 0 end retire_pred
from emp e, emp m
where e.mgr = m.empno

이번 실습은 오라클 조인 방법 equi, non equi, self, outer join 문법을 실습해 봤습니다.

오라클 조인 문법은 현업에서 자주 사용하는 문법이라고 합니다.

익숙해질 수 있도록 자주 사용해보는 것이 중요하다고 생각합니다.

 

다음 실습은 ansi join 문법을 진행해보도록 하겠습니다.

감사합니다.