[오라클 DBMS 공부] 조인(Join)이란? - 내부조인, 외부조인, ANSI조인

#104

1. 조인(Join)이란?

데이터를 처리하다보면 여러 테이블의 데이터를 조합하여 처리해야하는 경우가 있습니다.

테이블의 공통 컬럼(외래키 등)을 통하여 테이블을 결합 한 후 데이터를 처리할 수 있습니다.

이럴 때 사용하는 것을 조인이라고 합니다.

주로 WHERE 절에 조인 조건이라고 하여 사용합니다.

 

2. 조인 종류

조인은 크게 세 가지가 있습니다.

내부조인, 외부조인, ANSI 조인이 있습니다.

 

3. 내부조인

가장 기본적인 조인이며, 테이블 간의 공통 컬럼을 사용하여 컬럼 조건에 의한 결과를 출력하게 해줍니다.

이 때 출력한 로우(Row)의 수는 조회조건에 만족하는 수입니다.

 

예를 보면서 사용 방법에 대해서 알아보도록 하겠습니다.

 

EX) 내부조인, EMPLOYEES의 DEPARTMENT_NAME 출력

EMPLOYEES의 테이블에는 DEPARTMENT에 대한 코드 값은 있지만 명칭이 없습니다.

명칭은 DEPARTMENTS 테이블에 있기 때문에 이 두테이블을 조인하여 값을 얻을 수 있습니다.

EMPLOYEES 테이블과 DEPARTMENTS 테이블

두 테이블을 조인한 결과

WHERE 절의 조건을 통하여 두 테이블을 조인한 결과입니다.

 

이 때 주의하셔야 할 점은 별칭(Alias)이라는 부분입니다.

말씀드렸다시피 EMPLOYEES 테이블과 DEPARTMENTS의 테이블에는 department_id라는 공통 컬럼이 있습니다.

이 컬럼이 어느 테이블의 컬럼인지 구분하기 위해서는 emp, dep와 같이 해당 테이블의 별칭을 정해주셔야 합니다.

그리고 꼭 조건이 있어야 원하시는 데이터를 출력하실 수 있습니다.

 

만약 여기서 조건이 빠지게 된다면 어떻게 될까요?

데이터는 출력되지만 카타시안 프로덕트라고 하여 양 테이블의 데이터가 N:N 매핑된 값들이 출력됩니다.

- 안티 조인

말 그대로 조건의 반대의 데이터를 처리하는 조인입니다.

NOT IN 혹은 NOT EXISTS를 사용하여 조건에 해당되지 않은 데이터들을 처리합니다.

EX) 안티조인, LOCATION_ID가 1700이 아닌 EMPLOYEES를 출력

보시면 NOT IN을 이용하여 서브쿼리에 의해 이미 출력된 데이터 중에서 해당되지 않은 데이터들만 조건을 통해 출력하도록 하였습니다.

 

- 세미 조인

세미 조인은 EXISTS 연산자를 사용하여 서브쿼리에 해당되는 데이터들을 처리하는 조인입니다.

이번에는 안티 조인과 반대로 LOCATION_ID가 1700인 사원들이 존재하는지 확인하여 출력하도록 하였습니다.

 

4. 외부 조인

내부 조인의 경우 조건에 만족하지 않을 경우 출력되지 않는 데이터들이 있습니다.

예를들어 DEPARTMENT_ID가 NULL인 경우 조건에 만족하지 않기 때문에 조인한 결과에 해당 데이터가 출력되지 않을 수 있습니다.

이 때 외부조인을 사용할 경우 해당 데이터까지 확인할 수 있습니다.

즉, 조인조건을 만족하는 데이터를 출력하지만 만족하지 않는 데이터도 출력할 수 있게 해주는 조인입니다.

 

마찬가지로 예를 들면서 확인해보도록 하겠습니다.

EMPLOYEES의 테이블에 저장된 데이터 수를 보시면 총 107개의 데이터가 있습니다.

하지만 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 내부조인한 결과 106개의 데이터만 출력되고 있습니다.

이유는 EMPLOYEES 테이블의 DEPARTMENT_ID 데이터 중 NULL값이 있어 조건에 만족하지 못하였기 때문입니다.

 

이 때 107개의 데이터를 다 출력하고 싶다면 외부조인을 사용하시면 됩니다.

외부조인은 출력되지 않은 데이터를 가진 테이블과 조인한 테이블 쪽에 (+)를 추가하여 사용합니다.

