6. [Oracle] 조인을 사용하여 여러 테이블의 데이터 표시해보자.

-여러 테이블에서 데이터 가져오기

때때로 두 개 이상의 테이블에서 데이터를 사용해야 할 경우가 있습니다. 슬라이드 예제에서는 별도의 두 테이블에서 가져온 데이터가 보고서에 표시됩니다.

• 사원 ID는 EMPLOYEES 테이블에 있습니다.

• 부서 ID는 EMPLOYEES 테이블과 DEPARTMENTS 테이블에 모두 있습니다.

• 부서 이름은 DEPARTMENTS 테이블에 있습니다.

이 보고서를 작성하려면 EMPLOYEES 및 DEPARTMENTS 테이블을 연결하고 두 테이블에서 데이터에 액세스해야 합니다.

 

-natural join 생성

• NATURAL JOIN 절은 이름이 같은 두 테이블의 모든 행을 기반으로 합니다.

• 이 절은 두 테이블에서 대응되는 모든 열의 값이 동일한 행을 선택합니다.

• 동일한 이름을 가진 열이 서로 다른 데이터 유형을 가지면 오류가 반환됩니다.

 

두 테이블에서 데이터 유형과 이름이 일치하는 열을 기반으로 자동으로 테이블을 조인할 수 있습니다. 이 작업은 NATURAL JOIN 키워드를 사용하여 수행합니다. 참고: 조인은 두 테이블의 이름과 데이터 유형이 동일한 열에서만 발생합니다. 열 이름은 같지만 데이터 유형이 다를 경우 NATURAL JOIN 구문에서 오류가 발생합니다.

 

SELECT department_id, department_name, location_id, city

FROM departments NATURAL JOIN locations ;

 

예제에서 LOCATIONS 테이블은 두 테이블에서 유일하게 이름이 같은 열인 LOCATION_ID 열에 의해 DEPARTMENT 테이블에 조인됩니다. 공통되는 다른 열이 있을 경우 모두 조인에 사용됩니다.

 

-using 절로 조인

• 여러 열이 이름은 같지만 데이터 유형은 다를 경우 USING 절을 사용하여 Equijoin에 대한 열을 지정할 수 있습니 다.

• USING 절을 사용하면 두 개 이상의 열이 일치하는 경우 하나의 열만 일치하도록 할 수 있습니다.

• NATURAL JOIN과 USING 절은 상호 배타적입니다.

 

Natural join은 이름과 데이터 유형이 대응되는 모든 열을 사용하여 테이블을 조인합니다. USING 절을 사용하면 equijoin에 사용될 열만 지정할 수 있습니다.

 

-USING 절을 사용하여 레코드 검색

SELECT employee_id, last_name, location_id, department_id

FROM employees JOIN departments USING (department_id) ;

 

예제에서는 EMPLOYEES 및 DEPARTMENTS 테이블의 DEPARTMENT_ID 열이 조인되어 사원이 근무하는 부서의 LOCATION_ID가 표시됩니다.

 

-USING 절에 테이블 alias 사용

• USING 절에 사용되는 열을 한정하지 마십시오.

• 동일한 열이 SQL 문의 다른 곳에서 사용되는 경우 alias를 지정하지 마십시오.

 

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400;

 

USING 절을 사용하여 조인을 수행할 때 USING 절 자체에서 사용되는 열을 한정할 수 없습니다. 또한 해당 열이 SQL 문의 임의 위치에서 사용되는 경우 alias를 지정할 수 없습니다. 예를 들어 언급된 query에서 location_id 열이 USING 절에 사용되므로 WHERE 절의 location_id 열에 alias를 지정하면 안됩니다.

 

USING 절에서 참조되는 열은 SQL 문의 어느 위치에도 수식자(테이블 이름 또는 alias)를 포함해서는 안됩니다. 예를 들어, 다음 명령문은 유효합니다.

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400;

 

USING 절에 사용되지 않지만 두 테이블에 공통인 다른 열에는 테이블 alias를 접두어로 사용해야 하며 그렇지 않으면 "column ambiguously defined"라는 오류가 발생합니다.

 

-ON 절로 조인

