[펌] in 과 not exists 차이

[펌] http://cafe.naver.com/prodba/9145

not in 과 not exists 차이점 / 부정형을 긍정으로 변경할때

일반적으로 대용량을 처리할때 성능상의 이유로 Not Exists로 되어 있는 것을

Not In으로 바꿔서 Hash나 Merge Anti Join으로 유도하는 경우가 있는데

이는 Not Exists와 Not In의 관계가 "="이 성립한다는 전제 조건에서 이루어 진다.

하지만 모든경우 Not Exists와 Not In의 관계가 "=" 성립하는지에 대한 의문에서

아래와 같이 테스트를 해보았습니다.

<결론>
   Not In 과 Not Exists는 다르다.
    + Not IN과 Not Exists의 차이점은 연결고리가 되는 컬럼의
      값중 null값을 처리하는 부분에서 차이가 난다.

<이유>
    + Not In 은 where 절의 조건이 만족하더라도
      연결고리 컬럼이 Null값을 가진 다면 결과에서 무조건 제외 된다.

    + Not Exists는 Not In과 달리 Null값을 가진 row들도 결과에 포함된다.

    + 간단히 생각해보면 in은 조건에 만족하는 row를 찾는 것이고
      exists는 exists이하 절이 true인지 아닌지를 체크하는 것이기 때문에
      연결고리 컬럼의 값이 null값을 가질때 null은 조인에 참여하지 못하기 때문에
      in은 조건에 만족하는 것을 찾을 수 없는 것이고
      exists는 false의 값을 return한다.
     
      따라서 연결고리 값이 null값을 가질때
      Not in은 조인 연산을 하지 않기 때문에 결과에서 제외되며
      Not Exists는 exists이하의 절이 false를 리턴하고 거기에 대한 Not이기 때문에
      결과적으로 true가 되어 결과에 포함된다.

<참고사항>

  1) 부정형을 긍정으로 변경할 때

    + Not In일 때는 상관 없지만 Not Exists일때는
      논리적으로 부정형을 긍정으로 변경 한 후 연결고리가 되는 컬럼이 null값 가질 경우에
      대해서 반드시 True가 되도록 처리해 줘야 된다.
    + 즉 null 값을 가지는 컬럼에 대해서도 결과에 포함 되도록 해야 된다.

  ex)
      SELECT :current_il,
             A.SNG_NO,
             A.SNG_SEQ,
             A.JI_HANDO,
             A.JI_HAN_BAL,
       FROM IRMS_SNG_MAS A
      WHERE A.WONJ_ST2 = '1'
        AND A.GAGIGONG_GB IN('2','3')
        AND NOT EXISTS(SELECT 'X'
                         FROM IRMS_SNG_MAS G
                        WHERE G.WONJ_ST2 = '1'
                          AND G.GAGIGONG_GB IN('2','3')
                          AND (G.MANGI_IL < :current_il AND NVL(G.SIL_BAL,0) = 0)
                          AND G.SNG_NO = A.SNG_NO
                          AND G.SNG_SEQ = A.SNG_SEQ)

   <부정형을 긍정형으로 변경>

     + MANGI_IL이 NULL값을 가질때 반드시 참이 되도록 해야 됨.
    
      SELECT :current_il,
             A.SNG_NO,
             A.SNG_SEQ,
             A.JI_HANDO,
             A.JI_HAN_BAL,
        FROM IRMS_SNG_MAS A
       WHERE A.WONJ_ST2 = '1'
         AND A.GAGIGONG_GB IN('2','3')
         AND NOT (NVL(A.MANGI_IL,'99991231') < :CURRENT_IL AND NVL(A.SIL_BAL,0) = 0)

<테스트 테이브>
TEST_AJ01
TEST_AJ02

<데이타>

TEST_AJ01
========================
NO        MARRY_DT
-------------------
1        20030405
2        20030405
3        20030405
<null>   <null>
<null>   20030408

TEST_AJ02
========================
NO        MARRY_DT
-------------------
1        20030405
2        20030405
3        20030405
<null>   20030408

# NULL값을 가진 컬럼의 값은 <null>이라고 표현하였음

<테스트 SQL문>
1)
    SELECT *
      FROM TEST_AJ01 A
     WHERE A.NO NOT IN (SELECT NO FROM TEST_AJ02)
  
   ==결과==  -- 0건
   No rows returned

2)
    SELECT *
      FROM TEST_AJ01 A
     WHERE NOT EXISTS (SELECT 'X' FROM TEST_AJ02 B WHERE A.NO = B.NO)

   ==결과==  -- 2건
   <null>    <null>
   <null>    20030408