JOIN 함수

JOIN 함수

여러 곳의 테이블에 흩어져 있는 정보중 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 볼 수 있게끔 하는 방법

INNER JOIN (이너조인) - 테이블에 데이터가 존재하는 경우에만 값을 출력

OUTER JOIN (아우터조인) - 한쪽 테이블에는 데이터가 있고 다른 한쪽에는 데이터가 없을 경우 데이터가 있는 쪽 테이블의                                          내용을 전부 출력하게 하는 방법

Oracle 제품에서만 사용되는 Oracle 용 Join 과 모든 제품들에서 공통적으로 사용 가능한 ANSI Join 방법 두가지가 있다.

- Oracle Join 구문 :

SQL> SELECT e.ename , d.dname

      2  FROM EMP e , dept d ;

- ANSI Join 구문 :

SQL> SELECT e.ename , d.dname

     2  FROM emp e CROSS JOIN dept d

1. EQUI Join (등가 Join)

양쪽 테이블에 같은 조건이 존재할 경우의 값만을 가져오는 Join

- Oracle Join 구문 :

SCOTT>SELECT s.name "학생이름" , s.deptno1 "학과번호", d.dname "학과이름"

2    FROM student s , department d

3    WHERE s.deptno1 = d.deptno ;

- ANSI Join 구문 :

SCOTT>SELECT s.name "학생이름" , s.deptno1 "학과번호", d.dname "학과이름"

2    FROM student s JOIN department d

3    ON s.deptno1 = d.deptno ;

- Oracle Join 구문 :

SCOTT>SELECT s.name "학생이름" , d.dname "학과이름" , p.name "교수이름"

2    FROM student s , department d , professor p

3    WHERE s.deptno1 = d.deptno

4    AND s.profno = p.profno ;

- ANSI Join 구문 :

SCOTT>SELECT s.name "학생이름" , d.dname "학과이름" , p.name "교수이름"

2    FROM student s JOIN department d

3    ON s.deptno1 = d.deptno

4    JOIN professor p

5    ON s.profno = p.profno ;

- Oracle Join 구문 :

SCOTT>SELECT s.name "학생이름" , p.name "지도교수명"

2    FROM student s , professor p

3    WHERE s.profno = p.profno

4    AND s.deptno1 = 101 ;

- ANSI Join 구문 :

SCOTT>SELECT s.name "학생이름" , p.name "지도교수명"

2    FROM student s JOIN professor p

3    ON s.profno = p.profno

4    AND s.deptno1 = 101 ;

2. NON-Equit Join (비 등가 Join)

양쪽 테이블에 있는 데이터가 서로 같은 조건이 아닌 크거나 작어나 하는 경우의 JOIN

- Oracle Join 구문 :

SCOTT>SELECT go.gname "고객명" , go.point "POINT" , gi,gname "상품명"

2    FROM gogak go , gift gi

3    WHERE go.point BETWEEN gi.g_start AND gi.g_end ;

- ANSI Join 구문 :

SCOTT>SELECT go.gname "고객명" , go.point "POINT" , gi,gname "상품명"

2    FROM gogak go JOIN gift gi

3    ON go.point BETWEEN gi.g_start AND gi.g_end ;

- Oracle Join 구문 :

SCOTT>SELECT gI.gname "상품명" , COUNT(*) "필요수량"

2    FROM gogak go , gift gi

3    WHERE go.point BETWEEN gi.g_start AND gi.g_end ;

4    GROUP BY gi.gname ;

- ANSI Join 구문 :

SCOTT>SELECT gI.gname "상품명" , COUNT(*) "필요수량"

2    FROM gogak go JOIN gift gi

3    ON go.point BETWEEN gi.g_start AND gi.g_end ;

4    GROUP BY gi.gname ;

- Oracle Join 구문 :

SCOTT>SELECT go.gname "고객명" , go.point "POINT" , gi.gname "상품명"

2    FROM gogak go , gift gi

3    WHERE gi.g_start <= go.point

4    AND gi.gname = '산악용자전거' ;

- ANSI Join 구문 :

SCOTT>SELECT go.gname "고객명" , go.point "POINT" , gi.gname "상품명"

2    FROM gogak go JOIN gift gi

3    ON gi.g_start <= go.point

