III. 일반적인 서브쿼리

III. 일반적인 서브쿼리

1. 개요

1) 서브쿼리(IN, EXISTS, ANY, ...)는 Driving Operation(공급자)과 Filter Operation(확인자)으로 나눌 수 있다.

'SELECT * FROM TAB WHERE Z_CODE IN (Other Query);' 일 경우
Driving Operation : 서브쿼리(OTHER QUERY)가 먼저 수행되고 그 결과값을 Main절인 TAB에 공급하는 경우
Filter Operation : MAIN절인 TAB이 먼저 수행되고 그 결과값을 서브쿼리(Other Query)에서 체크로 푸는 경우

2) 서브쿼리를 Driving과 Filter로 사용하는 기준은 어느쪽이 작은 로우의 집합인지 파악

기본적인 접근 방법은 Main절과 서브쿼리절의 집합 크기가 작은쪽을 드라이빙하고 나머지를 액세스하는 방법이다.
예를들어 Main절의 집합 크기가 작다면 Main절을 먼저 액세스하고 서브쿼리를 EXISTS 구문을 사용하여 Filter로 푸는것이 좋고 서브쿼리 집합이 작다면 IN 구문을 사용하여 Driving으로 사용하는 것이 좋다.


-- 1. oracle version
SELECT *
FROM   V$VERSION;

BANNER                                                           
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit 

-- 2. create table & index & statistics 
DROP TABLE TAB_A PURGE;
DROP TABLE TAB_B PURGE;

CREATE TABLE TAB_A AS
SELECT LEVEL CNT,
       MOD(LEVEL, 10) GUBN
FROM   DUAL
CONNECT BY LEVEL <= 100
;

CREATE TABLE TAB_B AS
SELECT LEVEL CNT,
       MOD(LEVEL, 10) GUBN
FROM   DUAL
CONNECT BY LEVEL <= 100
;

CREATE UNIQUE INDEX TAB_A_U1 ON TAB_A (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_A_N1 ON TAB_A (GUBN) COMPUTE STATISTICS;
CREATE UNIQUE INDEX TAB_B_U1 ON TAB_B (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_B_N1 ON TAB_B (GUBN) COMPUTE STATISTICS;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_A', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_B', CASCADE => TRUE);

-- 3-1) Main절에 Unique 조건을 주어 Filter로 푸는 경우를 살펴봄
-- 보는 것처럼 Main절을 1건 읽은 뒤 서브쿼리를 한건만 읽어서 최적으로 액세스 함
SELECT A.*
FROM   TAB_A A
WHERE  A.CNT = 1
AND    EXISTS (SELECT B.GUBN
               FROM   TAB_B B
               WHERE  B.GUBN = A.GUBN)
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          3          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.003          0          3          0          1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   NESTED LOOPS SEMI (cr=3 pr=0 pw=0 time=109 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=75 us)
      1     INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438011)
      1    INDEX RANGE SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=32 us)(Object ID 10438014)
      
-- 3-2) Main절에 Unique 조건을 주어 Filter가 더 ?음에도 불구하고 Driving으로 사용할 경우
-- 서브쿼리를 강제로 Driving하기 위해 QB_NAME 힌트를 사용
-- 보는 것처럼 서브쿼리를 Driving하기 때문에 불필요하게 100건을 읽은 뒤 Main절에서 Filter를 하고 있음 
SELECT /*+ LEADING(B@SUB) */ 
       A.*
FROM   TAB_A A
WHERE  A.CNT = 1
AND    A.GUBN IN (SELECT /*+ QB_NAME(SUB) */ 
                         B.GUBN
                  FROM   TAB_B B
                  WHERE  B.GUBN = A.GUBN)
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.003          0          3          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.004          0          3          0          1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   HASH JOIN  (cr=3 pr=0 pw=0 time=3362 us)
     10    SORT UNIQUE (cr=1 pr=0 pw=0 time=218 us)
    100     INDEX FULL SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=150 us)(Object ID 10438014)
      1    TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=68 us)
      1     INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=36 us)(Object ID 10438011)
      
-- 4-1) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는 경우를 살펴봄
-- 보는 것처럼 서브쿼리를 1건 읽은 뒤 Main절을 10건 읽어서 최적으로 액세스 함
SELECT A.*
FROM   TAB_A A
WHERE  A.GUBN IN (SELECT B.GUBN
                  FROM   TAB_B B
                  WHERE  B.GUBN = A.GUBN
                  AND    B.CNT  = 1)
