현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.

 Mir의 운영환경


지금까지 하나의 테이블에 대한 SQL 명령어를 살펴보았다.
대충 대충 자주쓰는 명령어만 배웠는데도 머리속이 복잡하다.
물론 명령어를 100%외우면 좋지만 정말 모든 명령어를 100%외우기란 몇몇 천재들 빼고는 불가능할것이다.
필자는 포스트쓰면서 몇번이고 반복하는데도 계속 까먹는다.;;
하지만 어떤 기능을 가진 무엇인가 있다는것은 기억하기 때문에 필요하면 찾기라도 한다.
필자의 포스트를 읽고 있는사람도 그정도는 되어야 한다! 
그럼 이제부터 한개이상의 테이블을 사용하여 데이터를 조회해보는 방법을 배워보자.

아래 두개의 테이블이 있다.
하나는 우리가 계속 사용했던 사원(emp)테이블 


그리고 나머지 하나는 몇번 본적은 있을듯한 부서(dept)테이블

이 두가지 테이블을 가지고 사원에따른 부서명을 알아보자.
사원(emp)테이블에는 사원의 이름이 있고 부서코드가 있지만 소속부서명은 모른다.
부서(dept)테이블을 살펴보면 부서코드와 그 부서코드에 따른 부서명이 저장되어 있다.

자 그럼 SMITH의 부서명은 무엇일까?
사원 테이블에서 SMITH의 부서코드를 찾으면 20이라는 부서코드를 얻을수 있다.
그럼 부서테이블에서 다시 20이라는 부서코드를 가지고 부서명을 찾으면 RESEARCH라는 부서명을 얻을수 있다.
눈으로 보지말고 데이터베이스를 이용해서 찾아보도록 하자.
select * from emp where ename='SMITH';

SMITH의 사원정보를 찾아보았다. DEPTNO는 20이다.

select * from dept where deptno=20;

DEPTNO 20에대한 부서정보를 찾아보았다.
부서코드가 20일때 부서이름은 RESEARCH이며 DALLAS에 위치해 있다는것을 알수 있다.
그러면 SMITH의 부서는 RESEARCH이라는 결과를 낼수 있다.

우리가 여태까지 배운 명령어로는 한개의 테이블에서 조회를 하는 명령어였기 때문에
이렇게 한개이상의 테이블을 검색할려면 두번의 명령어를 사용해야된다.
그러면 이것을 한번의 명령어로 줄이는 방법을 알아보자. 



EQUI JOIN 
- 값을 일치시켜 두개의 테이블을 연걸하는 기본 조인방법. 
EQUI JOIN은 조인 대상이 되는 두 테이블에서 공통적인 컬럼의 값을 서로 일치시켜 두개의 테이블을 연결하는 방법이다.
위의 사원테이블과 부서테이블을 잘살펴보면 공통적으로 들어가는 컬럼값이 있다.
바로 부서번호이다. 이 부서번호를 기준으로 두개의 테이블을 연결할수 있는것이다.
아래 명령어를 살펴보자.

1. select *
2. from emp, dept 
3. where emp.deptno = dept.deptno;


1. 모든 컬럼을 선택하였다.

2. from(어디서 값을 가져오느냐?) 두테이블의 정보를 모두 가져와야되기 때문에 
 
emp테이블과 dept테이블을 동시에 기술하였다.

3. where절에서는 조건을 명시한다. 두테이블의 모든 값을 가져오는데 
emp.deptno(emp테이블의 deptno컬럼은) = (같다) dept.deptno(dept테이블의 deptno컬럼과)

두 컬럼값이 같으면 출력하라는 말이다.

출력해보자.

deptno의 값을 기준으로  emp테이블과 dept테이블이 함께 출력이 된것이 확인된다.

조인은 따로 명령어가 있는것이 아니라 위의 명령어처럼 from절에 한개이상의 테이블을 호출한뒤
where절에서 같은 컬럼을 출력하라고 조건을 주면된다.
그러면 emp테이블의 1행~14행과 dept테이블의 1행~5행을 각각 다 비교하게 된다.
이때 emp테이블의 deptno가 10이면 dept테이블의 deptno가 10일때 출력하게 되는것이다.
( where emp.deptno(10) = dept.deptno(10) 조건을 만족시킨다.)

