JOIN 종류 (ANSI방식 JOIN)

n  조인의 종류

ORACLE전용 조인(9i까지): Oracle DB에서만 사용

ANSI 조인(10g부터): DB종류에 상관없이 모든 DB에 사용 가능.

 

n  JOIN

두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하는 기능.

 

1.     카티션 곱 (Cartesian Product)

테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우에 발생

2.     EQUI JOIN

조인 대상 테이블에서 공통칼럼을 ‘=‘ 비교를 통해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법

 

문법)

 SELECT    table1.column, table2.column

 FROM       table1, table2

 WHERE     table1.column = table2.column;

 

Ex.학생 테이블과 부서 테이블을 EQUI JOIN 하여 학번, 이름, 학과번호, 소속학과 이름, 학과 위치를 출력하여라.

 

SELECT s.studno, s.name, s.deptno, d.dname, d.loc

   FROM  student s, department d

   WHERE s.deptno = d.deptno;

 

※ ANSI 방식

   Select e.empno, e.ename, e.sal, d.deptno, d.dname

   From emp e JOIN dept d

   ON (e.deptno=d.deptno);

 

3.     NON-EQUI JOIN

조인 조건에서 ‘<‘, ‘BETWEEN a AND b’ 와 같이 ‘=‘ 조건이 아닌 다른 종류의 연산자를 사용하는 조인 방법

     Select e.empno, e.ename, e.sal, s.grade

     From emp e, salgrade s

     Where e.sal >=s.losal and e.sal <=s.hisal

    

     ※ ANSI 방식

     Select e.empno, e.ename, e.sal, s.grade

     From emp e, salgrade s

     ON ( e.sal >=s.losal and e.sal <=s.hisa)

 

 

ex) temp 테이블의 사람 중 emp_level 테이블에서의 부장직급을 받아야 할 나이를 가지고 있는 사람의 사번, 성명, 생일, 현재나이, 현재 직급을 출력하세요.

 

     select t.emp_id, t.emp_name, t.birth_date, trunc((sysdate-t.birth_date)/365+1) as "나이",

t.lev

from temp t, emp_level e

where trunc((sysdate-t.birth_date)/365+1) between e.from_age and e.to_age

and e.lev='부장';

          

ex) Test13 , test14 테이블을 사용하여

1)     고객별로 받을 수 있는 상품을 출력하고, 2)각 상품이 몇 개가 필요한지 출력하세요.

                 <1번 결과>                                                   <2번 결과>

          

           select test14.cust, test13.gift from test13 , test14

where test13.point between test14.fpoint and test14.tpoint;

 

select test13.gift, count(test14.cust) from test13, test14

where test14.point between test13.fpoint and test13.tpoint

group by test13.gift;

          

ex) Test13 , test14 테이블을 참조하여

고객의 포인트보다 낮은 모든 등급의 상품을 선택할 수 있다고 할때 갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트, 선물을 보여주는 쿼리를 작성하시오

      select test14.cust, test14.point, test13.gift from test13, test14

where test13.gift='갈비세트' and test13.tpoint < test14.point;

 

 

4.     OUTER JOIN

결과가 NULL 일지라도 출력해야 할 경우 사용. NULL이 출력되는 칼럼에 (+)기호를 추가한다.

    

     Select e.empno, e.ename, e.sal, d.dname

     From emp1 e, dept1 d

     Where e.deptno = d.deptno(+)

    

     ※ ANSI 방식: Oracle과 반대. Null이 아닌 쪽에 준다. 왼쪽 Left 오른쪽은 Right. (여기는left)

     Select e.ename, e.ename, e.sal, d.dname

     From emp1 e left outer join dept1 d

     ON (e.deptno=d.deptno)

 

Ex) 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도 교수의 이름, 직급을 출력하여라. 단, 지도 교수가 배정되지 않은 학생이름도 함께 출력하여라.

 

SELECT s.name sname, s.grade, p.name pname, p.position

FROM student s, professor p

WHERE s.profno = p.profno(+) ORDER BY  p.profno;

 

     ※ Full outer join (지도교수, 학생 모두 Null일 경우 출력.)

     Where s.profno(+) = p.profno(+) <- 이런 문법은 성립하지 않는다. UNION을 사용한다.

    

     SELECT s.name sname, s.grade, p.name pname, p.position

FROM student s, professor p

WHERE s.profno = p.profno(+) ORDER BY  p.profno

     UNION

     SELECT s.name sname, s.grade, p.name pname, p.position

FROM student s, professor p

WHERE s.profno(+) = p.profno  ORDER BY  p.profno;

 

ANSI 방식

Select ~

From ~ FULL OUTER JOIN ~

ON ~;

    

Ex) Temp 테이블과 emp_level 테이블을 조인하여 각 사원의 사번, 이름, 직급, 현재연봉, 해당 직급의 연봉의 상, 하한금액을 보고자 한다. 단 연봉의 상, 하한이 결정 안 된 수습사원은 사번, 이름, 직급, 현재연봉만 나오면 된다. 쿼리를 만들어보세요.

 

