[Oracle] 오라클 아우터 조인 (외부조인) (Outer Join, Ansi Join)

참조: https://gent.tistory.com/289

 

--------------------------------------------
-- 오라클 조인 - 예제 1  
--------------------------------------------
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   deptno, 10000 locno, 1 SORT FROM dual UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   deptno, 10002 locno, 2 SORT FROM dual UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   deptno, 10001 locno, 3 SORT FROM dual UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   deptno, 10003 locno, 4 SORT FROM dual UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL deptno, 10000 locno, 5 SORT FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 10001 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 10002 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 10003 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 10004 locno, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.deptno = b.deptno(+)
 ORDER BY a.sort
;

--------------------------------------------
-- 오라클 조인 - 예제 2 
--------------------------------------------
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   DEPTNO, 1 SORT FROM DUAL UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   DEPTNO, 2 SORT  FROM DUAL UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   DEPTNO, 3 SORT  FROM DUAL UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   DEPTNO, 4 SORT  FROM DUAL UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL DEPTNO, 5 SORT  FROM DUAL
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 'NEW YORK' loc, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 'DALLAS'   loc, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 'CHICAGO'  loc, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 'BOSTON'   loc, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.deptno = b.deptno(+)
   AND a.locno  = b.locno(+)
 ORDER BY a.sort
;

--------------------------------------------
-- 오라클 조인 - 예제 3
-------------------------------------------- 
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   deptno, 10000 locno, 1 SORT FROM dual UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   deptno, 10002 locno, 2 SORT FROM dual UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   deptno, 10001 locno, 3 SORT FROM dual UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   deptno, 10003 locno, 4 SORT FROM dual UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL deptno, 10000 locno, 5 SORT FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 10001 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 10002 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 10003 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 10004 locno, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.deptno   = b.deptno(+)
   AND a.locno    = b.locno(+)
   AND b.useyn(+) = 'Y'
 ORDER BY a.sort 
;

--------------------------------------------
-- ANSI JOIN - 예제 1 
--------------------------------------------
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   deptno, 10000 locno, 1 SORT FROM dual UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   deptno, 10002 locno, 2 SORT FROM dual UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   deptno, 10001 locno, 3 SORT FROM dual UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   deptno, 10003 locno, 4 SORT FROM dual UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL deptno, 10000 locno, 5 SORT FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 10001 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 10002 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 10003 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 10004 locno, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a LEFT OUTER JOIN dept b
    ON a.deptno = b.deptno 
 ORDER BY a.sort
;

--------------------------------------------
-- ANSI JOIN - 예제 2 
--------------------------------------------
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   deptno, 10000 locno, 1 SORT FROM dual UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   deptno, 10002 locno, 2 SORT FROM dual UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   deptno, 10001 locno, 3 SORT FROM dual UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   deptno, 10003 locno, 4 SORT FROM dual UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL deptno, 10000 locno, 5 SORT FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 10001 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 10002 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 10003 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 10004 locno, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a LEFT OUTER JOIN dept b
    ON (a.deptno = b.deptno AND a.locno = b.locno) 
 ORDER BY a.sort
;

--------------------------------------------
-- ANSI JOIN - 예제 3
--------------------------------------------
WITH emp AS (
  SELECT 7839 EMPNO, 'KING'   ename, 'PRESIDENT' job, 90   deptno, 10000 locno, 1 SORT FROM dual UNION ALL
  SELECT 7566 EMPNO, 'JONES'  ename, 'MANAGER'   job, 20   deptno, 10002 locno, 2 SORT FROM dual UNION ALL
  SELECT 7788 EMPNO, 'SCOTT'  ename, 'ANALYST'   job, 10   deptno, 10001 locno, 3 SORT FROM dual UNION ALL
  SELECT 7654 EMPNO, 'MARTIN' ename, 'SALESMAN'  job, 30   deptno, 10003 locno, 4 SORT FROM dual UNION ALL
  SELECT 7900 EMPNO, 'JAMES'  ename, 'CLERK'     job, NULL deptno, 10000 locno, 5 SORT FROM dual
), dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname, 10001 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname, 10002 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname, 10003 locno, 'Y' useyn FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname, 10004 locno, 'N' useyn FROM dual
)

SELECT a.empno
     , a.ename
     , a.job
     , a.deptno
     , b.dname
  FROM emp a LEFT OUTER JOIN dept b
    ON (a.deptno = b.deptno AND a.locno = b.locno AND b.useyn = 'Y') 
 ORDER BY a.sort