여기서 주의할점은 정확한 컬럼을 지정해주어야 한다.

만약 where절에서 deptno = deptno 라고 명시해주었을때 오라클에서는 deptno를 찾을때
앞에 있는 deptno가 어디의 deptno인지 헷갈리게 된다.
결국 오라클은 애매하다는 말을 던져주고 명령을 듣지 않게 된다.

그래서 꼭 컬럼명앞에 테이블 이름을 기술하여 "emp테이블의 deptno이다"  "dept테이블의 deptno이다"
라고 정확하게 말해주도록 하자.

자 그럼 두개의 테이블을 조인시켜서 사원이름(ename)과 부서명(dname)을 같이 출력해보자.
select ename, dname
from emp, dept
where emp.deptno = dept.deptno;

처음 select를 할때 서로다른 두테이블에 있는 컬럼을 하나씩 select하였다. (ENAME과 DNAME)
그리고 난뒤 emp와 dept를 호출하였고 where절에서 조인을 시켜주었더니
서로다른 테이블에 있는 ename와 dname가 같이 출력할수 있게 되었다.

이번엔 두테이블을 조인시켜서 SMITH의 부서명을 찾아보자.
원래 SMITH만을 출력시킬때에는 where절에서 ename='SMITH'라는 조건을 주어 
이름이 SMITH인 사람만 출력시키게 되는데 현재 조인을 해야되기 때문에 where절을 사용하고 있다.
이때에 AND를 사용하여 검색조건을 추가하면된다.
select ename, dname
from emp, dept
where emp.deptno = dept.deptno
AND emp.ename='SMITH';

AND는 앞의 조건과 뒤의 조건을 둘다 만족해야지만 출력시키는 논리연산자인데(앞에서 배웠다!!!)
조인을 하는 조건(두개의 공통된 컬럼중에) AND 뒤의 검색조건(이름이 SMITH인 사람)
두개다 만족시키는 값만 출력하게 된다.

NON-EQUI JOIN - 값을 비교하면서 두개의 테이블을 연결하는 조인방법
위의 EQUI JOIN에서는 두개의 값을 일치시켜서 조인을 하는 방법을 배웠다. 
NON-EQUI JOIN은 값을 비교하면서 조인을 하는 방법을 말한다.

이번에 사용할 테이블은 급여등급 테이블(salgrade)이다.

간단히 살펴보면 LOSAL과 HISAL을 설정해놓고 그 사이의 등급(GRADE)를 정해놓은 테이블이다.
예를 들어 급여등급이 3등급이면 그사원의 급여는 1401~2000사이라는 것이다.

자 이 급여등급 테이블을 사용하여 사원들의 급여등급을 매겨보자.

1. select ename, grade

2. from emp, salgrade

3. where emp.sal >= salgrade.losal  and emp.sal <= salgrade.hisal;

1. ename과 grade컬럼을 선택하였다.

2. from (어디서?) emp테이블과 salgrade테이블에서 값을 가져온다.

3.where
(두테이블에서 값을 가져올때)
  emp.sal
(emp의 월급컬럼이) >= salgrade.losal and(salgrade의 losal컬럼보다 크거나)
  emp.sal(emp의 월급컬럼이) <= salgrade.hisal (salgrade의 hisal컬럼보다 작으면 값을 출력한다.)

보기 쉽게 월급까지 출력을 해보았다.

위에서 말했듯이 조인문은 특별한 명령어가 있는것이 아니다.
from절에서 두개의 테이블을 호출하고 EQUI 조인문같은경우에는 일치하는 컬럼값을 정해주었고
NON-EQUI 조인문은 범위를 정해놓았을뿐이다.

위 조건식 보면 만약 월급이 1500이면 등급1의 700과 1200사이에 들어가지 않기때문에
등급1은 출력이 되지않고 등급2도 마찬가지 1201에서 1400사이에 들어가지 않기 때문에 등급2도 출력이 안된다.
1500이면 등급3의 1401~2000사이에 들어가기 때문에 등급3이 출력되며
나머지 등급4,5의 범위에도 들어가지 않기 때문에 월급이 1500인 사람은 grade가 3으로 출력이 되는것이다.

