[Oracle] Join

4. Join

4.1 정의

하나의 테이블로 원하는 컬럼 정보를 참조할 수 없는 경우 관련된 테이블을 논리적으로 결합하여 원하는 컬럼 정보를 참조하는 방법

4.2 전제조건

4.2.1 논리적으로 결합되는 2개 이상의 테이블에는 반드시 공통 컬럼이 있어야 함

4.2.2 공통 컬럼은 데이터 타입과 데이터가 동일해야 함

4.3 Join 형식

SELECT 컬럼 리스트

FROM 조인대상 테이블들(컴머로 구분, 별칭사용)

WHERE 공통 컬럼을 이용한 조인조건

AND 일반조건;

(문제) Student, Department 테이블을 검색하여 이름, 학과코드, 학과이름을 출력하시오.

(문제) Emp 테이블의 사원번호와 Dept 테이블의 부서명을 조회하시오.

select e.empno, d.dname

from emp e, dept d

where e.deptno = d.deptno;

(문제) Student 테이블과 Department 테이블, Professor 테이블을 조인하여 학생의 이름과 학과이름, 지도교수 이름을 출력하시오.

select s.name "학생이름", d.dname "학과", p.pname "지도교수"

from student s, department d, professor p

where s.deptno1 = d.deptno and s.profno = p.profno;

4.4 종류

4.4.1 cross 조인(Cartesian Product, 카티지언 곱)

4.4.1.1 2개 이상의 테이블이 조인될 때 where 절에서 공통 컬럼에 의한 결합이 발생하지 않아서 두 테이블 간의 조합가능한 모든 경우의 수를 계산하여 결과를 산출하는 조인

　4.4.1.2 엄밀한 의미에서 조인이 아님

　　select e.ename, d.dname

　　from emp e, dept d;

4.4.2 내부 조인(inner join, 동등조인, Equi Join)

가장 일반적인 형태, where 절에 사용된 공통컬럼들이 동등연산자('=')에 의해 비교되는 조인

4.4.3 self join

참조해야 할 컬럼이 자신의 테이블에 있는 다른 컬럼인 경우에 사용하는 조인.

반드시 테이블에 대한 별칭을 써야 함.

(문제) emp 테이블에서 매니저 아이디(mgr)는 자신의 상급자의 empno와 동일하다. 이를 이용해서 각 사람의 사원번호와 이름, 매니저번호, 매니저의 이름을 조회하시오.

select

a.empno 사번, a.ename 이름,

b.empno 매니저사번, b.ename 매니저

from emp a, emp b

where a.mgr = b.empno

4.4.4 외부(outer) 조인

한쪽 테이블에는 해당하는 데이터가 존재하고 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 모든 데이터를 조회하는 조인

- 조회 조건에서 (+) 기호를 사용하는 조인.

- 데이터가 존재하지 않는 테이블의 조인 조건에 (+) 붙임

**(주의) 테이블에 (+)를 붙이지 않음

- (+)가 붙은 컬럼과는 in 연산자를 함께 사용할 수 없음

- (+)가 붙은 컬럼과는 서브쿼리를 같이 사용할 수 없음

* 학생 테이블과 교수 테이블을 조인하여 학생이름과 지도교수 이름을 출력

(단, 지도교수가 배정되지 않은 학생의 명단도 함께 출력)

select s.name, p.name

from student s, professor p

where s.profno=p.profno(+);

4.4.5 ANSI 조인: 새로운 국제 표준에 따른 조인, oracle 9i부터 지원

가) 내부조인 : inner join 사용

- where절 대신 on 사용

select e.empno, d.dname

from emp e inner join dept d

on e.deptno = d.deptno;

- where절 대신 using 사용(참조하는 컬럼명이 동일한 경우에만 사용가능)

select e.empno, d.dname

from emp e inner join dept d

using (deptno);

나) 외부조인 : [left|right|full] outer join 사용

- 데이터가 있는 테이블편을 기준으로 left 또는 right를 붙임

select s.name, s.deptno1, p.name

from student s full outer join professor p

on s.profno = p.profno;

4.5 실습예제

4.5.1 Emp와 dept 테이블을 조인하여 부서번호, 부서명, 이름, 급여를 출력하시오.

4.5.2 직책이 '사원'인 사원의 이름, 부서번호, 부서이름을 출력하시오.

4.5.3 이름이 '황인태'인 사원의 부서명을 출력하시오.

4.5.4 emp 테이블과 dept 테이블을 조인하여 모든 사원의 이름, 부서번호, 부서명, 급여를 출력하시오.

4.5.5 emp 테이블에 있는 empno, mgr를 이용하여 서로의 관계를 다음과 같이 출력하시오. "박진성의 매니저는 임채호이다"