사용하실 때 주의하실 점은 보고싶은 데이터가 있는 테이블의 반대 쪽에 테이블 쪽에 (+)를 하셔야 합니다.

만약 emp.department_id 쪽에 (+)를 하게 되면 반대로 DEPARTMENTS 테이블에 있는 데이터가 출력될 수 있습니다.

 

그리고 외부조인을 사용하기 위해서는 몇가지 규칙들이 있습니다.

 

a) (+)는 WHERE 절에서만 사용 가능

b) 조인 조건외에 다른 조건이 올 경우 해당 조건에도 (+)를 추가

c) (+)는 컬럼에만 사용이 가능하며, OR 연산자와는 사용할 수 없음

d) 오직 하나의 테이블과 외부 조인이 가능

e) IN과 서브쿼리와는 함께 사용할 수 없음

f) 조건 중 한 쪽에만 (+)를 추가할 수 있음

 

5. ANSI 조인

ANSI SQL 표준과 호환하여 사용할 수 있는 조인입니다.

즉, 내부 조인, 외부 조인을 ANSI 표준에 따라 사용하는 조인입니다.

 

1) ANSI 내부 조인

ANSI 표준의 내부 조인입니다.

먼저 사용 방법입니다.

ANSI 조인의 경우 쿼리에 조인이라는 것을 확실하게 명시하여 사용하고 WHERE절 조건을 통해 조인을 하는 것이 아니라 FROM 절에 작성합니다.

그리고 WHERE 절에는 조인 조건이 아닌 일반 조건만 작성하게 됩니다.

대신 공통 컬럼에 대한 정보가 있어야하기 때문에 ON 혹은 USING을 사용하여 공통 컬럼을 나열합니다.

USING의 경우 두 테이블의 컬럼명이 같을 경우 사용합니다.

예를 들면 위의 경우 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인하고자 하는 컬럼명이 department_id로 같기 때문에 ON 문장을 USING(department_id)로 변경하여도 동일한 결과를 얻으실 수 있습니다.

 

2) 크로스 조인

ANSI 조인에서 크로스 조인은 일반 내부 조인의 카다시안 프로덕트와 같은 조인입니다.

마찬가지로 사용하게 되면 조인하고자 하는 테이블들의 데이터들을 N:N으로 매핑한 데이터들을 처리할 수 있습니다.

3) ANSI 외부 조인

ANSI 외부 조인의 경우 마찬가지로 명시적으로 JOIN이란 걸 작성해주기 때문에 (+)를 추가할 필요가 없습니다.

그럼 어떻게 명시를 하는지 알아보도록 하겠습니다.

ANSI 외부 조인에는 LEFT | RIGHT | FULL OUTER JOIN이 있습니다.

LEFT의 경우 ANSI 외부 조인을 통해 보고 싶은 데이터가 있는 테이블을 왼쪽에 있을 때 사용합니다.

내부 조인을 하였을 때 보이지 않았던 데이터가 외부 조인을 하였을 때 보이시는 것을 보실 수 있습니다.

이 이유는 위에서 외부 조인을 설명했을 때와 같은 이유인데요.(NULL인 데이터)

이 때 이 데이터는 EMPLOYEES 테이블에만 있는 데이터입니다.

이 데이터를 보기 위해 FROM 절에 lEFT OUTER JOING을 이용하여 출력하였습니다.

 

그럼 반대로 RIGHT를 하게되면 어떻게 될까요?

RIGHT OUTER JOIN을 하게 되면 이젠 보이지 않았던 DEPARTMENTS 테이블에 있는 데이터가 보이게 됩니다.

 

마지막으로 FULL OUTER JOIN을 사용하게 되면 양쪽의 데이터가 모두 출력되게 됩니다.

EMPLOYEES의 107번 째의 데이터와 DEPARTMENTS의 16개의 데이터를 보실 수 있습니다.

FULL OUTER JOIN은 일반 외부 조인에서는 지원을 하지 않고 ANSIN 외부 조인에서만 지원하는 문법입니다.

 

이렇게 조인에 대해 알아봤습니다.

어떤 조인을 사용하실 지는 상황에 맞게 사용하시면 될 것으로 보입니다.

만약, 다른 DBMS화 호환성을 생각하여 조인을 사용하신다면 ANSI 조인을 사용하시기 바랍니다.