OracleDataBase10 - Join

 

JOIN

 ; 두개이상의 테이블에 나눠져 있는 관련 데이터들을 하나의 테이블로 모아서 조회하고자 할때 사용하는 명령입니다.

 

 

 

-> 'SCOTT' 이라는 직원의 부서명과 지역을 조회햐려면....

select deptno from emp where ename='SCOTT';   -- 20

 먼저 'SCOTT' 의 부서번호를 조회합니다.

select dname, loc from dept where deptno=20;

 조회한 부서번호로 부서명과 지역을 조회합니다. -->너무 번거로움..

 

 

 

- 위의 두개의 명령을 하나의 명형으로 합해주는 역할을 join 명령이 실행합니다

 

[1] cross join : 두개 이상의 테이블이 조인될때 where절에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는 경우

 

create table testA(

fieldA1 varchar2(10),

fieldA2 varchar2(10),

fieldA3 varchar2(10)

);

insert into testA values('value11','value12','value13');

insert into testA values('value21','value22','value23');

insert into testA values('value31','value32','value33');

 

drop table testB purge;

create table testB(

fieldB1 number(10),

fieldB2 number(10)

);

insert into testB values(11,12);

insert into testB values(21,22);

 

select * from testA;

select * from testB;

 

 * 가장 최악의 결과를 얻는 조인 방식

-A 테이블과 B 테이블의 cross join 된다면

-A테이블의 1번 레코드와 B테이블의 모든 레코드와 하나하나 모두 조합

-A테이블의 2번 레코드와 B테이블의 모든 레코드와 하나하나 모두  조합

-A테이블의 3번 레코드와 B테이블의 모든 레코드와 하나하나 모두  조합

 

select * from testA;  -- 레코드 3, 필드 3

select * from testB;  -- 레코드 2, 필드 2

-- 크로스 조인결과

select * from testA, testB;   -- 레코드 6, 필드 5

 

 

 

 

[2] equi join : 조인 대상이 되는 두테이블에서 공통적으로 존재하는 컬럼의 값이 일치하는 행을 연결하여 결과를 생성

 

 

 select * from dept   -- 레코드 4, 필드 3

 select * from emp  -- 레코드 13, 필드 8

 select * from emp, dept   -- 레코드 52, 필드 11

 

 -> 크로스 조인으로 조합된 52개의 레코드중  emp의 deptno와 detp의 deptno 가 같은 것만 골라내어 13개를 골라냅니다

 select * from emp, dept where emp.deptno = dept.deptno;

 - 실행의 결과는 해당 사원의 부서번호에 따른 부서명과 지역이 해당 사원 옆에  나란히 위치해서 표시됩니다.

 

 ->각 사원의 이름, 부서번호, 부서명, 지역만 을 출력하세요

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

 from emp, dept 

 where emp.deptno = dept.deptno;

 - select와 from 사이에 필요한 필드만 표시하려한다면, 위와 같이 필드명 뿐만아니라 그 필드의 소속 테이블 이름을

     '.' 점으로 구분해서 이어줘야 정확히 어느 테이블의 필드를 표시할지가 결정됩니다. 물론 이름만으로 그것이 구분된다면

    그 필드는 테이블 이름을 안써도 무방하지만, deptno 같이 두 테이블에 동일한 이름로 존재하는 필드는 반드시 테이블명

    까지 같이 써주어야 합니다.

 

 

 

 

 

 

-테이블 명에 별칭을 부여한 후 컬럼앞에 소속테이블을 지정  

 

select a.ename, b.dname, b.loc, a.deptno  

from emp a, dept b

where a.deptno = b.deptno and a.ename='SCOTT';

 

select a.num, a.rentdate, a.mnum, b.name, b.phone

from rentlist a, memberlist b

where a.mnum = b.num;

 

 

[3] non-equi join

-동일 컬럼이 없어서  다른 조건을 사용하여 조인

-조인 조건에 특정 범위내에 있는지를 조사하기 위해 조건절에 조인 조건을 '=' 연산자 이외의 비교

select * from emp;

select * from salgrade;

 

--연산자를 이용

select a.ename, a.sal, b.grade from emp a, salgrade b

where a.sal>=b.losal and a.sal<=b.hisal;

 

-- 비교 명령을 이용

select a.ename, a.sal, b.grade from emp a, salgrade b

where  a.sal between b.losal and b.hisal;

 

 

 

[4] 세개의 테이블을 하나로 JOIN(equi , nonequi 조인 의 조합)

select a.ename, a.sal, c.grade, b.dname 

from emp a, dept b, salgrade c

where a.deptno = b.deptno and  a.sal between c.losal and c.hisal;

 

 

 

 

 

 

 

[5] outer join

-조인 조건에 만족하지 못해서 해당 결과를 출력시에 누락이 되는 문제점이 발생할때 해당 레코드를출력하는 조인

select a.num, b.bnum , a.subject, b.rentdate 

from booklist a, rentlist b 

where a.num=b.bnum(+);

 booklist 에는 도서코드가 있지만 한번도 대여한적이 없어서 rentlist에는 존재하는 않아서 null 로 표시되는 경우...

 보통 null 로 표시될 필드에 (+) 표시를 넣습니다

 

 

 

[6] ANSI join

  (1) Ansi Cross join

select * from emp, dept  -- 일반크로스 조인 표현 

select * from emp cross join dept     -- ansi Cross join -> 일반 크로스 조인과 같은 효과

 (2) Ansi inner join 

-일반 equi 조인과 같은 효과

select  ename, dname from emp a, dept b where a.deptno = b.deptno 

 

-Ansi 이너 조인의 표현 방식

select  ename, dname 

from emp inner join dept 

on emp.deptno = dept.deptno;

 

-Ansi 이너 조인의  다른 표현 방식 : 두테이블의 조인 기준이 되는 필드명이 똑같을때만 사용가능

select  ename, dname 

from emp inner join dept 

using (deptno); 

 

(3) Ansi Outer Join  -- 기존 아우터 조인의 표현방식

select * from emp, dept 

where emp.deptno = dept.deptno(+);

select * from emp, dept 

where emp.deptno(+) = dept.deptno;

 

- Ansi Outer Join 표현방식

select * from emp 

left outer join dept 

on  emp.deptno = dept.deptno;

select * from emp 

right outer join dept 

on  emp.deptno = dept.deptno;

-> 기준이 되는 필드명중 A 테이블의 필드에는 있으나 B테이블 필드에는 해당값이 없는 경우에 대한 표현여부결정