반대로 생각하면 grade가 1일때 sal이 700과 1200사이에 있어야지만 grade 1이 출력이 될수 있다.


만약 여기서 자신의 월급이 9999가 넘으면 어떻게 될까?
그냥 출력이 안되는것이다.
조인문이 아니라고 생각하면 where절의 and로 "x이상 y이하의 값만 출력시켜라" 라고 명령을 내린거다. 
 

범위를 지정해주기때문에 and말고도 between and를 사용해서 출력해도 된다.
select ename, sal, grade
from emp, salgrade
where emp.sal between salgrade.losal and salgrade.hisal;


자 그럼 여기에서 부서테이블(dept)의 부서명까지 가져와보도록 하자.

SELECT ename, dname, sal, grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno
AND  emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

조금 어지러워보이지만 풀어보면 간단하다 SELECT절에서 DNAME까지 선택한뒤에
FROM절에서 emp, dept, salgrade 세개의 테이블을 호출해주었다.
emp와 dept는 EQUI 조인이기 때문에 값을 일치시켜주고 (emp.deptno = dept.deptno)
emp와 salgrade는 NON-EQUI 조인으로 비교연산자 BETWEEN을 사용하여 조인시켜주면 된다.

자 조인의 형태는 이두개가 끝이다. 값을 일치시키거나 값을 비교하거나 둘중 하나만 해주면 된다.
세상에 크기가 같지도 않고 크지도않고 작지도 않는게 있을까?  없다!!

하지만 프로그래밍상에는 있다.. (에휴..)
바로 NULL값이다. 비교할 대상자체가 없기때문에 같지도 않고 크지도 않고 작지도 않다.
참 짜증나는 녀석이다.

자 그럼 NULL값처리 배우기 전에 한가지만 더 배우고 가자.

SELF JOIN
- 한개의 테이블을 한개이상의 테이블처럼 사용하는 조인
SELF 조인은 말그대로 나를 조인한다는 뜻이다.
무슨뜻일까? 아직 이해가 잘안된다.
우선 아래 emp테이블을 보자. 

emp테이블을 보면 각 사원별로 매니저(mgr)가 있는것이 확인된다.
자 그럼 SMITH의 매니저는 누구일까?
SMITH의 mgr을 찾아보니 7902이다. mgr에 적혀 있는 숫자는 각 사원번호이다.
7902의 사원번호를 가진 사람을 찾아보니 FORD사원이다.
왠지 어디서 비슷한말을 본적있지 않은가? 
아직 모르겠다면 테이터베이스를 이용하여 SMITH의 매니저 이름을 출력해보자.

select ename, mgr from emp where ename='SMITH';

SMITH의 매니저의 사원번호는 7902이다.

select empno, ename from emp where empno=7902;

사원번호 7902는 FORD이다.

어디서 많이 본적 있지않은가? 
EQUI 조인문을 배울때 다른 두개의 테이블로부터 정보를 가져올때 두번의 명령을 사용했엇다.
그런데 이번엔 분명 하나의 테이블에 있는데 SMITH의 매니저 이름을 출력시킬려고 하니 두번의 명령을 사용하게 된다.
그럼 이것을 한번의 명령으로 나타낼려면 어떻게 해야될까? 이럴때 SELF 조인을 사용하게 되는것이다.


1. SELECT employee.ename, manager.ename

2. FROM emp employee, emp manager

3. WHERE employee.mgr = manager.empno;


1. ename과 ename 똑같은 컬럼을 선택하였다. 이름이 같으면 모호성(애매하다고 하는거)이 생기기 때문에
  하나의 ename는 employss테이블 다른 하나의 ename는 manager테이블에서 가져와라고 하였다.
  둘다 처음보는 테이블인데 이 테이블이 어디서 오는것일까? FROM절을 보면 알게 된다.