• Natural Join의 조인 조건은 기본적으로 이름이 같은 모든 열의 equijoin입니다.

• ON 절을 사용하여 임의 조건을 지정하거나 조인할 열을 지정합니다.

• 조인 조건은 다른 검색 조건과는 별개입니다.

• ON 절을 사용하면 코드를 이해하기 쉽습니다.

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id);

이 예제에서 EMPLOYEES 및 DEPARTMENTS 테이블의 DEPARTMENT_ID 열은 ON 절을 사용하여 조인됩니다. EMPLOYEES 테이블의 부서 ID가 DEPARTMENTS 테이블의 부서 ID와 같은 경우 항상 행이 반환됩니다 . 일치하는 column_name을 한정하려면 테이블 alias가 필요합니다.

 

또한 ON 절을 사용하여 서로 다른 이름을 가진 열을 조인할 수 있습니다. 슬라이드 예제의 (e.department_id = d.department_id)처럼 조인된 열을 괄호로 둘러싸는 것은 선택 사항입니다. 따라서 ON e.department_id = d.department_id도 제대로 작동합니다.

 

-ON 절을 사용하여 3-Way 조인

SELECT employee_id, city, department_name

FROM employees e JOIN departments d ON d.department_id = e.department_id

JOIN locations l ON d.location_id = l.location_id;

 

3-way 조인이란 세 개의 테이블을 조인하는 것을 말합니다. SQL:1999 호환 구문에서 조인은 왼쪽에서 오른쪽으로 수행됩니다. 따라서 가장 먼저 수행되는 조인은EMPLOYEES JOIN DEPARTMENTS입니다. 첫번째 조인 조건은 EMPLOYEES 및 DEPARTMENTS의 열을 참조할 수 있지만 LOCATIONS의 열은 참조할 수 없습니다. 두번째 조인 조건은 세 개의 테이블에서 모두 열을 참조할 수 있습니다.

 

-조인에 추가 조건 적용

AND 절 또는WHERE 절을 사용하여 추가 조건을 적용합니다.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id)

AND e.manager_id = 149 ;

 

또는

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id)

WHERE e.manager_id = 149 ;

 

조인에 추가 조건을 적용할 수 있습니다. 위의 예제는 EMPLOYEES 및 DEPARTMENTS 테이블에서 조인을 수행하고 관리자 ID가 149인 사원만 표시합니다. ON 절에 다른 조건을 추가하려면 AND 절을 사용하면 됩니다. 또는 WHERE 절을 사용하여 추가 조건을 적용할 수 있습니다.

 

-SELF JOIN

때때로 테이블을 자체 조인해야 할 경우가 있습니다. 각 사원의 관리자 이름을 찾으려면 EMPLOYEES 테이블을 자체 조인하거나 Self Join을 수행해야 합니다.

 

관리자 이름을 찾으려면 다음을 수행해야 합니다.

• EMPLOYEES 테이블에서 LAST_NAME 열을 조사하여 Lorentz를 찾습니다.

• MANAGER_ID 열을 조사하여 Lorentz의 관리자 번호를 찾습니다. Lorentz의 관리자 번호는 103입니다.

• LAST_NAME 열을 조사하여 EMPLOYEE_ID가 103인 관리자의 이름을 찾습니다. Hunold의 사원 번호가103이므로 Hunold가 Lorentz의 관리자입니다.

 

과정에서 EMPLOYEES 테이블을 두 번 조사하게 됩니다. 먼저 테이블을 조사하여 LAST_NAME 열에서 Lorentz를 찾고MANAGER_ID 값 103을 찾습니다. 이어서 EMPLOYEE_ID 열을 조사하여 103을 찾고LAST_NAME 열에서 Hunold를 찾습니다.

 

-on절을 사용하는 self join

SELECT worker.last_name emp, manager.last_name mgr

FROM employees worker JOIN employees manager

ON (worker.manager_id = manager.employee_id);

 

ON 절은 동일한 테이블이나 다른 테이블에서 서로 다른 이름을 가진 열을 조인하는 데도 사용할 수 있습니다.

위의 예제는 EMPLOYEE_ID 및 MANAGER_ID 열을 기반으로 하는 EMPLOYEES 테이블의 Self Join입니다.

 

