오라클 SQL과 PL/SQL ( OUTER JOIN 주의사항 / SELF JOIN )

ORACLE OUTER JOIN 주의사항

오라클 아우터 조인은 WHERE절의 아우터 조인이 되는 컬럼들에 대해서는 전부 아우터 조인 연산자(+)를 붙여야 합니다.
만약 하나라도 빠진다면 결과는 일반 조인과 동일하게 되어 원하지 않는 데이터를 추출하게 됩니다.

만약 부서에 대한 정보를 모두 보여주고 부서 번호가 20인 사원의 사원번호 이름 급여를 보여주는 예제를 본다면
사원 테이블의 조인 유무와 상관없이 부서정보를 보여주기 위해서는 아우터 조인을 사용해야 합니다.
기준이 되는 테이블이 부서 테이블이므로 WHERE절에 사원테이블의 모든 컬럼에 아우터 조인 연산자를 붙입니다.

 

SELECT D.DEPTNO, D.DNAME, D.LOC, E.EMPNO, E.ENAME, E.SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO (+)
AND E.DEPTNO = 20;

분명 부서 정보는 다 추출하려고 했는데 부서번호가 20인 사원 테이블과 조인이되는 정보만 추출하였습니다.
위 SQL의 문제는 WHERE조건절에서 아우터 조인이 되는 컬럼 E.DEPTNO에 (+)연산자가 빠졋다는 것입니다.

 

SELECT D.DEPTNO, D.DNAME, D.LOC, E.EMPNO, E.ENAME, E.SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO (+)
AND E.DEPTNO(+) = 20;

 

다시한번 정리하면 WHERE절에 아우터 조인이 되는 컬럼들에 대해 모두 아우터 연산자 (+)를 붙여야 정확한 데이터를 추출할 수 있습니다.

 

ANSI OUTER JOIN의 주의사항

직업이 CLERK인 사원의 정보(사원번호, 이름, 직업)을 출력하고 그중에 'CHICAGO'에 위치한 부서에 소속된 사원의 부서 정보(부서번호, 부서명, 위치)를 출력하세요

ANSI 아우터 조인의 LEFT OUTER JOIN을 사용한다면 EMP 테이블이 기준 테이블이 됨으로 FROM EMP E LEFT OUTER JOIN DEPT D와 같이 사용할 것입니다.

 

SELECT E.EMPNO , E.ENAME , E.JOB, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO 
AND D.LOC = 'CHICAGO'
WHERE E.JOB = 'CLERK';

 

만약에 WHERE절에 있는 조건을 AND로 바꿔서 ON절에 위치시킨다면 결과는 어떻게 될까요 ?

 

SELECT E.EMPNO , E.ENAME , E.JOB, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO 
AND D.LOC = 'CHICAGO'
AND E.JOB = 'CLERK';

ON절에 아우터 조인이 되는 조건절을 기술한다고 하였는데 기준이 되는 EMP테이블에서 조건에 맞는 집합을 추출한 후 그 중에서 OUTER조인 조건을 만족하는 집합만 아우터 조인을 하는 것입니다.
WHERE 절에는 아무 조건이 없음으로 EMP 전체 데이터가 출력되고 그 중에서 아우터 조인 대상 집합은 E.JOB = 'CLERK'를 만족하는 집합입니다.
JOB이 CLERK인 사원이 소속된 부서가 CHICAGO에 위치한 사원들만 부서 정보를 보여줍니다.

정리하면 ANSI 아우터 조인에서 WHERE절에 기술한 조건은 기준 테이블의 집합의 수를 결정하고 ON절에 기술한 조건은 기준 집합중에 아우터 조인 대상이 되는 집합을 말하는 것으로 전체 결과 집합에는 아무런 영향을 주지 않습니다.

 

 

SELF 조인
만약 JOIN을 해야 했는데 원하는 데이터가 하나의 테이블에 다 들어 있는 경우는 어떻게 할까요 ?

EMP테이블의 예를 들어보겠습니다.

SELECT EMPNO, ENAME, MGR FROM EMP;

 

EMPNO는 사원번호 MGR은 상사의 사원번호 입니다.
출력하고 싶은 형태가 SMITH의 상사는 FORD이다 일 경우 출력에 필요한 데이터는 EMP테이블에 다 있습니다.
이럴 경우 아주 요긴하게 사용하는 것이 SELF 조인입니다.

SLEF조인의 원리는 우선 데이터를 가지고 있는 하나의 테이블을 메모리에서 별명으로 두개로 사용해서 호출하는 방법으로 2개의 테이블을 만든 후 일반적인 JOIN을 작업합니다.

 

ORACLE

SELECT E1.ENAME ENAME, E2.ENAME MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

ANSI

SELECT E1.ENAME "ENAME", E2.ENAME "MGR_ENAME"
FROM EMP E1 JOIN EMP E2
ON E1.MGR = E2.EMPNO;

위 쿼리에서 처럼 2EMP 테이블의 별명을 E1, E2 로 두번사용하게 되면 메모리에서 오라클이 이 테이블을 별명을 다르게 해서 복사를 하게 됩니다.