[Oracle] 오라클 JOIN

INNER JOIN

- 가장 일반적인 JOIN 형태

- 두 개 이상의 테이블의 공통 컬럼을 JOIN 조건으로 만들어 데이터 조회

- JOIN에 참여하는 두 컬럼은 동일한 데이터 타입(필수사항은 아님)

- WHERE 절에는 일반 조건과 JOIN 조건을 병행 사용 가능

- SELF JOIN > 자기 자신과 JOIN

- ANTI JOIN > NOT IN 연산자를 사용한 JOIN

- SEMI JOIN > EXISTS 연산자를 사용한 JOIN

SELECT A.EVENT_NO, B.CUST_NO

  FROM TEVENT A, TCUSTOMER B

WHERE A.CUST_ID = B.CUST_ID;

▶ 이벤트에 참여한 고객의 데이터 출력

 

Outer JOIN

- 두 테이블에서 어느 한 테이블의 데이터가 없을 경우, 전체 row수를 보존하면서 조회 (데이터가 없을 경우 NULL로 출력)

- 오라클에서 OUTER JOIN의 연산자는 (+)이며 모두 출력되어야 하는 테이블의 반대쪽에 (+) 표시

- WHERE 절에서만 (+)

- 테이블 간 OUTER JOIN 조건이 1개 이상일 경우, 모든 OUTER JOIN 조건에 (+)

- OR 연산자와는 사용 불가

- 오직 한 개의 테이블과만 OUTER JOIN 가능

- (+)가 붙은 컬럼과는 IN 사용 불가

SELECT A.EVENT_NO, B.CUST_NAME

   FROM TEVENT A, TCUSTOMER B

WHERE B.CUST_ID = A.CUST_ID(+);

▶ 모든 고객의 데이터 출력 (이벤트에 참여했든 참여하지 않았든..)

Outer NL JOIN, Outer Sort Merge JOIN

- Outer JOIN할 때 방향이 한쪽으로 고정, (+) 기호가 붙지 않은 테이블이 항상 드라이빙

(힌트를 이용해도 순서가 바뀌지 않음)

Outer Hash JOIN (Right Outer Hash JOIN)

-- Inner 쪽 집합을 Hash 테이블로 빌드 가능 (10g 부터)

SELECT /*+ use_hash(d e) swap_join_inputs(d) */ D.DNAME, E.ENAME

  FROM DEPT D, EMP E

WHERE E.DEPTNO = D.DEPTNO(+);

-- 부서가 NULL인 직원도 다 보여줘라.

-- But!! EMP 테이블은 Hash Area에 들어가기엔 넘나 크다는 것

-- swap_join_inputs(d) 힌트를 사용하여 DEPT 테이블이 빌드되도록 유도

Execution Plan

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

  0          SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=14 Bytes=280)

  1        0   HASH JOIN (RIGHT OUTER) (Cost=9 Card=14 Bytes=280)

  2        1         TABLE ACCESS (FULL) OF 'DEPT' (Cost=4 Card=4 Bytes=44)

  3        1         TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=14 Bytes=126)

-- Inner 집합인 DEPT 테이블을 Hash 테이블로 빌드

-- Outer 집합인 EMP 테이블을 읽으면서 Hash 테이블을 탐색

-- Outer JOIN이므로 JOIN 성공 여부에 상관없이 EMP 레코드를 결과집합에 삽입

Full Outer JOIN

Ansi Full Outer JOIN

- 내부적으로는 Left Outer JOIN + Union All + Anti JOIN (Not Exists) 방식

Native Hash Full Outer JOIN

- /*+ opt_param('_optimizer_native_full_outer_join', 'force') */ 힌트 사용 

1) A 테이블을 Hash 테이블로 빌드

2) B 테이블로 해시 테이블을 탐색하면서 JOIN

3) JOIN 성공 여부에 상관없이 B 테이블 레코드를 결과집합에 삽입, JOIN에 성공한 레코드에 체크 표시

4) 탐색 단계가 끝나면 Right Outer Hash JOIN과 동일한 결과 집합이 만들어짐

5) Hash 테이블을 스캔하면서 체크 표시가 없는 A 테이블 레코드를 결과집합에 삽입

→ B 테이블에 존재하지 않는 (B 테이블의 컬럼값이 NULL인) A 테이블의 레코드가 마지막에 출력

Uion All을 이용한 Full Outer JOIN

SELECT 고객ID, SUM(입금액) 입금액, SUM(출금액) 출금액

   FROM (

SELECT 고객ID, 입금액, TO_NUMBER(NULL) 출금액

   FROM 입금

UNION ALL

SELECT 고객ID, TO_NUMBER(NULL) 입금액, 출금액

   FROM 출금

   )

GROUP BY 고객ID;

ANSI JOIN

- ANSI 표준 문법을 사용한 JOIN

- INNER JOIN ... ON

- INNER JOIN ... USING()

- CROSS JOIN

- [LEFT | RIGHT | FULL] OUTER JOIN

+ LEFT OUTER JOIN > 왼쪽에 있는 것은 JOIN 조건이 맞지 않더라도 다 나타나야 함

+ RIGHT OUTER JOIN > 오른쪽에 있는 것은 JOIN 조건이 맞지 않더라도 다 나타나야 함 

+ FULL OUTER JOIN > JOIN시 양쪽 테이블의 데이터를 전부 출력

* FULL OUTER JOIN의 경우, ANSI 문법만 가능