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

오라클에서 자주 사용되는 작업중 하나가 이 글에서 정리할 JOIN이라는 것인데요. 그동안 하나의 테이블에서 DML 명령어를 이용했지만 이번엔 서로 다른 테이블간에 상호연결을 하는 JOIN에 대해서 공부해보겠습니다. 

조인의 종류가 많기 때문에 여기서는 내부조인과 외부조인을 정리하고, 다음글에서 나머지 조인들을 정리할 예정이고 예제는 Oracle의 기본 사용자인 HR계정을 통해 실습하도록 할게요.

■ 조인 ( JOIN )

1. 조인이란??

→ 서로 다른 테이블간에 설정된 관계가 결합하여 1개 이상의 테이블에서 데이터를 조회하기 위해 사용 됩니다. 이 때 테이블간의 상호 연결을 조인이라고 하는데요. 각각의 테이블에 분리된 연관성 있는 데이터를 연결하거나 조합해야 하는데 이러한 일련의 작업들을 조인이라고 합니다.

2. 종류

→ 내부 조인 ( Inner Join )

→ 외부 조인 ( Outer Join )

→ 크로스 조인 ( Cross Join )

→ 셀프 조인 ( Self - Join )

→ 안티 조인 ( Anti Join )

→ 세미 조인 ( Semi Join )

→ ANSI 조인

2. 구문형식

SELECT 컬럼리스트 FROM 조인대상이 되는 테이블 

          WHERE 조인조건;

※ 컬러명이 사용되는 곳에서는 테이블명, 컬럼명의 형태로 컬럼명의 소속을 지정해 주어야 합니다.

■ 내부조인

1. 내부조인은??

→ 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 방법

2. 내부조인을 사용하는 이유

→ 이렇게만 조회하면 마지막에 부서코드(department_id)만나오고 부서명을 알 수가 없습니다. 해당 부서명을 나타내고 싶은데.. 부서코드에 대한 부서명은 다른 테이블에 존재합니다.

→ 그래서 내부조인을 이용해 두 테이블간의 상호연결 질의에서 employees테이블의 부서ID값과 일치하는 department 테이블의 부서ID값을 찾아 employees테이블에 존재하는 first_name, last_name, email 칼럼의 값들을 나타내고 department 테이블의 department_name값을 나타냅니다. 

→ 또한 나타내고자 하는 컬럼의 소속(테이블 이름)이 명확하지 않을 경우 위 사진과 같이 오류가 발생될 수 있는데요. 각각의 테이블에 독립적으로 존재하는 컬럼의 경우는 생략을 해도 원하는 결과를 얻을 수 있으나 해석의 명확성을 위하여 반드시 다음과 같이 소속을 표현해주는 것이 바람직한 방법입니다. [departments.department_id]

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

→ 다른글의 예제에서도 사용했었지만, FROM절에서 테이블명에 별명을 붙이고 이를 활용하면 좀 더 간략화한 질의가 가능하니 참고하시길 바라고요.

3. 3개의 테이블을 이용한 내부조인

→ 위 예제에서는 사원의 직책이 무엇인지 필요하기 때문에 3개의 테이블 컬럼간에 조인이 필요하였고, employees테이블의 JOB_ID에 따른 jobs테이블의 JOB_TITLE정보를 추출한 것입니다. 원리는 WHERE절에서 두 테이블간의 조인결과를 기준이 되는 테이블로 설정하여 결과를 산출해내고, 이를 중심으로 jobs테이블과 employees테이블 조건을 설정하여 서로 다른 조건간의 결합을 만듭니다. 

4. 조인조건과 함께 사용되는 일반조건이 있을 경우는?? 

→ 일반 조건이 있을 경우.. 조인 조건의 우선순위는 일반조건을 가장 먼저 처리하게 됩니다..

→ 조인 조건에서 동등 연산자( = )를 사용한 조인을 동등조인(Equi Join)이라고 합니다.

① location 테이블의 state_province = 'California' 조건에 만족하는 값을 대상으로

② location_id 값과 같은 값을 가지는 데이터를 departments 테이블에서 찾아 조인을 합니다.

③ 2의 결과와 동일한 employee_id 값이 같은 것을 employees 테이블에서 찾아 조인을 합니다.

④ 3의 결과와 jobs 테이블을 비교하여 조인하고 최종 쿼리 결과를 얻습니다.

⑤ 조인에 사용된 기준 테이블은 일반조건의 locations 테이블입니다.

■ 외부조인

1. 외부조인은??

→ 상호 테이블간에 일치되는 값으로 연결되는 내부조인과는 달리 어느 한 테이블에 공통 컬럼값이 없더라도 해당 로우들이 조회결과에 포함되게 하는 조인이며 조회 조건에서 (+) 기호를 사용하여 조인합니다.

2. 외부조인을 사용하는 이유

→ 내부조인으로 인하여 전체 자료 107개에서.. 하나의 row가 null 값을 가지게 되면 상호 테이블간에 일치하지 않는 자료가 되어 결과에서 누락되는 현상이 일어날 수 있습니다.

→ employees 테이블에는 존재하고 departments 테이블에 존재하지 않는다고 할지라도, employees 테이블을 기준으로 하여 department테이블이 조인에 참여하라는 의미를 부여하기 위하여 where절 조건에서 department_id (+)를 사용합니다.

※ 주의사항 : 외부조인을 사용했더라도 이후에 내부조인을 사용했다면.. 최종적으로 내부조인이 되므로 위 사진과 같은 오류가 생깁니다. 

3. 외부조인시 제약사항

→ 다음은 외부조인의 제약사항이므로 조심해야 될 부분입니다. 위 사진처럼 모든 외부조인 조건에 (+)를 붙여야 하며, 일반조건에도 (+)를 붙이지 않으면 아래와 같이 다른 결과값이 나옵니다.

↓↓↓↓↓↓↓↓

→ 이렇게 반드시 외부조건을 사용하려면 일반조건에도 (+)를 붙여줘야 원하는 결과값을 얻을 수 있습니다. 그 외 여러가지 제약사항을 하나씩 살펴 보도록하겠습니다.

제약사항 ① : 테이블 자신에 (+)를 붙일 수 없습니다.

↓↓↓↓↓↓↓↓

→ 테이블 자신에 (+)를 붙일 수 없으니, 이럴 경우 셀프 조인을 사용합니다.

제약사항 ② : (+)는 컬럼에만 붙일 수 있으며, OR연산자와 같이 사용 불가

제약사항 ③ : 외부조인시 하나의 테이블은 하나의 다른 테이블과 포괄조인 X

제약사항 ④ : 외부조인시 서브쿼리와 같이 사용불가

제약사항 ⑤ : 조인의 조건을 양쪽에 붙인경우 오류 발생

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

4. 예제 

Q) 모든 사원을 조회하는데 있어서 직책 변동 기록이 있는 사원이 있다면 그 변동내역까지 조회하는 쿼리를 작성.

   ※ 직책변동 기록이 없는 사원(기록변동값이 Null)도 있으므로 외부 조인을 사용해야함

Q) 현재부서와 직책변동 부서가 같은 사원인 경우 직책 변동내역을 출력.

  ※ 현재 부서에서 다른부서로 직책이 변경된 경우는 제외

▼ 관련글

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) 정리