NOT IN 에서 NULL 사용 시 주의사항

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,NULL)

SELECT * FROM DEPT WHERE DEPTNO NOT IN (10,20,NULL);

결과가 하나도 안나왔다. 

아마도 대부분의 사람들은 다음과 같은 기대값을 생각했을 것이다.

IN은 근본적으로 OR연산과 같다. 따라서 다음과 같이 표현될 수 있다.

--IN 테이블
SELECT 
	*	
FROM DEPT 
WHERE  (DEPTNO = 10 
     OR DEPTNO = 20
     OR DEPTNO = NULL)
     
     ----------------
--NOT IN 테이블
SELECT 
	*	
FROM DEPT 
WHERE NOT (DEPTNO = 10 
        OR DEPTNO = 20
        OR DEPTNO = NULL)

DEPT 의 DEPTNO 이 10일 때 어떻게 필터링 되는지 보자

 

 

DEPTNO = 10 

IN 테이블

(DEPTNO = 10 , DEPTNO = 20 , DETPNO = NULL) 

(TRUE, FALSE, NULL)   NULL과의 연산은 NULL을 리턴한다.

(TRUE, NULL)      OR은 하나라도 TRUE이면 TRUE를 리턴하므로 FALSE를 제거해봤다.

TRUE     OR연산 시 TRUE는 NULL과의 연산에도 TRUE를 리턴한다.

 

NOT IN 테이블

마지막 결과에 부정 연산자를 사용해보자 

NOT(TRUE)  

FALSE

 

DEPTNO = 40 

NOT IN 테이블

(DEPTNO = 10 , DEPTNO = 20 , DETPNO = NULL) 

(FALSE, FALSE, NULL)

(FALSE, NULL)  

(NULL)   OR 연산 시 FALSE는 NULL과 연산 결과로 NULL을 반환한다. 

 

따라서 하나라도 TRUE가 있는 경우 NOT 결과로 FALSE를 반환한다.

전부 FALSE일 때는 NULL이 있어 NULL을 반환하기 때문에 아무 결과도 나오지 않게 된다.

 

 

IN 연산자 안에 NULL값이 들어가게 되면 TRUE를 리턴한 때는 정상동작하지만

FALSE의 경우 항상 NULL과의 연산에서 NULL을 리턴하게 되므로 

NOT IN 은 NULL이 하나라도 들어가면 모든 결과가 부정된다. 

 

해법

SELECT * FROM DEPT WHERE DEPTNO NOT IN (10,20,DECODE(30,NULL,'9999',30)) --NVL 등 방법은 여러 가지

SELECT 
	*	
FROM DEPT A
WHERE NOT EXISTS (SELECT 1 
                    FROM DEPT B 
                   WHERE DEPTNO IN(10, 20, NULL)
                     AND A.DEPTNO = B.DEPTNO)

NULL일 가능성이 있는 컬럼을 보정한다.

 

또는 EXISTS를 사용한다.