오라클〃[공부]13. 크로스조인, 셀프조인, 세미조인, ANSI조인

후.. 조인은 예제로 공부해도 머리에 잘 들어오지 않아 어렵지만 그래도 내부조인과 외부조인에 이어 나머지 크로스조인과, 셀프조인, 세미조인, ANSI조인까지 마무리 해야겠어요. 

내부조인과 외부조인만 제대로 숙지하고 있어도 불편함은 없겠지만 블로그에 공부목적으로 정리를 하기 때문에 간단하게 예제를 실습하면서 살펴볼까합니다. 

■ 크로스 조인

크로스 조인(Cross Join)은 Cartesian Product라고 하며 거의 사용되지 않습니다. 그렇기 때문에 엄밀한 의미에서 조인이라고 할 수 없는데요. 테이블 상호간에 연결될 수 있는 모든 경우의 수를 산출하여 나타내는 조인입니다.

■ 셀프 조인

셀프조인(Self-Join)은 동일한 테이블을 대상으로 조인을 맺는 것을 말하며 반드시 별명을 붙여서 사용합니다. 동일한 테이블에서 상호간의 관계를 맺고 있고, 이를 근거로 자료를 산출하고자 할 때 사용하며 자기참조 테이블(같은 테이블의 특정 컬럼을 참조하는 테이블)에서 셀프조인이 사용됩니다.

예를 들어보면 이렇습니다. 인사테이블에는 각각의 직원들에 대한 매니저 관계를 나타내는 컬럼이 있으며, 매니저 또한 인사테이블에 존재합니다. 이때 임의의 사원에 대한 매니저 정보는 동일한 테이블에 존재하게 되는데 그럴경우 상호간의 관계를 나타내고자 셀프조인을 사용하게 됩니다. 극히 제한적이기는 하지만 셀프조인도 필요할 때가 있기 때문이죠. 

■ 안티 조인

안티조인(Anti-Join)은 기준이 되는 테이블을 중심으로 기준 테이블의 공통 컬럼 값과 다른 값을 가진 또 다른 테이블의 데이터를 추출하는 관계로 Not In 연산자를 사용하는 조인을 말합니다.

1) 메인쿼리인 employees의 데이터중에 

NOT IN을 사용하여 서브쿼리의 departments테이블 칼럼중

dep.location_id = 1500인것을 제외한 결과물을 얻어내는것입니다.

2) demp.location_id = 1500을 출력하면

department_id값이 50이네요.

3) 이 쿼리문은 1번 사진의 쿼리문과 동일합니다.

NOT IN 외부조인으로 출력된 값은 

120~140번의 employee_id가 제외된 결과가 추출되었습니다. 

■ 세미 조인

세미 조인(Semi-Join)은 EXISTS 연산자를 사용하여 조인하는 방법입니다. 조인의 종류가 많기 때문에 만약에 기억이 안나신다면 세미조인은 서브쿼리내에서 존재하는 데이터만 추출한다는점, 그리고 위에서 정리한 안티조인의 반대라고 생각하시면 됩니다.

※ EXISTS는 특정 컬럴럼값이 존재하는지 여부를 체크하며 오직 서브쿼리에만 올 수 있습니다.

※ 일반조인과 다른점으로는 세미조인의 결과값은 중복이 제거됩니다. 

→ 위 예제는 employees테이블과 departments테이블의 department_id가 같은 값들을 뽑아내고, salary(봉급)이 10000달러 이상인 직원만 뽑아서 추출합니다. 추가로 EXISTS을 붙였기 때문에 서브쿼리인 employees테이블내에 있는 데이터만 추출하고 중복을 제거해서 결과를 띄웁니다.

■ ANSI 조인

1. 일반적인 내부조인 

SELECT emp.employee_id, emp.first_name, dep.department_name
  FROM employees emp, departments dep
 WHERE emp.department_id = dep.department_id;

1-1. ANSI 내부조인 ( Where 구문을 대신하여 On을 사용 )

SELECT emp.employee_id, emp.first_name, dep.department_name
  FROM employees emp INNER JOIN departments dep
    ON emp.department_id = dep.department_id;

1-2. ANSI 내부조인 ( Using 구문을 사용하여 동일한 비교컬럼을 하나만 기술하는 방법도 가능)

SELECT emp.employee_id, emp.first_name, dep.department_name
  FROM employees emp INNER JOIN departments dep
 USING ( department_id );

※ Using구문을 사용할 경우 조건에 사용된 첫번째줄의 dep.department_name을 department_name으로 테이블명을 제거하고 컬럼명만 기술하는게 일반적입니다. 

2. 일반적인 외부조인 

SELECT Jhis.employee_id, emp.first_name
  FROM employees emp, job_history jhis
 WHERE emp.employee_id = jhis.employee_id(+)
 ORDER BY jhis.employee_id;

2-1. ANSI 외부조인 ( +를 대신하여 기준이 되는 위치를 표기하고 OUTER JOIN을 명시합니다. )

SELECT Jhis.employee_id, emp.first_name
  FROM employees emp LEFT OUTER JOIN job_history jhis
    ON emp.employee_id = jhis.employee_id
ORDER BY jhis.employee_id;

SELECT Jhis.employee_id, emp.first_name
  FROM job_history jhis RIGHT OUTER JOIN employees emp
    ON emp.employee_id = jhis.employee_id
ORDER BY jhis.employee_id;

SELECT emp.employee_id, emp.first_name, dep.department_name
  FROM employees emp FULL OUTER JOIN departments dep
    ON emp.employee_id = dep.manager_id;

2-2. ANSI 외부조인 ( Using 사용 )

SELECT employee_id, emp.first_name
  FROM job_history jhis RIGHT OUTER JOIN employees emp
 USING ( employee_id )
ORDER BY employee_id;

▼ 관련글

2015/12/18 - 오라클〃[공부]3. DML / DDL / DCL 비교

2015/12/18 - 오라클〃[공부]4. Oracle Select 총 정리

2015/12/20 - 오라클〃[공부]5. Oracle 숫자 내장함수(Function)

2015/12/20 - 오라클〃[공부]6. Oracle 문자 내장함수

2015/12/20 - 오라클〃[공부]7. Oracle 날짜 내장함수

2015/12/20 - 오라클〃[공부]8. Oracle 변환 내장함수

2015/12/20 - 오라클〃[공부]9. Decode 함수와 Case 표현식

2015/12/20 - 오라클〃[공부]10. NULL 내장함수 (NVL / NULLIF)

2015/12/21 - 오라클〃[공부]11. 서브쿼리(Sub Query) 정리

2015/12/21 - 오라클〃[공부]12. 내부조인, 외부조인 정리 및 제약조건 + 예제