Outer Join에 주는 조건과 일반조건 실행순서 주의

EMP와 DEPT 두개의 테이블을 보면

1) EMP 테이블에 급여(SAL)가 3000 이상인 사람은 KING 한명뿐이다

2) DEPT에는 아무도 근무하지 않는 부서번호 40번이 있다 

 

 

 

CASE 1. DEPT랑 EMP테이블(에서 급여 3000 넘는 건만) Left Outer Join 할래

 

※ emp 에서 급여 3000 넘는 king 만 조인에 참여했다.

※ 일반 조건 (emp에서 급여 3000 넘는) 먼저, join 나중에 실행

 

위 ANSI 구문을 ORACLE로 옮기면 이렇게 된다.

조인에 달린 일반조건인 (+) 붙은 AND절을 먼저 처리하고 나서 조인을 처리하는 순서로 실행된다. 

---

(주의) ANSI에서 WHERE에 조건 주거나, ORACLE에서 (+)을 빼면 이런 결과가 나온다 

ANSI에서 JOIN절에 달려야 하는 조건을 WHERE에 달아버리거나

오라클에서 (+) 빼먹고 그냥 조건을 달아버리거나..

---

 

 

CASE 2. CASE 1을 시도하였으나 뭔가 다른 결과가 나와버림 

--ANSI 일반조건 → 조인조건
SELECT * 
  FROM DEPT D LEFT OUTER JOIN EMP  E 
    ON E.DEPTNO = D.DEPTNO
   AND E.SAL    > 3000 ; 
 
 ------------------------------------------------------
 --ANSI 조인조건 → 일반조건
 SELECT * 
  FROM DEPT D LEFT OUTER JOIN EMP  E 
    ON E.DEPTNO = D.DEPTNO
 WHERE SAL   > 3000 
    OR SAL IS NULL ;

위에서 봤던 테이블을 그대로 만들려면 이렇게 문장을 써도 될 것 같은데 결과는 이렇게 나온다.

FROM절에서 이미 조인을 끝마치고, 그 다음 WHERE절에서 조건을 주기 때문에 

(Outer Join의 결과로 나온 null이 아니라) 데이터 자체가 null이었던 40번 부서 & 급여 5000이 넘는 KING의 데이터만이 출력된다 

 

오라클 문법도 마찬가지다

--ORACLE 일반조건 → 조인조건
 SELECT * 
  FROM DEPT D 
      ,EMP  E 
 WHERE E.DEPTNO (+) = D.DEPTNO
   AND E.SAL    (+) > 3000; 
 
------------------------------------------------------
--ORACLE2 조인조건 → 일반조건
SELECT * 
  FROM DEPT D 
      ,EMP  E 
 WHERE E.DEPTNO (+) = D.DEPTNO
   AND (E.SAL   > 3000 OR E.SAL IS NULL); 

------------------------------------------------------
--ORACLE3 조인조건 → 일반조건 (2와 같은 결과)
SELECT * 
  FROM (SELECT D.DEPTNO AS DEPTID, D.DNAME, D.LOC, E.*
          FROM DEPT D 
              ,EMP  E 
         WHERE E.DEPTNO (+) = D.DEPTNO)
 WHERE SAL   > 3000 
    OR SAL IS NULL ;

 

 

 

솔직히 정리하면서도 너무 헷갈린다 ^_^ !

 

조인 대상에 조건을 주고 싶다면 

1. ANSI에서는 FROM 절에 AND를 달아서 줄 것 (WHERE에 주면 안됨)

2. ORACLE에서는 조건에 (+) 붙일 것