2. SELF조인의 핵심이다.  
  두개의 emp테이블을 호출하여 각각 employss와 manager이라는 별칭을 달아주었다.
  이렇게 별칭을 달아주면 emp테이블은 두개의 이름을 가지게 된다.
  생성된 두개의 이름을 가지고 각각 다른테이블처럼 사용할수 있는데 

  이것이 바로 하나의 테이블을 가지고 하나이상의 테이블처럼 사용하는방법이다.
 

3. employss와 manager은 공통된 컬럼명을 가지고 있기때문에 위치를 확실하게 해주어야 한다.
  나머지는 EQUI조인과 같은 형식이다. 공통되는 값을 일치시켜주면 된다.


조금만 넓게 보면 EQUI조인문과 다른것이없다.
복잡하게 생각하지 말자! 그냥 테이블에 별칭을 달아서 사용한다는것뿐이다.
다만 그 별칭들이 어떤 테이블을 가리킬때 하나의 공통된 테이블을 가리키고 있으면 SELF조인이라고 하는것이다.

자 마지막으로 NULL값 처리에대해 알아보자.

OUTER JOIN

위 SELF조인 예제에서 보면 emp테이블은 14개의 테이블을 가지고 있는데 13개의 행이 선택되었다고 한다.
누가 빠졌는지 한번 찾아보자. 

KING은 회사의 사장(PRESIDENT)이여서 매니저가 NULL값으로 저장되어있는것이 확인된다.
조인조건이 empolyee.mgr = manager.empno 였는데 사원번호가 NULL인 사원이 없으므로
KING은 조인조건에 만족하지 못하고 출력이 되지 않았다.

그럼 KING를 출력시킬려면 어떻게 해야될까? 이럴때에 외부조인을 사용해야된다.
외부조인은 " (+) " 연산자를 사용하여 NULL값으로 인해 배제된 행을 결과에 포함시키는 방법이다.
아래 예제를 보자.

select employee.ename, manager.ename
from emp employee, emp manager
where employee.mgr = manager.empno(+) 

위 외부조인(+)를 빼면 EQUI조인문장과 똑같다. (SELF조인도 크게보면 EQUI조인에 속한다고 했었다.)
EQUI조인은 한가지 제약점이 있는데 조인을 생성하려 하는 두 개의 테이블에 속한 두개의 컬럼에서
공통된 값이 없으면 출력이 되지 않는다.
(where절에서 A = X인 결과만 출력시켜라고 하였을때  A=X가 아닌것들은 출력 시키지 않는거와 같은거다.)

이럴때 OUTER 조인 (+)연산자를 사용하는데 조인시킬 값이 없는 측에 (+)를 위치 시킨다.

위 예제를 보면 mepolyee.mgr에는 null값을 가지고 있지만 manager.empno는 null값이 없다.
그래서  A = X의 조건을 만족시키지 못하기때문에 출력이 되지 않는다.

하지만 (+)연산자를 manager.empno에 위치시킴으로 null값이 없지만 출력을 시켜주는것이다.


반대로 mepolyee.mgr에 (+)연산자를 위치시키면 어떻게 될까?
empno에 있는 사원번호를 보면 14개의 사원번호가 있다. 하지만 mgr은 중복된 사원번호를 빼면 6종류 밖에 없다.
그럼 manager.empno에 있는 사원번호가 mepolyee.mgr에 없기 때문에  A = X의 조건을 만족시키지 못한다.
그래서 위 예제를 출력시킬때 6명의 매니저만 출력시키고 나머지는 출력이 되지 않는다.
이때 (+)연산자를 mepolyee.mgr에 위치시키면 나머지 사원번호를 mgr이 가지고 있지 않아도 출력을 시켜주는것이다.
select employee.ename, manager.ename
from emp employee, emp manager
where employee.mgr(+) = manager.empno




한가지를 예로 더 들어보자.
부서테이블(dept)에 보면 부서번호가 10부터 40까지 있다.
부서테이블(dept)과 사원테이블(emp)을 이용하여 부서번호에 따른 사원이름을 출력시키되
부서번호가 40까지 모두 출력되게 만들어보자.
(각자 해보고 난뒤에 아래 예제를 보자)