select t.emp_id, t.emp_name, t.lev, t.salary, e.from_sal, e.to_sal from temp t, emp_level e

where t.lev=e.lev(+)

    

5.     SELF JOIN

원하는 데이터가 한 개의 테이블에 존재할 경우, 하나의 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN 을 하는 조인

 

※ ANSI 방식

Select ~

From emp e JOIN emp m

ON (e.mgr=m.empno) order by 1;

 

※ 3개 이상의 테이블을 사용하는 조인

Select ~

From emp1 e, dept1 d, loc1 l

Where e.deptno = d.dptno

And d.loc_id = l.loc_id;

 

ANSI 방식: 3개 이상일 경우, JOIN 사용 후 ON(where)다음 다시 JOIN으로 묶어준다

Select ~

From emp1 e JOIN dept1 d

ON (e.detpno = d.dptno)

JOIN loc l

ON (d.loc_id=l.loc_id)

 

Ex) 부서번호가 201 이상인 부서 이름과 해당 부서가 소속된 상위 부서의 이름을 출력.

 

SELECT dept.dname || ‘ is belong to ’ || org.dname

FROM    department dept, department org

WHERE   dept.college = org.deptno

AND    dept.deptno >= 201;

 

Ex) 몸무게가 80kg 이상인 학생의 학번, 이름, 체중, 학과이름, 학과위치를 출력하여라

 

select s.studno,s.name,s.weight,p.dname,p.loc

from student s, department p

where s.deptno=p.deptno and s.weight > 80 ;

 

 

0.     Temp 테이블을 사용해 사번, 성명, 생일, 자신 보다 생일이 빠른 사람 수를 조회하여

자신보다 생일이 빠른 사람수순서대로 오름차순으로 정렬해서 출력하세요

 

<출력 결과>

사번      이름   생일    빠른사람수

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

19960303 설까치25-SEP-71 0

19966102 지문덕05-JUL-72 1

19930402 강감찬15-AUG-72 2

19960212 배뱅이15-DEC-72 3

19960101 홍길동22-MAR-73 4

19950303 이순신15-JUN-73 5

19970101 김길동25-JAN-74 6

19970201 박문수15-APR-75 7

19930331 정도령25-MAY-76 8

19970112 연흥부05-NOV-76 9

20000101 이태백25-JAN-80 10

………………………………

20 rows selected.

 

1.     EQUI JOIN 을 이용하여 학생 이름과 소속 학과 이름을 학과 이름, 학생 이름순으로 정렬하여 출력하세요.

2.     OUTER JOIN을 이용하여 101번 학과에 소속된 학생들의 이름과 지도 교수 이름을 출력 하세요. 이때 지도교수가 배정되지 않은 학생도 함께 출력 하세요.

3.     공과대학에 소속된 학부와 학과 이름을 출력하세요.

4.     각 학과별로 학과번호, 학과이름, 소속 학생이름, 학생의 지도교수 이름, 지도교수 직급, 지도교수 소속 학과번호, 지도교수 소속 확과 명 출력(지도교수 없는 학생과 지도 학생이 없는 교수 모두 출력)

5.     교수 테이블에서 학과번호, 교수이름, 교수 별 지도 학생 수, 학과별 소계학생 수, 총 학생 수를 출력해라.

 

각 학과에 소속된 학과 이름, 학생 이름, 교수 이름을 출력하세요.

모든 학생의 이름, 지도교수 이름, 학과 이름을 출력하세요.

교수 별로 교수 이름과 지도 학생 수를 출력하세요.

select t.emp_id 사번,t.emp_name 이름,t.birth_date 생일,count(c.birth_date) as 빠른사람수

from temp t, temp c

where t.birth_date > c.birth_date(+)

group by t.emp_id, t.emp_name, t.birth_date order by 빠른사람수

 

1.      

select d.dname 학과이름, s.name 학생이름

from student s, department d

where s.deptno = d. deptno

order by d.dname,s.name;

 

2.      

select s.name 학생,p.name 지도교수

from student s, professor p

where s.profno=p.profno(+) and s.deptno=101;

 

3.      

select a.dname 학부, b.dname 학과

from department a, department b

where a.deptno=b.college

 

4.      

select d.deptno 학과번호, d.dname 학과이름, s.name 학생이름, p.name 지도교수, p.position 교수직급, p.deptno 교수학과번호, d.dname 교수학과명

from student s, department d, professor p

where s.deptno=d.deptno(+) and s.profno=p.profno(+)

union

select d.deptno 학과번호, d.dname 학과이름, s.name 학생이름, p.name 지도교수, p.position 교수직급, p.deptno 교수학과번호, d.dname 교수학과명

from student s, department d, professor p

where p.deptno=d.deptno(+) and s.profno(+)=p.profno

 

5.      

select p.deptno 학과번호, p.name 교수이름, nvl(count(s.name),0) 지도학생수,

from professor p, student s

where s.profno(+)=p.profno

group by rollup(p.deptno, p.name)