;                  

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          6          0         10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.004          0          6          0         10

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   NESTED LOOPS  (cr=6 pr=0 pw=0 time=137 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=73 us)
      1     INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438013)
     10    TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=55 us)
     10     INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=242 us)(Object ID 10438012)
;

-- 4-2) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는것이 더 ?음에도 불구하고 Filter로 사용할 경우
-- Main을 강제로 Driving하기 위해 힌트를 사용
-- 보는 것처럼 Main절을 Driving 때문에 불필요하게 100건을 읽은 뒤 서브쿼리에서 Filter를 하고 있음
SELECT /*+ LEADING(A) */
       A.*
FROM   TAB_A A
WHERE  EXISTS (SELECT /*+ NO_UNNEST */
                      B.GUBN
               FROM   TAB_B B
               WHERE  B.GUBN = A.GUBN
               AND    B.CNT  = 1)
;                  

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.004          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.001          0         24          0         10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.010        0.004          0         24          0         10

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   FILTER  (cr=24 pr=0 pw=0 time=197 us)
    100    TABLE ACCESS FULL TAB_A (cr=4 pr=0 pw=0 time=1084 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=20 pr=0 pw=0 time=196 us)
     10     INDEX UNIQUE SCAN TAB_B_U1 (cr=10 pr=0 pw=0 time=107 us)(Object ID 10438013)
;

3) IN이 서브쿼리를 Driving으로, 또는 EXISTS가 서브쿼리를 Filter로 반드시 풀지 않음

일반적으로 서브쿼리에 IN을 사용할 경우 서브쿼리가 Driving이 되고 EXISTS를 사용할경우 Filter로 옵티마이저가 풀지만 반드시 그렇지는 않다.
그러므로 반드시 실행계획을 확인하여 유도하는대로 옵티마이저가 오는지 확인해야 한다.



-- 4-2) 예제 사용
-- EXISTS 구문을 사용하였기 때문에 TAB_A가 Driving이 될 것 같지만 옵티마이저는 CBQT(Cost Based Query Transformation)
-- 를 사용하기 때문에 서브쿼리가 Driving이 되도록 변경하여 플랜을 작성한다.
SELECT A.*
FROM   TAB_A A
WHERE  EXISTS (SELECT B.GUBN
               FROM   TAB_B B
               WHERE  B.GUBN = A.GUBN
               AND    B.CNT  = 1)
;                  

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          6          0         10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0          6          0         10

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   NESTED LOOPS  (cr=6 pr=0 pw=0 time=131 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=74 us)
      1     INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=42 us)(Object ID 10438013)
     10    TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=54 us)
     10     INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=143 us)(Object ID 10438012)

2. 서브쿼리 파라미터

1) 서브쿼리 관련 파라미터의 버전별 변화 과정

이름	8i	9i	10g	설명
_unnest_notexists_sq	n/a	single	n/a	하나 이상의 테이블을 가지는 NOT EXISTS 서브쿼리를 가능하면 UNNEST
_unnest_subqerty	false	true	true	상관 서브쿼리 unnesting을 가능하게 함
_ordered_semi-join	true	true	true	정렬된 세미 조인(EXISTS) 서브쿼리를 가능하게 함
_cost_equality_semi_join	n/a	true	true	등식(=equality) 세미 조인(EXISTS)의 비용계산을 활성화
_always_anti_join	nested_loops	choose	choose	가능하면 anti-JOIN(NOT EXISTS) 방식을 항상 사용
_always_semi_join	standard	choose	choose	가능하면 semi-JOIN(EXISTS) 방식을 항상 사용
_optimizer_correct_sq_selectivity	n/a	n/a	true	서브쿼리 선택도의 정확한 계산을 강제
_optimizer_squ_bottomup	n/a	n/a	true	Bottom-up 방식으로 서브쿼리 unnesting을 가능하게 함
_distinct_view_unnesting	n/a	n/a	false	in subquery를 'select distinct' 뷰로 unnesting 가능하게 함
_right_outer_hash_enable	n/a	n/a	true	right outer(semi와 anti 해시 조인을 포함해서) 해시 조인을 가능하게 함
_remove_aggr_subquery	n/a	n/a	true	포함된 집계 서브쿼리의 제거를 가능하게 함

3. 서브쿼리의 분류