4    AND gi.gname = '산악용자전거' ;

- Oracle Join 구문 :

SCOTT>SELECT e.name "이름" , trunc((sysdate-e.birthday)/365,0) "현재나이" , e.position "현재직급", 

g.position "예상직급"

2    FROM emp2 e , p_grade g

3    WHERE trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ;

- ANSI Join 구문 :

SCOTT>SELECT e.name "이름" , trunc((sysdate-e.birthday)/365,0) "현재나이" , e.position "현재직급", 

g.position "예상직급"

2    FROM emp2 e JOIN p_grade g

3    ON trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ;

3. OUTER Join

오라클 아우터 조인은 Where 조건절에서 데이터가 없는 쪽에 (+) 표시를 추가

ANSI 아우터 조인구문에서 2번째 행을 보면 LEFT OUTER JOIN 이라는 구문이 있는데

ANSI 아우터 조인에서는 데이터가 존재하는 쪽에 표시를 하기 때문에 이런 구문을 사용  

Outer Join 예제 2 :

Student 테이블과 Professor 테이블을 Join 하여 학생이름과 지도교수 이름을 출력하세요.

단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력하세요.

- Oracle Outer Join 구문 :

SCOTT>SELECT s.name "학생이름" , p.name "교수이름"

2    FROM student s , professor p

3    WHERE s.profno(+) = p.profno

4    UNION

5    SELECT s.name "학생이름" , p.name "교수이름"

6    FROM student s , professor p

7    WHERE s.profno = p.profno(+) ;

- ANSI Full Outer Join 구문 :

SCOTT>SELECT s.name "학생이름" , p.name "교수이름"

2    FROM student s FULL OUTER JOIN professor p

3    ON s.profno = p.profno ;

4. SELF Join

하나의 테이블에 있는 데이터를 JOIN 하는 방법

SELF Join 예 )  dept2 테이블에서 DNAME 은 부서명 , PDEPT는 상위부서 입니다.

출력 하고자 하는 형태가 "경영지원부의 상위부서는 사장실이다" 라는 형태일 경우 원하는 데이터가 모두 dept2 

테이블에 있기 때문에 SELF 조인을 사용

왼쪽 테이블의 PDEPT 번호와 오른쪽 테이블의 DCODE 번호가 같은 부서가 상위부서 명입니다.

- Oracle Join 구문 :

SCOTT>SELECT a.dname "부서명" , b.dname "상위부서명"

2    FROM dept2 a , dept2 b

3    WHERE a.pdept = b.dcode ;

- ANSI Join 구문 :

SCOTT>SELECT a.dname "부서명" , b.dname "상위부서명"

2    FROM dept2 a JOIN dept2 b

3    ON a.pdept = b.dcode ;

- Oracle Join 구문 :

SCOTT>SELECT p.profno "교수번호" , p.name "교수명" , p.hiredate "입사일" , count(b.hiredate) "빠른사람"

2    FROM professor p , professor b

3    WHERE p.hiredate > b.hiredate (+)

4    GROUP BY p.profno , p.name, p.hiredate

5    ORDER BY 4 ;

- ANSI Join 구문 :

SCOTT>SELECT p.profno "교수번호" , p.name "교수명" , p.hiredate "입사일" , count(b.hiredate) "빠른사람"

2    FROM professor p LEFT OUTER JOIN professor b

3    ON p.hiredate > b.hiredate 

4    GROUP BY p.profno , p.name, p.hiredate

5    ORDER BY 4 ;

- Oracle Join 구문 :

SCOTT>SELECT a.empno "사원번호" , a.name "사원명" , a.hiredate "입사일" , count(b.hiredate) "먼저입사한사람수"

2    FROM emp a , emp b

3    WHERE a.hiredate > b.hiredate (+) 

4    GROUP BY a.empno , a.name, a.hiredate

5    ORDER BY 4 ;

- ANSI Join 구문 :

SCOTT>SELECT a.empno "사원번호" , a.name "사원명" , a.hiredate "입사일" , count(b.hiredate) "먼저입사한사람수"

2    FROM emp a LEFT OUTER JOIN emp b

3    ON a.hiredate > b.hiredate (+) 

4    GROUP BY a.empno , a.name, a.hiredate

5    ORDER BY 4 ;