Oracle - Self Join, Outer Join

 

 

Self Join

동일한 테이블을 두 번 사용하여 자신과 조인하는 것입니다. 같은 테이블의 서로 다른 행을 비교하거나 연관시키고자 할 때 사용됩니다. Self Join은 기본적으로 테이블을 두 번 참조하여, 각 테이블을 별칭(alias)으로 구분합니다.

 

 

<예시>

SELECT A.ENAME AS EMPLOYEE, B.ENAME AS MANAGER
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO;

 

설명: 이 예제는 EMP 테이블에서 직원(EMPLOYEE)과 그들의 관리자(MANAGER)를 쌍으로 묶어 보여줍니다. 여기서 A와 B는 EMP 테이블의 두 가지 별칭입니다. A.MGR = B.EMPNO는 직원의 관리자 번호(MGR)와 다른 직원의 사원 번호(EMPNO)를 비교하여 두 데이터를 연결합니다.

 

SELECT a1.EMPNO , a1.ENAME, a2.EMPNO  
FROM emp a1, emp a2 
WHERE a1.MGR = a2.EMPNO AND a1.ENAME = 'SMITH';

설명:  SMITH 사원의 사원번호, 이름, 직속상관 이름을 가져온다.

a1 : SMITH사원의 정보

a2 : 직속상관의 정보

직속상관의 번호와 그에 해당하는 사원번호를 가지고 있는 컬럼을 조회

 

SELECT a1.DEPTNO, a1.ENAME, a1.JOB
FROM emp a1, emp a2
WHERE a1.MGR = a2.EMPNO AND a2.ENAME = 'FORD';

설명:  FORD사원 밑에서 일하는 사원들의 사원번호, 이름, 직무를 가져온다.

 a1: 부하 직원의 정보

a2: FORD의 정보

 

SELECT a3.EMPNO, a3.ENAME, a3.JOB  
FROM emp a1, emp a2, emp a3
WHERE a1.MGR = a2.EMPNO AND a2.JOB = a3.JOB AND a1.ENAME = 'SMITH';

설명:  SMITH 사원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

a1: SMITH의정보

a2 : SMITH의 직속상관 정보

a3 : 직속상관과 동일한 직무를 가지고 있는 사원들의 정보

 

 

Outer Join

두 테이블 간의 조인에서 한쪽 테이블의 모든 행을 포함시키면서, 다른 테이블에 일치하는 값이 없는 경우에도 결과를 반환하도록 하는 조인입니다. Oracle에서는 'LEFT OUTER JOIN' , 'RIGHT OUTER JOIN', 'FULL OUTER JOIN'
을 사용하여 각각 왼쪽, 오른쪽, 또는 양쪽 테이블의 모든 데이터를 포함하는 조인을 수행할 수 있습니다.

 

<예시>

SELECT E.ENAME, D.DNAME
FROM EMP E
LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

설명: 이 쿼리는 EMP 테이블과 DEPT 테이블을 조인하여 직원의 이름(ENAME)과 부서의 이름(DNAME)을 반환합니다. LEFT OUTER JOIN을 사용하여, 부서 정보가 없는 직원도 결과에 포함되며, 이 경우 부서 이름은 NULL로 나타납니다.

 

SELECT d.DNAME, e.EMPNO, e.ENAME, e.SAL 
FROM DEPT d 
LEFT OUTER JOIN EMP e 
ON e.DEPTNO = d.DEPTNO;

 

좌측의 경우 Dept 테이블을 조회 한 결과이고, 우측의 결과는 emp테이블에서 deptno를 조회한 결과이다.

deptno에는 deptno = 40 이 존재하지만 emp테이블에서는 deptno = 40이 존재하지 않는다. 

이러한 경우 LEFT OUTER JOIN을 사용하여 OPERATIONS의 컬럼을 NULL값으로 하여 값을 가져온다.