OUT Join

◈ Out(외부) Join 

 - equijoin 문장들의 한가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개 
    컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 Return하지 않는 다는 것입니다. 

 - 정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다. 
    Outer join 연산자 "( + )"입니다. 

 - 조인시킬 값이 없는 조인측에 "( + )"를 위치 시킵니다. 

 - Outer join 연산자는 표현식의 한 편에만 올 수 있습니다. 

예제1) 일반 조인의 경우  SQL> SELECT DISTINCT(a.deptno), b.deptno          FROM emp a, dept b          WHERE  a.deptno = b.deptno DEPTNO     DEPTNO ---------- ----------         10         10         20         20         30         30 예제2)out join을 했을 경우 SQL>  SELECT DISTINCT(a.deptno), b.deptno           FROM emp a, dept b           WHERE  a.deptno(+) = b.deptno  DEPTNO     DEPTNO  -------     ----------      10         10      20         20      30         30                  40 ※ 다음의 쿼리를 한번 잘 보시기 바랍니다.  SQL>  SELECT DISTINCT(a.deptno), b.deptno           FROM emp a, dept b           WHERE  a.deptno(+) = b.deptno                AND a.ename LIKE '%';     DEPTNO     DEPTNO ---------- ----------         10         10         20         20         30         30 쿼리 결과를 잘 보면 out조인이 되지 않은 것을 알 수 있습니다. 위 쿼리를 out조인이 되기 위해서는 아래와 같이 고쳐야 합니다.  SQL> SELECT DISTINCT(a.deptno), b.deptno          FROM emp a, dept b          WHERE  a.deptno(+) = b.deptno               AND a.ename(+) LIKE '%'     DEPTNO     DEPTNO ---------- ----------         10         10         20         20         30         30                     40 OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도  똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.

Oracle9i 부터는 ANSI/ISO SQL표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 합니다.

☞ LEFT OUTER JOIN   왼쪽 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.     SQL>SELECT DISTINCT(e.deptno), d.deptno      FROM dept d LEFT OUTER JOIN emp e      ON d.deptno = e.deptno;        ☞ RIGHT OUTER JOIN   - 오른쪽에 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.     SQL>SELECT DISTINCT(a.deptno), b.deptno      FROM emp a RIGHT OUTER JOIN dept b      ON a.deptno = b.deptno;        ☞ FULL OUTER JOIN  양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다.     SQL>SELECT DISTINCT(a.deptno), b.deptno      FROM emp a FULL OUTER JOIN dept b      ON a.deptno = b.deptno;     -- 위 세 문장의 결과는 아래와 같습니다.      DEPTNO     DEPTNO  ---------- ----------          10         10          20         20          30         30                     40     LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 하실 수 있습니다.

지금까지 SQL의 조인을 어설프게 이해하고 사용하고 있었다 덕분에 오늘 삽질좀 했다. 그래서 관련 내용을 정리해본다. 

1. INNER JOIN
  - 두 테이블간의 조인 조건을 만족하는 ROW만 리턴함... (교집합 이라고 하기엔 좀 애매하지만 일단 그렇게 이해하는게 쉽다.)

 

2. OUTER JOIN
  - LEFT/RIGHT/FULL 형태의 OUTER JOIN 이 있음
  - LEFT OUTER JOIN의 경우 조인문 왼쪽에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭하며, 매칭되는 데이터가 없는 경우 NULL 매칭
  - RIGHT OUTER JOIN은 LEFT 조인의 반대  - FULL OUTER JOIN은 일반적으로 사용할 일이 없으며, DB에 따라 지원하지 않음??? 간단히 설명하자면 두 테이블의 합집합이라고 이해하면 될 듯...

3. 주의 사항
  - 일반적으로 조인은 1:1 혹은 N:1 의 관계를 갖는 경우 유용한 것 같음... 1:N 관계에서 조인문을 사용하는 경우 기준이 되는 테이블의 데이터가 중복되는 결과를 리턴함. 따라서 1:N 관계에서 조인문을 통해 N에 해당하는 테이블의 컬럼을 이용해 제한조건을 사용하는 경우 distinct 혹은 group by 를 사용하여 1에 해당 하는 테이블의 데이터가 중복되지 않도록 해야 한다.  (에궁... 먼가 명확하지 않은 설명 ㅠ.ㅠ)
 

4. 예제 

CREATE TABLE tbl1 (id INT, name VARCHAR(8));
INSERT INTO tbl1 (id,name) VALUES (1,'aaa') ;
INSERT INTO tbl1 (id,name) VALUES (2,'bbb') ;
INSERT INTO tbl1 (id,name) VALUES (3,'ccc') ;
INSERT INTO tbl1 (id,name) VALUES (4,'ddd') ;
INSERT INTO tbl1 (id,name) VALUES (5,'eee') ;

CREATE TABLE tbl2 (id INT, car VARCHAR(8)) ;
INSERT INTO tbl2 (id,car) VALUES (2,'AVANTE') ;
INSERT INTO tbl2 (id,car) VALUES (3,'SONATA') ;
INSERT INTO tbl2 (id,car) VALUES (2, 'MINI');

-- Inner Join1
select a.id, a.name, b.car from tbl1 a inner join tbl2 b on a.id = b.id
-- Inner Join2 (위와 동일)
select a.id, a.name, b.car from tbl1 a, tbl2 b where a.id = b.id

<result>
2 bbb   AVANTE
3 ccc   SONATA

-- left outer join
select a.id, a.name, b.car from tbl1 a left outer join tbl2 b on a.id = b.id

<result>
1 aaa null
2 bbb AVANTE
3 ccc SONATA
4 ddd null
5 eee null

-- right outer join
select a.id, a.name, b.car from tbl1 a right outer join tbl2 b on a.id = b.id

<result>
2 bbb AVANTE
3 ccc SONATA

-- left outer join 을 이용해서 inner join과 동일한 결과 얻기
select a.id, a.name, b.car from tbl1 a left outer join tbl2 b on a.id = b.id where b.id is not null

<result>
2 bbb AVANTE
3 ccc SONATA

-- N:1 관계에서의 조인 예제 (Outer Join 의 경우도 비슷함)

INSERT INTO tbl2 (id,car) values (2, 'MINI');

select a.id, a.name, b.car from tbl1 a, tbl2 b where a.id = b.id

<result>
2 bbb   AVANTE
2 bbb   MINI
3 ccc   SONATA

select distinct a.id, a.name, b.car from tbl1 a, tbl2 b where a.id = b.id group by a.id

<result>
2 bbb AVANTE
3 ccc SONATA