카테고리	특징
상관/비상관	상관 서브쿼리는 outer 쿼리 블록에 있는 컬럼을 참조한다. 상관 서브쿼리는 대개 조인으로 변환될 수 있다. 비상관 서브쿼리는 선행 서브쿼리가 될 가능성이 있다.
단순/복잡	단순한 서브쿼리는 단일 테이블만을 포함한다. 복잡한 서브쿼리는 여러 개의 테이블을 포함하며, 조인형태 또는 서브쿼리 안에 또 다른 서브쿼리를 갖는 형태이다. 서브쿼리가 복잡할 때는 적용되지 않고 단순할 때만 옵티마이저가 수행하는 기능들이 있다.
집계	단순한(단일 테이블) 서브쿼리일지라도 그 안에 어떤 집계연산을 포함한다면 옵티마이저가 그들을 변환하지 못하게 하는 제약이 있을 수 있다.
단일행	(기껏해야) 단일 로우를 리턴하는 서브쿼리. 이는 대개 그 서브쿼리가 쿼리 전체의 선행 포인트가 될 수 있음을 의미한다.
IN / EXISTS	NOT IN 서브쿼리는 NOT EXISTS 서브쿼리로 재작성될 수 있다. 그런 후에 어떤 제약 하에서 안티 조인으로 변환될 수 있다. 'NOT IN'이 'IN'의 반대말이 아니며, 특히 NULL 컬럼인 경우 이 점에 주의해야 한다.

4. Semi JOIN

세미 조인이란 선행 테이블의 한 로우가 후행 테이블에서 한 로우와 조인에 성공하면 그 선행 로우에 대해서 더는 후행 테이블로의 프로세싱을 진행하지 않고 멈추기 때문에 리소스 사용을 절약하는 효과가 있으며 IN 또는 EXISTS 같은 구문의 효율적인 처리를 위해 나온 방식이다.

9i까지는 SQL 구문에서 선행 테이블이 먼저 드라이빙 되도록 구성되어 있다면 옵티마이저가 선행 테이블의 데이터가 많다 할지라도 실행계획을 그렇게 구성하였지만 10g로 오면서 선행 테이블의 데이터가 많다면 후행 테이블을 먼저 드라이빙하고 실행계획에서는 'SEMI'에서 'RIGHT SEMI' 문구가 보인다.


-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;

CREATE TABLE EMP (DEPT_NO NUMBER	NOT NULL,
                  SAL     NUMBER,
                  EMP_NO  NUMBER,
                  PADDING VARCHAR2(60),
                  CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
;

INSERT INTO EMP
SELECT MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 20000
;


CREATE TABLE DEPT (DEPT_NO    NUMBER(6),
                   DEPT_GROUP NUMBER)
;

INSERT INTO DEPT VALUES(0, 1);
INSERT INTO DEPT VALUES(1, 1);
INSERT INTO DEPT VALUES(2, 1);
INSERT INTO DEPT VALUES(3, 2);
INSERT INTO DEPT VALUES(4, 2);
INSERT INTO DEPT VALUES(5, 2);
COMMIT;


ALTER TABLE DEPT ADD CONSTRAINT D_UK UNIQUE (DEPT_NO);
ALTER TABLE DEPT MODIFY DEPT_NO NOT NULL;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          => 'EMP',
                                  CASCADE	       => TRUE,
                                  ESTIMATE_PERCENT => NULL, 
                                  METHOD_OPT       =>'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'DEPT',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 2. 9i Semi Join
SELECT * FROM V$VERSION;

BANNER                                                                
----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';

EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EXISTS (SELECT 1
               FROM   DEPT
               WHERE  EMP.DEPT_NO = DEPT.DEPT_NO
               AND    DEPT.DEPT_GROUP = 1)
;

@XPLAN;
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    54 |
|*  1 |  HASH JOIN SEMI    |      | 10000 |   751K|    54 |
|   2 |   TABLE ACCESS FULL| EMP  | 20000 |  1406K|    36 |
|*  3 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     4 |
-----------------------------------------------------------
;

-- 3. 10g Semi Join
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';

EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EXISTS (SELECT 1
               FROM   DEPT
               WHERE  EMP.DEPT_NO = DEPT.DEPT_NO
               AND    DEPT.DEPT_GROUP = 1)
;

@XPLAN;
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10000 |   751K|    70   (2)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      | 10000 |   751K|    70   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | DEPT |     3 |    15 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 |
-----------------------------------------------------------------------------
;

5. Anti Join

안티 조인은 세미 조인의 부정형으로 보면 된다.


EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EMP.DEPT_NO NOT IN (SELECT DEPT.DEPT_NO
                           FROM   DEPT
                           WHERE  DEPT.DEPT_GROUP = 2)
;

@XPLAN;
----------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |      | 10000 |   751K|    70   (2)| 00:00:01 | 
|*  1 |  HASH JOIN RIGHT ANTI|      | 10000 |   751K|    70   (2)| 00:00:01 | 
|*  2 |   TABLE ACCESS FULL  | DEPT |     3 |    15 |     5   (0)| 00:00:01 | 
|   3 |   TABLE ACCESS FULL  | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 | 
-----------------------------------------------------------------------------

6. NULL과 NOT IN 그리고 NOT EXISTS

reference : http://www.ocmkorea.com
서브쿼리에서 NOT EXISTS를 NOT IN으로 변환 시 조인키에 NULL값이 허용되어 있고
NULL값이 들어가 있다면 데이터가 다르게 나올 수 있다.

1) NOT IN