자 부서테이블에는 부서번호가 10부터 40까지 있는데 사원테이블에는 부서번호가 30까지 밖에 없다.
A = X를 만족시키지 못하는것이다.
그럼 부서번호 40을 출력시킬려면 (+)연산자를 어디에 위치시켜야 될까?
40이라는 부서번호가 없는 테이블(값이 없는 테이블)에 위치시켜야지 된다.
select d.deptno, e.ename
from dept d, emp e
where d.deptno = e.deptno(+)

(별칭을 한글자로 정해주어 더욱더 편하게 사용할수도 있다.)


확실히 이해가 되었는지 모르지만 후에 몇번 공식을 적용하다보면 이해가 될것이다!
(필자는 이것을 글로설명하기 너무 힘들었다;;;)





자 그럼 마무리로 문제를 풀어보자!

------------------------------------------------------------------------------------------


1. 사원 이름이 SCOTT인 사원의 사번(empno), 이름(ename), 부서명(dname)를 출력하세요.

2. 사원이름과 급여(sal)와 급여등급(grade)을 출력하세요.

3. 위 2번문제에서 부서명을 추가시켜 출력하세요.

4. 사원이름과 매니저의 이름을 아래와 같은 형식으로 출력하세요.
     "XXX"의 매니져는 "XXX" 입니다. 

5. 부서번호가 30번인 사원들의 이름, 직급(job), 부서번호(deptno), 부서위치(loc)를 출력하세요.

6. 보너스(comm)을 받은사원의 이름, 보너스, 부서명, 부서위치를 출력하세요.

7. DALLAS에서 근무하는 사원들의 이름, 직급, 부서번호, 부서명을 출력하세요.

8. 이름에 'A'가 들어가는 사원들의 이름과 부서명을 출력하세요.

9. 사원이름, 부서번호와 해당사원과 같은 부서에 있는 사원을 출력하세요.
   (해당사원과 같은부서에 있는 사원의 해딩을 '동료'라고 설정해주세요)



------------------------------------------------------------------------------------------


1. 사원 이름이 SCOTT인 사원의 사번(empno), 이름(ename), 부서명(dname)를 출력하세요.
select empno, ename, dname
from emp e, dept d
where e.deptno = d.deptno;



2. 사원이름과 급여(sal)와 급여등급(grade)을 출력하세요.
select sal, grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;



3. 위 2번문제에서 사원 이름과 부서명을 추가시켜 출력하세요.
select sal, grade, ename, dname
from emp e, salgrade s, dept d
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;



4. 사원이름과 매니저의 이름을 아래와 같은 형식으로 출력하세요.
"XXX"의 매니져는 "XXX" 입니다.
select e.ename || '의 매니져는' || m.ename || '입니다.'
from emp e, emp m
where e.mgr = m.empno;



5. 부서번호가 30번인 사원들의 이름, 직급(job), 부서번호(deptno), 부서위치(loc)를 출력하세요.
select e.ename, e.job, d.deptno, d.loc
from emp e, dept d
where e.deptno = d.deptno;

 

 

 




6. 보너스(comm)을 받은사원의 이름, 보너스, 부서명, 부서위치를 출력하세요.
select e.ename, e.comm, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and e.comm > 0;     (혹은 comm =! 0 )


7. DALLAS에서 근무하는 사원들의 이름, 직급, 부서번호, 부서명을 출력하세요.
select e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
and d.loc = 'DALLAS';


8. 이름에 'A'가 들어가는 사원들의 이름과 부서명을 출력하세요.
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.ename like '%A%';


9. SMITH의 사원이름, 부서번호와 SMITH과 같은 부서에 있는 사원을 출력하세요.
(해당사원과 같은부서에 있는 사원의 해딩을 '동료'라고 설정해주세요)/
select e.ename, e.deptno, i.ename "동료"
from emp e, emp i
where e.deptno = i.deptno
and e.ename = 'SMITH';


 


자 이번Chapter에서 한개이상의 테이블을 연결시켜 검색을 하게 하는 JOIN문을 살펴보았다.
앞으로  한개이상의 테이블을 이용하여 계속 검색을 해야되니 조인의 개념을 확실하게 잡고 난뒤 넘어가도록 하자.

+ Recent posts