SQL 5-11. 포괄 조인
관련링크
본문
5-12. 포괄 조인
포괄 조인을 사용하여 일치하는 항목이 없는 레코드 반환
행이 조인 조건을 만족하지 않으면 질의 결과에 나타나지 않는다.
예제) 등가조인
오라클 디비 예제 :
SELECT e.last_name, e.department_id, d.department_name
FROM employyees e, departments d
WHERE e.department_id = d.department_id;
DEPARTMENTS테이블의 등가 조인 조건에서 사원 Grant의 부서ID가 EMPLOYEES테이블에 기록되어 있지 않기 때문에
Grant는 나타나지 않는다.
결과 집합에는 20명의 사원 레코드가 표시되지 않고 19명의 사원 레코드가 표시됩니다. (등가 조인)
포괄 조인 구문
․ 포괄 조인을 사용하면 조인 조건을 만족하지 않는 행도 볼 수 있습니다.
․ 포괄 조인 연산자는 더하기 기호( + )입니다.
예제)
오라클 디비 예제 :
SELECT table1.column, table2.column
FROM table1, table2
WHERH table1.column(+) = tabel2.column;
오라클 디비 예제 :
SELECT table1.column, table2.column
FROM table1, table2
WHERH table1.column1 = tabel2.column(+);
☞설명)
조인 조건에 포괄 조인 연산자를 사용하면 누락된 행을 반환할 수 있다.
이 연산자는 더하기 기호를 괄호로 묶어(+)표시하며 정보가 부족한 조인“옆”에 넣는다.
이 연산자는 하나 이상의 널 행을 생성하는데 이러한 널 행은 완전한 테이블의 하나 이상의 행과 조인할 수 있다.
구문 설명 :
table1.column = 테이블을 조인하거나 관련시키는 조건입니다.
table2.column (+) 포괄 조인 기호로서 WHERE절 조건의 한 쪽에만 넣을 수 있으며 양쪽에 넣을 수 없다.
(일치하는 행이 없는 테이블에서는 열 이름 다음에 포괄 조인 기호를 넣습니다.)
예제) 포괄 조인 사용
오라클 디비 예제 :
SELECT e.last_name, e.department_id,
d.department_name
FROM employees e, departments d
WHERH e.department_id(+) = d.department_id;
☞설명)
예제는 사원의 이름,부서ID및 부서 이름을 표시합니다. Contracting부서에는 사원이 없으므로 출력 결과에 공백 값이 표시된다.
MSSQL 디비 예제 :
sp_dbcmptlevel pubs, 80
select employee.emp_id,employee.lname,
employee.pub_id ,
pub_info.pub_id,
pub_info.pr_info
FROM employee , pub_info
WHERE employee.pub_id *= pub_info.pub_id
--부서가 있고 사원과 사원이 있는 부서를 출력하는데 부서가 없는 사원을 더 출력한다.
--출력되지 않은쪽 테이블의 컬럼은 NULL로 출력된다.
select employee.emp_id,employee.lname,
employee.pub_id ,
pub_info.pub_id,
pub_info.pr_info
FROM employee , pub_info
WHERE employee.pub_id =* pub_info.pub_id
--부서가 있고 사원과 사원이 있는 부서를 출력하는데 사원이 없는 부서를 더 출력한다.
포괄 조인 제한 사항
․ 포괄 조인 연산자는 표현식의 한 쪽
즉, 정보가 누락된 쪽에만 표시할 수 있으며 상대편 테이블과 직업 일치하는 행이 없는데 테이블의 행을 반환한다.
․ 포괄 조인을 포함하는 조건은 IN연산자를 사용할 수 없으며 OR연산자를 사용해 다른 조건에 링크할 수 없다.
① 내부 조인(inner join)과 포괄 조인(outer join) 비교
․ SQL : 1999에서 두 테이블을 조인해서 일치하는 열만 반환하는 조인이 내부 조인이다.
․ 두 테이블을 조인해서 내부 조인의 결과와 함께 일치하지 않는 왼쪽(또는 오른쪽)테이블의 행을 반환하는 조인이 왼쪽
(또는 오른쪽)포괄 조인이다.
․ 두 테이블을 조인해서 내부 조인의 결과와 함께 왼쪽 및 오른쪽 조인의 결과를 반환하는 조인이 전체 포괄 조인이다.
예제) 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테이블의 모든 행을 검색한다.
이 질의는 이전 릴리스에서는 다음과 같이 작성되었다.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;
MSSQL 디비 예제 :
select employee.emp_id,employee.lname, employee.pub_id, pub_info.pub_id, pub_info.pr_info
FROM employee LEFT OUTER JOIN pub_info
ON employee.pub_id = pub_info.pub_id
예제) RIGHT OUTER JOIN
오라클 디비 예제 :
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
☞설명)
이 질의는 EMPLOYEES테이블에 일치하는 행이 없어도 오른쪽 테이블인 DEPARTMENTS테이블의 모든 행을 검색한다.
이 질의는 이전 릴리스에서 다음과 같이 작성된다.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);
MSSQL 디비 예제 :
select employee.emp_id,employee.lname, employee.pub_id, pub_info.pub_id, pub_info.pr_info
FROM employee RIGHT OUTER JOIN pub_info
ON employee.pub_id = pub_info.pub_id
예제) FULL OUTER JOIN
오라클 디비 예제 : SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
☞설명)
이 질의는 DEPARTMENTS테이블에 일치하는 행이 없어도 EMPLOYEES테이블의 모든 행을 검색한다.
또한 EMPLOYEES테이블에 일치하는 행이 없어도 DEPARTMENTS테이블의 모든 행을 검색한다
.
MSSQL 디비 예제 :
select employee.emp_id,employee.lname, employee.pub_id, pub_info.pub_id, pub_info.pr_info
FROM employee FULL OUTER JOIN pub_info
ON employee.pub_id = pub_info.pub_id
예제) 조건 추가
오라클 디비 예제 : SELECT e.employee_id, e.last_name, e.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager id = 149;
☞설명) 추가 조건 적용
WHERE절에 추가 조건을 적용할 수 있다.
예제는 EMPLOYEES 및 DEPARTMENTS테이블에 대해 조인을 수행하고 관리자ID가 149인 사원만 표시한다.