-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE TAB1 PURGE;
DROP TABLE TAB2 PURGE;

CREATE TABLE TAB1 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9802' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9804' COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, NULL   COL2 FROM DUAL UNION ALL
SELECT '6666' COL1, '9807' COL2 FROM DUAL UNION ALL
SELECT NULL   COL1, '9809' COL2 FROM DUAL
;

CREATE TABLE TAB2 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT 'KKKK' COL1, NULL   COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT NULL   COL1, '9809' COL2 FROM DUAL
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          => 'TAB1',
                                  CASCADE	       => TRUE,
                                  ESTIMATE_PERCENT => NULL, 
                                  METHOD_OPT       =>'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'TAB2',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 2. NOT IN 테스트
SELECT *
FROM   TAB1
WHERE  COL2 NOT IN (SELECT COL2 
                    FROM   TAB2)
;

no rows selected

TAB1

COL1	COL2
1111	9801
2222	9801
3333	9801
4444	9802
4444	9804
5555	NULL
6666	9807
NULL	9809

TAB2

COL1	COL2
1111	9801
2222	9801
3333	9801
KKK	NULL
5555	9801
NULL	9809

이렇게 구성되어 있는 테이블을 COL2 컬럼으로 NOT IN인 것들을 찾을 때 데이터를 한건도 찾을 수 없었다.
예상대로라면 TAB2의 DISTICT.COL2인 값을 제외한 TAB1.COL1을 찾으면 되므로 '9802, 9804, NULL, 9807' 4개의 로우가 나와야 하나 한건도 나오지 않았는데 그 이유는 아래와 같다.

옵티마이저는 TAB2.COL2 컬럼의 NOT IN 조건에 만족하는 값을 찾기 위해 먼저 DISTINCT를 해서 '9801, NULL, 9809' 값을 가져온다. 그 다음 그 값을 가지고 TAB1.COL2 값과 아래처럼 비교를 한다.

COL2 <> '9801' AND COL2 <> NULL AND COL2 '9809'

여기서 COL2 <> '9801' 조건은 TRUE이나 COL2 <> NULL 조건은 FALSE이므로 이 조건은 모두 FALSE가 되고 이로 인해 데이터를 한건도 가져오지 못한다.

그러므로 값을 취하려면 서브쿼리에서 COL2 값이 IS NOT NULL인 값들만 취하도록 변경해 주어야 한다.


SELECT *
FROM   TAB1
WHERE  COL2 NOT IN (SELECT COL2 
                    FROM   TAB2
                    WHERE  COL2 IS NOT NULL)
;

COL1     COL2    
-------- --------
4444     9802    
4444     9804    
6666     9807

TAB2.COL2에서 IS NOT NULL값을 취했기 때문에 TAB1.COL1에서도 NULL값은 가져오지 않는다.

2) NOT EXISTS

EXISTS는 드라이빙이 아닌 필터 방식으로 처리하므로 데이터를 가져올 수 있다.


SELECT *
FROM   TAB1
WHERE  NOT EXISTS (SELECT 1 
                   FROM   TAB2
                   WHERE  TAB2.COL2 = TAB1.COL2)
ORDER BY COL1                   
;

COL1     COL2     
-------- -------- 
4444     9804     
4444     9802     
5555              
6666     9807

3) 정리

그러므로 NOT IN을 NOT EXISTS로 변경 시 이 점을 반드시 유의하여 접근해야 한다.