-Nonequijoin

Nonequijoin은 등호 연산자 외의 다른 연산자를 포함하는 조인 조건입니다.

Nonequijoin의 한 예로EMPLOYEES 테이블과 JOB_GRADES 테이블 사이의 관계를 들 수 있습니다. EMPLOYEES 테이블의 SALARY 열은 JOB_GRADES 테이블의 LOWEST_SAL 열에 있는 값과 HIGHEST_SAL 열에 있는 값 사이의 범위에 해당하는 값을 가집니다. 따라서 급여를 기준으로 각 사원의 등급을 지정할 수 있습니다. 관계는 등호(=) 연산자가 아닌 다른 연산자를 사용하여 구합니다.

 

-Nonequijoin을 사용하여 레코드 검색

SELECT e.last_name, e.salary, j.grade_level

FROM employees e JOIN job_grades j

ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 

예제에서는 사원의 급여 등급을 평가하는 Nonequijoin을 생성합니다. 급여는 낮은 급여 범위와 높은 급여 범위 쌍(pair) 사이에 있어야 합니다. 이 query가 실행될 때 각 사원은 한 번만 나타납니다. 리스트에서 반복되는 사원은 없습니다.

 

여기에는 다음과 같은 두 가지 이유가 있습니다.

• JOB_GRADES 테이블의 어떠한 행도 중복되는 등급을 포함하지 않습니다. 즉, 한 사원에 대한 급여 값은 급여 등급 테이블의 한 행에서 낮은 급여 값과 높은 급여 값 범위에만 속할 수 있습니다.

• 모든 사원의 급여는 직무 등급 테이블에서 제공하는 한계 내에 속합니다. 즉, LOWEST_SAL 열에 포함된 최저값보 다 적은 급여를 받거나 HIGHEST_SAL 열에 포함된 최고값보다 많은 급여를 받는 사원은 없습니다.

 

-outer join

조인 조건을 충족하지 못하는 행은 query 결과에 나타나지 않습니다. 사원이 없는 부서 레코드 또는 부서가 할당되지 않은 사원을 반환하려면 OUTER Join을 사용하면 됩니다.

 

-INNER Join과 OUTER Join

• INNER Join의 결과는 물론 , 왼쪽(또는 오른쪽) 테이블의 일치하지 않는 행도 반환하는 두 테이블 간의 조인을 Left(또는 Right) OUTER Join이라고 합니다.

• INNER Join의 결과는 물론, Left 및 Right OUTER Join의 결과를 반환하는 두 테이블 간의 조인을 Full OUTER Join이라고 합니다.

 

NATURAL JOIN, USING 또는 ON 절을 사용하여 테이블을 조인하면 결과는 INNER Join이 됩니다. 일치하지 않는 행은 출력에 표시되지 않습니다. 일치하지 않는 행을 반환하려면 OUTER Join을 사용하면 됩니다. OUTER Join은 조인 조건을 만족하는 모든 행을 반환하며, 한 테이블의 행이 다른 테이블의 어떤 행과도 조인 조건을 만족하지 않는 경우 테이블의 일부 또는 전체 행을 반환합니다.

 

OUTER Join에는 다음 세 가지 유형이 있습니다.

• LEFT OUTER

• RIGHT OUTER

• FULL OUTER

 

-LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

DEPARTMENTS 테이블에 대응되는 행이 없어도 왼쪽 테이블인 EMPLOYEES 테이블의 모든 행을 검색합니다.

 

-RIGHT OUTER JOIN

SELECT e.last_name, d.department_id, d.department_name

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

이 query는 EMPLOYEES 테이블에 대응되는 행이 없어도 오른쪽 테이블인 DEPARTMENTS 테이블의 모든 행을 검색합니다.

 

-FULL OUTER JOIN

SELECT e.last_name, d.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

이 query는 DEPARTMENTS 테이블에 대응되는 행이 없어도 EMPLOYEES 테이블의 모든 행을 검색합니다. 또한 EMPLOYEES 테이블에 일치하는 행이 없어도 DEPARTMENTS 테이블의 모든 행을 검색합니다.